ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Getting Up To Date with JDBC API

by Jeff Brown
08/21/2001

The JDBC API is Java's way of accessing any tabular data source, typically a relational database. The API presents an object-oriented interface to the data and is made up of classes and interfaces in the java.sql package, with standard extensions in javax.sql. Since JDBC 1.0, a lot of functionality has been added to the API. JDBC 2.0 introduced scrollable result sets, updatable result sets, batch update capability and support for new SQL3 data types. The new JDBC 3.0 specification, in its proposed final draft form at this point, is promising to add more robust transactions by way of savepoints, a number of resource pooling enhancements, retrieval of auto-generated keys and a lot more. Through all of the additional functionality, the API has remained very straightforward and very easy to work with. This article discusses a number of these new areas of functionality.

A Quick JDBC Primer

The four basic steps involved with using JDBC to connect to a database are:

Loading the driver is accomplished either by setting the jdbc.drivers system property, or by dynamically loading the appropriate driver class with a call to Class.forName(). The following command line launches the com.ociweb.jdbc.MyApplicationName application and loads the sun.jdbc.odbc.JdbcOdbcDriver driver. This particular driver is used to connect to ODBC data sources, but the procedure is the same for all JDBC drivers.

java –Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver com.ociweb.jdbc.MyApplicationName

The other approach is to load the driver by calling Class.forName().

try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException exc) {
}

The appropriate driver must be loaded before a connection to the database can be established. Once the driver is loaded, a connection to the database can be established with a call to DriverManager.getConnection(). There are several versions of the getConnection method that accept different parameters. The simplest version accepts a single String argument that is a URL to a database. The format of the URL is:

jdbc:sub-protocol:sub-name

The sub-protocol portion of the URL is used by the DriverManager class to locate the appropriate driver. The sub-name portion of the URL is used by the driver to identify which database to connect to. The specifics of the sub-name vary from vendor to vendor so refer to the documentation for your specific JDBC driver. For example, a database URL to an ODBC data source might look like jdbc:odbc:HR. In this particular case, the sub-name is HR and this should be the name of an ODBC data source.

// Load the JDBC-ODBC bridge driver used
// for connecting to an ODBC data source...
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// declare a URL to an ODBC data source named HR
String dbUrl = "jdbc:odbc:HR";

// Retrieve a connection to the database...
Connection conn = DriverManager.getConnection(dbUrl);

Comment on this articleWhat's your experience with the JDBC 3.0 API?
Post your comments

Once a connection to the database has been established, SQL can be sent to the database through a Statement object. The Connection interface defines a createStatement() method which returns a Statement that may be used to issue SQL commands on that Connection. There are two methods in the Statement interface for sending SQL, executeQuery() and executeUpdate(). Each of these methods accept a String argument. The executeQuery() method expects an SQL "select" statement as an argument and will return a ResultSet containing the results of the query.

The ResultSet interface defines the next() method which is used to iterate over the results. The ResultSet interface also defines numerous get methods for retrieving individual columns out of the results:

Statement stmt = conn.createStatement();
String sql = "select first_name, last_name from users";
ResultSet results = stmt.executeQuery(sql);

while(results.next()) {
    // the integer arguments to the getString() method 
    // here correspond with the columns specified in 
    // the select statement note that the integer 
    // arguments are NOT zero based
    // the first column is index 1
    String firstName = results.getString(1);
    String lastName = results.getString(2);
    // do something with the data...
}

The executeUpdate() method in the Statement interface expects an SQL statement that is updating the database, not selecting data from the database:

Statement stmt = conn.createStatement();
String sql = "update users set eligible_flag = 'Y' where age > 66";
// the return value of executeUpdate() indicates
// how many rows were affected by the update...
int n = stmt.executeUpdate(sql);
System.out.println(n + " rows were updated.");

Those are the very basics of using JDBC to issue simple selects and updates to the database. The JDBC API provides much more functionality.

Some More Advanced Functionality

Scrollable ResultSets

Related Reading

Database Programming with JDBC and Java, Second Edition Database Programming with JDBC and Java
By George Reese
Table of Contents
Index
Sample Chapter
Full Description
Read Online -- Safari

Starting with JDBC 2.0, a number of new features were added that relate to the flexibility of result sets. One of the new features added in JDBC 2.0 is the scrollability of result sets. JDBC 1.0 result sets are limited in such a way that each row in the result set may only be visited once and the result set has to be traversed from front to end.

