ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Getting Up To Date with JDBC API
Pages: 1, 2, 3

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.