Making Batch Updates

A batch update is a set of multiple update statements that is submitted to the database for processing as a batch. Sending multiple update statements to the database together as a unit can, in some situations, be much more efficient than sending each update statement separately. This ability to send updates as a unit, referred to as the batch update facility, is one of the features provided with the JDBC 2.0 API.

Using Statement Objects for Batch Updates
In the JDBC 1.0 API, Statement objects submit updates to the database individually with the method executeUpdate . Multiple executeUpdate statements can be sent in the same transaction, but even though they are committed or rolled back as a unit, they are still processed individually. The interfaces derived from Statement , PreparedStatement and CallableStatement , have the same capabilities, using their own version of executeUpdate .

With the JDBC 2.0 API, Statement, PreparedStatement, and CallableStatement objects have the ability to maintain a list of commands that can be submitted together as a batch. They are created with an associated list, which is initially empty. You can add SQL commands to this list with the method addBatch, and you can empty the list with the method clearBatch. You send all of the commands in the list to the database with the method executeBatch. Now let's see how these methods work.

Let's suppose that our coffee house proprietor wants to start carrying flavored coffees. He has determined that his best source is one of his current suppliers, Superior Coffee, and he wants to add four new coffees to the table COFFEES . Because he is inserting only four new rows, a batch update may not improve performance significantly, but this is a good opportunity to demonstrate batch updates. Remember that the table COFFEES has five columns: column COF_NAME of type VARCHAR (32), column SUP_ID of type INTEGER , column PRICE of type FLOAT , column SALES of type INTEGER , and column TOTAL of type INTEGER . Each row he inserts will have values for the five columns in order. The code for inserting the new rows as a batch might look similar to this:
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Amaretto', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
int [] updateCounts = stmt.executeBatch();
Now let's examine the code line by line.
 
con.setAutoCommit(false);
This line disables auto-commit mode for the Connection object con so that the transaction will not be automatically committed or rolled back when the method executeBatch is called. (If you do not recall what a transaction is, you should review the sections Disabling Auto-commit Mode and Committing a Transaction .) To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.
 
Statement stmt = con.createStatement();
This line of code creates the Statement object stmt . As is true of all newly-created Statement objects, stmt has a list of commands associated with it, and that list is empty.
 
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Amaretto', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES" +
	      "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
Each of these lines of code adds a command to the list of commands associated with stmt . These commands are all INSERT INTO statements, each one adding a row consisting of five column values. The values for the columns COF_NAME and PRICE are self-explanatory. The second value in each row is 49 because that is the identification number for the supplier, Superior Coffee. The last two values, the entries for the columns SALES and TOTAL , all start out being zero because there have been no sales yet. (SALES is the number of pounds of this row's coffee sold in the current week; TOTAL is the total of all the cumulative salesof this coffee.)
 
int [] updateCounts = stmt.executeBatch();
In this line, stmt sends the four SQL commands that were added to its list of commands off to the database to be executed as a batch. Note that stmt uses the method executeBatch to send the batch of insertions, not the method executeUpdate , which sends only one command and returns a single update count. The DBMS will execute the commands in the order in which they were added to the list of commands, so it will first add the row of values for Amaretto, then add the row for Hazelnut, then Amaretto decaf, and finally Hazelnut decaf. If all four commands execute successfully, the DBMS will return an update count for each command in the order in which it was executed. The update counts, which indicate how many rows were affected by each command, are stored in the array of int , updateCounts .

At this point updateCounts should contain four elements of type int . In this case, each int will be 1 because an insertion affects one row. The list of commands associated with stmt will now be empty because the four commands added previously were sent to the database when stmt called the method executeBatch . You can at any time empty this list of commands with the method clearBatch .
Batch Update Exceptions
There are two exceptions that can be thrown during a batch update operation: SQLException and BatchUpdateException .

All methods in the JDBC API will throw an SQLException object when there is a database access problem. In addition, the method executeBatch will throw an SQLException if you have used the method addBatch to add a command that returns a result set to the batch of commands being executed. Typically a query (a SELECT statement) will return a result set, but some methods, such as some of the DatabaseMetaData methods can also return a result set.

Just using the method addBatch to add a command that produces a result set does not cause an exception to be thrown. There is no problem while the command is just sitting in a Statenment object's command list. But there will be a problem when the method executeBatch submits the batch to the DBMS to be executed. When each command is executed, it must return an update count that can be added to the array of update counts returned by the executeBatch method. Trying to put a result set in an array of update counts will cause an error and cause executeBatch to throw an SQLException . In other words, only commands that return an update count (commands such as INSERT INTO , UPDATE , DELETE , CREATE TABLE , DROP TABLE , ALTER TABLE , and so on) can be executed as a batch with the executeBatch method.

If no SQLException was thrown, you know that there were no access problems and that all of the commands produce update counts. If one of the commands cannot be executed for some other reason, the method executeBatch will throw a BatchUpdateException . In addition to the information that all exceptions have, this exception contains an array of the update counts for the commands that executed successfully before the exception was thrown. Because the update counts are in the same order as the commands that produced them, you can tell how many commands were successful and which commands they are.

BatchUpdateException is derived from SQLException . This means that you can use all of the methods available to an SQLException object with it. The following code fragment prints the SQLException information and the update counts contained in a BatchUpdateException object. Because getUpdateCounts returns an array of int , it uses a for loop to print each of the update counts.
 
try {
	// make some updates
} catch(BatchUpdateException b) {
	System.err.println("SQLException: " + b.getMessage());
	System.err.println("SQLState:  " + b.getSQLState());
	System.err.println("Message:  " + b.getMessage());
	System.err.println("Vendor:  " + b.getErrorCode());
	System.err.print("Update counts:  ");
	int [] updateCounts = b.getUpdateCounts();
	for (int i = 0; i < updateCounts.length; i++) {
		System.err.print(updateCounts[i] + "   ");
	}
}
For the complete Batch Update program, see BatchUpdate.java. The code puts together the code fragments from previous sections to make a complete program. One thing you might notice is that there are two catch blocks at the end of the application. If there is a BatchUpdateException object, the first catch block will catch it. The second one will catch an SQLException object that is not a BatchUpdateException object.

New Features in the JDBC 2.0 API