ResultSet myResults = myStmt.executeQuery(myQueryString);
// visit each item once...
while(myResults.next()) {
    // process the results
}

JDBC 2.0 result sets may be one of three different types with respect to scrollability. Result sets of type TYPE_FORWARD_ONLY are similar to JDBC 1.0 result sets. The two new types of result sets are TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE. Both of these result set types are scrollable. TYPE_SCROLL_INSENSITIVE result sets are scrollable, and are not sensitive to changes made by other transactions. TYPE_SCROLL_SENSITIVE result sets are also scrollable, but they are sensitive to changes made by other transactions. If a result set is one of the scrollable types, then there are a number of methods that will scroll the result set cursor around in more flexible ways than simply calling next() to visit each item sequentially.

// retrieve results from the database
ResultSet results = myStatement.executeQuery(myQueryString);

// moves the cursor to the third row in the set
results.absolute(3);

// moves the cursor to the fifth row from the end
results.absolute(-5);

// moves the cursor backwards one row
results.previous();

// moves the cursor forward 4 rows
results.relative(4);

// moves the cursor backward 2 rows
results.relative(-2);

// moves the cursor to the first row in the result set
results.first();

// moves the cursor to the last row in the result set
results.last();

All of these methods return a boolean that indicates whether or not the cursor is actually on a row in the result set. For example, if the result contains 10 rows and code tries to move the cursor to the 12th row, then the cursor is left in a position that is after the last row. The methods afterLast() and beforeFirst() can be used to query check this condition also.

ResultSet results = myStatement.executeQuery(myQueryString);

// assume that results contains 10 rows of information

// this will work fine and returns true
results.absolute(5);

// this will move the cursor off the end of the result
// set and will return false
results.relative(7);

// this will also return true now
boolean isAfterLastRow = results.afterLast();

None of this works with result sets of type TYPE_FORWARD_ONLY. The Statement object that returned the result set determines the result set's type. There is an overloaded version of createStatement() in the Connection interface that accepts two integer arguments. The first integer is a result set scrollability type and the second is a result set concurrency type (concurrency will be discussed shortly).

// ResultSet objects returned from this statement 
// should be scrollable, should not be sensitive 
// to changes made by other transactions and should 
// be read only
Statement myStatement = 
  conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                             ResultSet.CONCUR_READ_ONLY);

Note that the arguments being passed to the createStatement() method here are only requests for ResultSets to be of the specified type. Not all JDBC drivers support all of these ResultSet types. The ResultSet methods getType() and getConcurrency() will return the ResultSet's actual type and concurrency, respectively.

Updatable ResultSets
Result sets have one of two concurrency types. A read only result set is of type CONCUR_READ_ONLY and an updatable result set is of type CONCUR_UPDATABLE. JDBC 1.0 result sets are all of type CONCUR_READ_ONLY. Updatable result sets allow changes to be made to the data using Java code instead of using SQL statements. This approach may be much more natural for Java developers.

The ResultSet interface defines a number of updateXXX() methods for updating the contents of a result set. There are 2 forms of each of these update methods.

updateInt(int columnIndex, int i);
updateInt(String columnName, int i);

updateString(int columnIndex, String s);
updateString(String columnName, String s);

etc...

The first argument is either the column index that is being updated or a string that represents the name of the column being updated. The index corresponds to the order that the column names were specified in the select statement.

String sql = "select part_no, description, qty_needed from stock where qty_on_hand < 10";
ResultSet results = stmt.executeQuery(sql);
while(results.next()) {
  // update the qty_needed column with the value 500
  results.updateInt(3, 500);

  // send the change to the database
  results.updateRow();
}

After modifying values in a row of a result set, a call should be made to either updateRow() to send the update to the database or cancelRowUpdates() to effectively undo the update.

Batch Updates
Version 2.0 of the JDBC specification introduced the idea of batch updates. Batch updates provide the ability to send a group of operations to the database instead of sending each operation independently. Sending the collection of operations as a group has the potential to greatly improve performance by cutting down on the communication back and forth between the application and the database. In JDBC 1.0, every update is sent to the database independently.

// each time executeUpdate is called,
// an insert is sent to the database
stmt.executeUpdate("insert into users (first_name, last_name) values('Jeff', 'Brown')");
stmt.executeUpdate("insert into users (first_name, last_name) values('Betsy', 'Brown')");
stmt.executeUpdate("insert into users (first_name, last_name) values('Zack', 'Brown')");
stmt.executeUpdate("insert into users (first_name, last_name) values('Jake', 'Brown')");

