5.1.7 Sending Batch Updates

A Statement object may submit multiple update commands together as a single unit, or batch, to the underlying DBMS. This ability to submit multiple updates as a batch rather than having to send each update individually can improve performance greatly in some situations.

The following code fragment demonstrates how to send a batch update to a database. In this example, a new row is inserted into three different tables in order to add a new employee to a company database. The code fragment starts by turning off the Connection object con's auto-commit mode in order to allow multiple statements to be sent together as a transaction. After creating the Statement object stmt, it adds three SQL INSERT INTO commands to the batch with the method addBatch and then sends the batch to the database with the method executeBatch. The code looks like this:

Statement stmt = con.createStatement();
con.setAutoCommit(false);
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, '260')");
int [] updateCounts = stmt.executeBatch();

Because the connection's auto-commit mode is disabled, the application is free to decide whether or not to commit the transaction if an error occurs or if some of the commands in the batch fail to execute. For example, the application may not commit the changes if any of the insertions fail, thereby avoiding the situation where employee information exists in some tables but not in others.
In the Java 2 platform, a Statement object is created with an associated list of commands. This list is empty to begin with; commands are added to the list with the Statement method addBatch. The commands added to the list must all return only a simple update count. If, for example, one of the commands is a query (a SELECT statement), which will return a result set, the method executeBatch will throw a BatchUpdateException. A Statement object's list of commands can be emptied by calling the method clearBatch on it.

In the preceding example, the method executeBatch submits stmt's list of commands to the underlying DBMS for execution. The DBMS executes each command in the order in which it was added to the batch and returns an update count for each command in the batch, also in order. If one of the commands does not return an update count, its return value cannot be added to the array of update counts that the method executeBatch returns. In this case, the method executeBatch will throw a BatchUpdateException. This exception keeps track of the update counts for the commands that executed successfully before the error occurred, and the order of these update counts likewise follows the order of the commands in the batch.
In the following code fragment, an application uses a try/catch block, and if a BatchUpdateException is thrown, it retrieves the exception's array of update counts to discover which commands in a batch update executed successfully before the BatchUpdateException object was thrown.

try {
stmt.addBatch("INSERT INTO employees VALUES (" +
"1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, '260')");
int [] updateCounts = stmt.executeBatch();
} catch(BatchUpdateException b) {
System.err.println("Update counts of successful commands: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i ++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
}

If a printout was generated and looked similar to the following, the first two commands succeeded and the third one failed.
Update counts of successful commands:
1 ;1

JDBC drivers are not required to support batch updates, so a particular driver might not implement the methods addBatch, clearBatch, and executeBatch. Normally a programmer knows whether a driver that he/she is working with supports batch updates, but if an application wants to check, it can call the DatabaseMetaData method supportsBatchUpdates to find out. In the following code fragment, a batch update is used only if the driver supports batch updates; otherwise, each update is sent as a separate statement. The connection's auto-commit mode is disabled so that in either case, all the updates are included in one transaction.

con.setAutoCommit(false);
if(dbmd.supportsBatchUpdates) {
stmt.addBatch("INSERT INTO . . .");
stmt.addBatch("DELETE . . .");
stmt.addBatch("INSERT INTO . . .");
. . .
stmt.executeBatch();
} else {
System.err.print("Driver does not support batch updates; ");
System.err.println("sending updates in separate statements.");
stmt.executeUpdate("INSERT INTO . . .");
stmt.executeUpdate("DELETE . . .");
stmt.executeUpdate("INSERT INTO . . .");
. . .
con.commit();
If one of the commands in a batch update fails, the method executeBatch will throw a BatchUpdateException. The BatchUpdateException method getUpdateCounts can be called to get an array of the update counts that were returned. In the previous examples, as soon as a command in a batch failed, the driver stopped processing commands, so the array contained update counts for only those commands that were executed before the first failure. A driver may be implemented so that it continues to process subsequent commands instead of stopping with a failure. In this case, the array of update counts returned by the method getUpdateCounts will contain a value for every command in the batch. The value for a command that failed is Statement.EXECUTE_FAILED.