Getting Up To Date with the JDBC API

Getting Up To Date with the JDBC API

By Jeff Brown, OCI Senior Software Engineer

July 2001


The Java Database Connectivity (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 ResultSets, updateable ResultSets, 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, promises 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 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:

  1. Load the appropriate JDBC driver
  2. Request a connection to the database
  3. Send SQL to the database
  4. Process results, if appropriate

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().

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

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:


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.

Note: 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.
  1. // Load the JDBC-ODBC bridge driver used
  2. // for connecting to an ODBC data source
  3. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  5. // declare a URL to an ODBC data source named HR
  6. String dbUrl = "jdbc:odbc:HR";
  8. // Retrieve a connection to the database
  9. Connection connection = DriverManager.getConnection(dbUrl);

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 accepts 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.

  1. Statement statement = connection.createStatement();
  2. String sql = "select first_name, last_name from users";
  3. ResultSet resultSet = statement.executeQuery(sql);
  5. while( {
  6. // the integer arguments to the getString() method here correspond
  7. // with the columns specified in the select statement
  8. // note that the integer arguments are NOT zero based
  9. // the first column is index 1
  10. String firstName = resultSet.getString(1);
  11. String lastName = resultSet.getString(2);
  12. // do something with the data
  13. }

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

  1. Statement statement = connnection.createStatement();
  2. String sql = "update users set eligible_flag = 'Y' where age > 66";
  3. // the return value of executeUpdate() indicates how many rows were affected
  4. // by the update
  5. int n = statement.executeUpdate(sql);
  6. 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

Starting with JDBC 2.0, a number of new features were added that relate to the flexibility of ResultSets.

One of the new features added in JDBC 2.0 is the scrollability of ResultSets. JDBC 1.0 ResultSets are limited in such a way that each row in the ResultSet may only be visited once and the ResultSet has to be traversed from front to end.

  1. ResultSet resultSet = statement.executeQuery(sql);
  2. // visit each item once
  3. while ( {
  4. // process the resultSet
  5. }

JDBC 2.0 ResultSets may be one of three different types with respect to scrollability:


ResultSets of type TYPE_FORWARD_ONLY are similar to JDBC 1.0 ResultSets.

The two new types of ResultSets are TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE. Both of these ResultSet types are scrollable.

If a ResultSet is one of the scrollable types, then there are a number of methods that will scroll the ResultSet cursor around in more flexible ways than simply calling next() to visit each item sequentially.

  1. // retrieve resultSet from the database
  2. ResultSet resultSet = statement.executeQuery(sql);
  4. // moves the cursor to the third row
  5. resultSet.absolute(3);
  7. // moves the cursor to the fifth row from the end
  8. resultSet.absolute(-5);
  10. // moves the cursor backwards one row
  11. resultSet.previous();
  13. // moves the cursor forward 4 rows
  14. resultSet.relative(4);
  16. // moves the cursor backward 2 rows
  17. resultSet.relative(-2);
  19. // moves the cursor to the first row in the ResultSet
  20. resultSet.first();
  22. // moves the cursor to the last row in the ResultSet
  23. resultSet.last();

All of these methods return a boolean that indicates whether or not the cursor is actually on a row in the ResultSet. 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 this condition also.

  1. ResultSet resultSet = statement.executeQuery(sql);
  2. // assume that resultSet contains 10 rows of information
  3. // this will work fine and returns true
  4. resultSet.absolute(5);
  6. // this will move the cursor off the end of resultSet and will return false
  7. resultSet.relative(7);
  9. // this will also return true now
  10. boolean isAfterLastRow = resultSet.afterLast();

None of this works with ResultSets of type TYPE_FORWARD_ONLY.

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

  1. // ResultSet objects returned from this Statement should be scrollable,
  2. // should not be sensitive to changes made by other transactions and should
  3. // be read only
  4. Statement statement = connnection.createStatement(
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

ResultSets have one of two concurrency types: A read-only ResultSet is of type CONCUR_READ_ONLY, and an updatable ResultSet is of type CONCUR_UPDATABLE.

JDBC 1.0 ResultSets are all of type CONCUR_READ_ONLY.

Updatable ResultSets 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 ResultSet.

There are two forms of each of these update methods.

  1. updateInt(int columnIndex, int i);
  2. updateInt(String columnName, int i);
  4. updateString(int columnIndex, String s);
  5. updateString(String columnName, String s);
  6. ...

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.

  1. String sql = "select part_no, description, qty_needed from stock where "
  2. + "qty_on_hand < 10";
  3. ResultSet resultSet = statement.executeQuery(sql);
  4. while ( {
  5. // update the qty_needed column with the value 500
  6. resultSet.updateInt(3, 500);
  8. // send the change to the database
  9. resultSet.updateRow();
  10. }

After modifying values in a row of a ResultSet, 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 a 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.

  1. // each time executeUpdate is called, an insert is sent to the database
  2. statement.executeUpdate("insert into users (first_name, last_name) "
  3. + "values('Jeff', 'Brown')");
  4. statement.executeUpdate("insert into users (first_name, last_name) "
  5. + "values('Betsy', 'Brown')");
  6. statement.executeUpdate("insert into users (first_name, last_name) "
  7. + "values('Zack', 'Brown')");
  8. statement.executeUpdate("insert into users (first_name, last_name) "
  9. + "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().

  1. statement.addBatch("insert into users (first_name, last_name) "
  2. + "values('Jeff', 'Brown')");
  3. statement.addBatch("insert into users (first_name, last_name) "
  4. + "values('Betsy', 'Brown')");
  5. statement.addBatch("insert into users (first_name, last_name) "
  6. + "values('Zack', 'Brown')");
  7. statement.addBatch("insert into users (first_name, last_name) "
  8. + "values('Jake', 'Brown')");
  10. // send all of the updates to the database
  11. statement.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.

  1. // retrieve a connection to the database
  2. Connection connection = DriverManager.getConnection(dbUrl);
  4. // turn off auto-commit mode
  5. connection.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.

  1. connection.setAutoCommit(false);
  2. Statement statement = connection.createStatement();
  4. // send 4 separate updates to the database
  5. statement.executeUpdate(updateString1);
  6. statement.executeUpdate(updateString2);
  7. statement.executeUpdate(updateString3);
  8. statement.executeUpdate(updateString4);
  10. // commit the updates
  11. connection.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 committing 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.

  1. connection.setAutoCommit(false);
  2. Statement statement = connection.createStatement();
  4. // send 2 updates to the database
  5. statement.executeUpdate(updateString1);
  6. statement.executeUpdate(updateString2);
  8. // create a savepoint
  9. Savepoint savePoint1 = connection.setSavepoint("SP1");
  11. // send 2 more updates to the database
  12. statement.executeUpdate(updateString3);
  13. statement.executeUpdate(updateString4);
  15. // rollback to SP1, effectively removing the last 2 updates from the
  16. // transaction
  17. connection.rollback(savePoint1);
  19. // commit the updates
  20. connection.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.

  1. Statement statement = connection.createStatement();
  3. // insert a new user into the database
  4. // notice that the USER_ID is not accounted for here
  5. statement.executeUpdate("insert into users (first_name, last_name) "
  6. + "values('Jeff', 'Brown')");
  8. // Retrieve a ResultSet containing all of the auto-generated keys from the
  9. // last update issued on this statement
  10. // the specific details of the format of this ResultSet are not clearly
  11. // specified yet
  12. ResultSet resultSet = statement.getGeneratedKeys();


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.


Software Engineering Tech Trends (SETT) is a regular publication featuring emerging trends in software engineering.