|The batch update facility allows a Statement object to submit a set of heterogeneous update commands together as a single unit, or batch, to the underlying DBMS. In the example below all of the update operations required to insert a new employee into a fictitious company database are submitted as a single batch.
|// turn off autocommit
Statement stmt = con.createStatement();
stmt.addBatch(“INSERT INTO employees VALUES (1000, ‘Joe Jones’)”);
// submit a batch of update commands for execution
|In the example, autocommit mode is disabled to prevent the driver from committing the transaction when Statement.executeBatch() is called. Disabling autocommit allows an application to decide whether or not to commit the transaction in the event that an error occurs and some of the commands in a batch cannot be processed successfully. For this reason, autocommit should always be turned off when batch updates are done. The commit behavior of executeBatch is always implementation defined when an error occurs and autocommit is true.
|To keep our discussion of batch updates general, we define the term element to refer to an individual member of a batch. As we have seen, an element in a batch is just a simple command when a Statement object is being used. Although we are focusing on using Statement objects to do batch updates in this section, the discussion that follows applies to PreparedStatment and CallableStatementobjects, as well.
|In the new JDBC API, a Statement object has the ability to keep track of a list of commands -or batch-that can be submitted together for execution. When a Statement object is created, its associated batch is empty-the batch contains no elements. The Statement.addBatch() method adds an element to the calling statement’s batch. The method Statement.clearBatch() (not shown above) can be called to reset a batch if the application decides not to submit a batch of commands that has been constructed for a statement.
|The Statement.executeBatch() method submits a statement’s batch to the underlying data source for execution. Batch elements are executed serially (at least logically) in the order in which they were added to the batch. When all of the elements in a batch execute successfully, executeBatch() returns an integer array containing one entry for each element in the batch. The entries in the array are ordered according to the order in which the elements were processed (which, again, is the same as the order in which the elements were originally added to the batch). An entry in the array may have the following values:
|1. If the value of an array entry is greater than or equal to zero, then the batch element was processed successfully and the value is an update count indicating the number of rows in the database that were effected by the element’s execution.
|2. A value of -2 indicates that a element was processed successfully, but that the number of effected rows is unknown.
|Calling executeBatch() closes the calling Statement object’s current result set if one is open. The statement’s internal list of batch elements is reset to empty once executeBatch() returns. The behavior of the executeQuery, executeUpdate, or execute methods is implementation defined when a statement’s batch is non-empty.
|ExecuteBatch() throws a BatchUpdateException if any of the elements in the batch fail to execute properly, or if an element attempts to return a result set. Only DDL and DML commands that return a simple update count may be executed as part of a batch. When a BatchUpdateException is thrown, the BatchUpdateException.getUpdateCounts() method can be called to obtain an integer array of update counts that describes the outcome of the batch execution.