JDBC Concepts

Connecting to a Database
This example uses the JDBC-ODBC bridge to connect to a database called "mydatabase".
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url = "jdbc:odbc:mydatabase";
Connection con = DriverManager.getConnection(
url, "login", "password");
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}

Creating a Table
This example creates a table called "mytable" with three columns: COL_A which holds strings, COL_B which holds integers, and COL_C which holds floating point numbers.
try {
Statement stmt = con.createStatement();

stmt.executeUpdate("CREATE TABLE mytable (
COL_A VARCHAR(100), COL_B INTEGER, COL_C FLOAT)");
} catch (SQLException e) {
}

Entering a New Row into a Table
This example enters a row containing a string, an integer, and a floating point number into the table called "mytable".
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate("INSERT INTO mytable
VALUES (‘Patrick Chan’, 123, 1.23)");
connection.close();
} catch (SQLException e) {
}

Getting All Rows from a Table
This example retrieves all the rows from a table called "mytable". A row in "mytable" consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();

// Get data using colunm names.
ResultSet rs = stmt.executeQuery(
"SELECT * FROM mytable");
while (rs.next()) {
String s = rs.getString("COL_A");
int i = rs.getInt("COL_B");
float f = rs.getFloat("COL_C");
process(s, i, f);
}


// Get data using colunm numbers.
rs = stmt.executeQuery(
"SELECT * FROM mytable");
while (rs.next()) {
String s = rs.getString(1);
int i = rs.getInt(2);
float f = rs.getFloat(3);
process(s, i, f);
}
} catch (SQLException e) {
}

Getting Particular Rows from a Table
This example retrieves all rows from a table called "mytable" whose column COL_A equals "Patrick Chan". A row in "mytable" consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT * FROM mytable WHERE COL_A = ‘Patrick Chan’");
rs.next();
String s = rs.getString("COL_A");
int i = rs.getInt("COL_B");
float f = rs.getFloat("COL_C");
process(s, i, f);
} catch (SQLException e) {
}


Updating a Row of Data in a Table
This example updates a row in a table called "mytable". In particular, for all rows whose column COL_B equals 123, column COL_A is set to "John Doe".
try {
Statement stmt = connection.createStatement();
int numUpdated = stmt.executeUpdate(
"UPDATE mytable SET COL_A = ‘John Doe’
WHERE COL_B = 123");
connection.close();
} catch (SQLException e) {
}


Using a Prepared Statement
A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called "mytable" whose column COL_A equals "Patrick Chan". This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to "John Doe".
try {
// Retrieving rows from the database.
PreparedStatement stmt = connection.prepareStatement(
"SELECT * FROM mytable WHERE COL_A = ?");
int colunm = 1;
stmt.setString(colunm, "Patrick Chan");
ResultSet rs = stmt.executeQuery();

// Updating the database.
stmt = connection.prepareStatement(
"UPDATE mytable SET COL_A = ? WHERE COL_B = ?");
colunm = 1;
stmt.setString(colunm, "John Doe");
colunm = 2;
stmt.setInt(colunm, 123);
int numUpdated = stmt.executeUpdate();
} catch (SQLException e) {
}