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

Introduction

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

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.

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.

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

Statement statement = connection.createStatement();
String sql = "select first_name, last_name from users";
ResultSet resultSet = statement.executeQuery(sql);
 
while(resultSet.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 = resultSet.getString(1);
    String lastName = resultSet.getString(2);
    // do something with the data
}

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

Statement statement = connnection.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 = statement.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

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.

ResultSet resultSet = statement.executeQuery(sql);
// visit each item once
while (resultSet.next()) {
// process the resultSet
}

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

1. TYPE_FORWARD_ONLY
2. TYPE_SCROLL_INSENSITIVE
3. TYPE_SCROLL_SENSITIVE

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.

• TYPE_SCROLL_INSENSITIVE ResultSets are scrollable and are not sensitive to changes made by other transactions.
• TYPE_SCROLL_SENSITIVE ResultSets are also scrollable, but they are sensitive to changes made by other transactions.

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.

// retrieve resultSet from the database
ResultSet resultSet = statement.executeQuery(sql);
 
// moves the cursor to the third row
resultSet.absolute(3);
 
// moves the cursor to the fifth row from the end
resultSet.absolute(-5);
 
// moves the cursor backwards one row
resultSet.previous();
 
// moves the cursor forward 4 rows
resultSet.relative(4);
 
// moves the cursor backward 2 rows
resultSet.relative(-2);
 
// moves the cursor to the first row in the ResultSet
resultSet.first();
 
// moves the cursor to the last row in the ResultSet
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 12^th 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.

ResultSet resultSet = statement.executeQuery(sql);
// assume that resultSet contains 10 rows of information
// this will work fine and returns true
resultSet.absolute(5);
 
// this will move the cursor off the end of resultSet and will return false
resultSet.relative(7);
 
// this will also return true now
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).

// 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 statement = connnection.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

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.

updateInt(int columnIndex, int i);
updateInt(String columnName, int i);
 
updateString(int columnIndex, String s);
updateString(String columnName, String s);
...

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 resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    // update the qty_needed column with the value 500
    resultSet.updateInt(3, 500);
 
    // send the change to the database
    resultSet.updateRow();
}

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.

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

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

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

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

connection.setAutoCommit(false);
Statement statement = connection.createStatement();
 
// send 2 updates to the database
statement.executeUpdate(updateString1);
statement.executeUpdate(updateString2);
 
// create a savepoint
Savepoint savePoint1 = connection.setSavepoint("SP1");
 
// send 2 more updates to the database
statement.executeUpdate(updateString3);
statement.executeUpdate(updateString4);
 
// rollback to SP1, effectively removing the last 2 updates from the
// transaction
connection.rollback(savePoint1);
 
// commit the updates
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.

Statement statement = connection.createStatement();
 
// insert a new user into the database
// notice that the USER_ID is not accounted for here
statement.executeUpdate("insert into users (first_name, last_name) "
        + "values('Jeff', 'Brown')");
 
// Retrieve a ResultSet 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 resultSet = statement.getGeneratedKeys();

Summary

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.

References

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