The batch update approach accumulates a group of updates on the client and sends them all to the database at once. After adding a group of updates to a batch, the items are sent to the database with a call to executeBatch() or the batch is cleared out with a call to clearBatch().

stmt.addBatch("insert into users (first_name, last_name) values('Jeff', 'Brown')");
stmt.addBatch("insert into users (first_name, last_name) values('Betsy', 'Brown')");
stmt.addBatch("insert into users (first_name, last_name) values('Zack', 'Brown')");
stmt.addBatch("insert into users (first_name, last_name) values('Jake', 'Brown')");

// send all of the updates to the database
stmt.executeBatch();

A JDBC 2.0 driver may or may not support batch updates. The supportsBatchUpdates() method in the DatabaseMetaData class may be called to discover if a particular driver supports batch updates.

JDBC 3.0

The JDBC 3.0 specification is currently in a proposed final draft form. The current draft proposes a number of enhancements including transaction savepoints and retrieval of auto-generated keys.

Transaction Savepoints
To use transactions, a JDBC Connection object must not be in auto-commit mode. The default behavior is for a connection to be in auto-commit mode. To use transactions, a call must be made to setAutoCommit() with an argument of false.

// retrieve a connection to the database
Connection conn = DriverManager.getConnection(myDBUrl);

// turn off auto-commit mode
conn.setAutoCommit(false);

Once a connection is out of auto-commit mode, transactions must be explicitly committed to the database, or rolled back by making calls to commit() or rollback() on the connection. Calling the commit() method commits the changes to the database and implicitly begins a new transaction. Calling the rollback() method rolls the changes back as if they had never happened, and also implicitly begins a new transaction.

conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

// send 4 separate updates to the database
stmt.executeUpdate(updateString1);
stmt.executeUpdate(updateString2);
stmt.executeUpdate(updateString3);
stmt.executeUpdate(updateString4);

// commit the updates
conn.commit();

Before JDBC 3.0, all four of these updates would have to be committed as a group or rolled back as a group. There would be no way of commiting a subset of the updates once they were added to the transaction. With JDBC 3.0, savepoints will offer finer-grained control of this behavior. During a transaction a named savepoint may be inserted between operations. This named savepoint acts as a marker in the transaction and the transaction may be rolled back to that marker, effectively removing all of the operations after the marker but leaving all of the operations before the marker in place.

conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

// send 2 updates to the database
stmt.executeUpdate(updateString1);
stmt.executeUpdate(updateString2);

// create a savepoint
Savepoint sp1 = conn.setSavepoint("SP1");

// send 2 more updates to the database
stmt.executeUpdate(updateString3);
stmt.executeUpdate(updateString4);

// rollback to sp1, effectively removing the 
// last 2 updates from the transaction
conn.rollback(sp1);

// commit the updates
conn.commit();

Auto-Generated Keys

Some databases allow for certain columns to be given automatically generated key values. In this case, an insert statement would not be responsible for supplying a value for the column. The database would generate a unique value for the column and insert the value. This is often used for generating unique primary keys. A problem with this approach is that it may be difficult to get the value after the insert is executed. The JDBC 3.0 specification proposes a more functional Statement interface that provides access to these values after an insert.

Assume a table called USERS with 3 columns. The FIRST_NAME column and LAST_NAME column are varchars. The USER_ID column is an auto generated column and should contain a unique identifier for each user in the table.

Statement stmt = conn.createStatement();

// insert a new user into the database
// notice that the USER_ID is not accounted for here
stmt.executeUpdate("insert into users (first_name, last_name) values('Jeff', 'Brown')");

// Retrieve a result set containing all of the auto-generated keys from
// the last update issued on this statement
// the specific details of the format of this ResultSet are not clearly specified yet
ResultSet rs = stmt.getGeneratedKeys();

Conclusion

The JDBC API has matured a lot over the last few years. The API continues to get more flexible and powerful while remaining very simple and straightforward. The Java 2 Standard Edition (J2SE) version 1.4 will contain version 3.0 of JDBC. It may take some time before most driver vendors support all of the new functionality.

A related technology is Java Data Objects (JDO). JDO promises to hide more of the database specific code from the developer. Some benefits of JDO will be the addition of more compile time checking and more manipulation of data through the API instead of through SQL statements. JDO is intended to complement JDBC.

For the latest information on JDBC and JDO, visit http://java.sun.com/products/jdbc/.

Jeff Brown is a Senior Software Engineer, Object Computing, Inc. (OCI)


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.