Batch Updates:JAVA

Description of Batch Updates

Statements

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 data source.

Since the JDBC 2.0 API, a Statement object has had 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. An application adds
commands to a statement’s batch one at a time by calling the method
Statement.addBatch and providing it with the SQL update command to be
added. All of the commands added to a batch must be statements that return an
update count.

If an application decides not to submit a batch of updates that has been constructed
for a statement, it can call the method Statement.clearBatch to clear the batch
of all commands.

In CODE EXAMPLE 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
con.setAutoCommit(false);
Statement stmt = con.createStatement();
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)");
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();

CODE EXAMPLE Creating and executing a batch of insert statementsIn the example, auto-commit mode is disabled to prevent the driver from committing the transaction when Statement.executeBatch is called. Disabling auto-commit 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, auto-commit should always be
turned off when batch updates are done. The commit behaviour of executeBatch
is always implementation-defined when an error occurs and auto-commit is true.
It is not possible to set a savepoint "within" a batch of statements to enable partial
recovery. If a savepoint is set any time before the method executeBatch is called,
is set before any of the statements that have been added to the batch are executed.
Although the focus in this section is on using Statement objects to do batch
updates, the discussion that follows applies to PreparedStatment and
CallableStatement objects as well.

Successful Execution

The Statement.executeBatch method submits a statement’s batch to the
underlying data source for execution. Batch commands are executed serially (at least
logically) in the order in which they were added to the batch. When all of the
commands in a batch execute successfully, the method executeBatch returns an
integer array containing one entry for each command in the batch.
The entries in the array are ordered according to the order in which the commands
were processed (which, again, is the same as the order in which the commands were
originally added to the batch). When all of the commands in a batch have been
executed successfully, an entry in the array of update counts may have the following
values :

• 0 or greater — the command was processed successfully and the value is an update count indicating the number of rows in the database that were affected by the command’s execution
• Statement.SUCCESS_NO_INFO— the command was processed successfully, but the number of rows affected is unknown Calling the method executeBatch closes the calling Statement object’s current result set if one is open. The statement’s batch is reset to empty once executeBatch returns. The behaviour of the methods executeQuery, executeUpdate, and execute is implementation-defined when a statement’s batch is non-empty. Only DDL and DML commands that return a simple update count may be executed as part of a batch. The method executeBatch throws a BatchUpdateException
  

if any of the commands in the batch fail to execute properly or if a command
attempts to return a result set. When a BatchUpdateException is thrown, an
application can call the BatchUpdateException.getUpdateCounts method to
obtain an integer array of update counts that describes the outcome of the batch
execution.

Handling Failures during Execution

A JDBC driver may or may not continue processing the remaining commands in a
batch once execution of a command fails. However, a JDBC driver must always
provide the same behaviour with a particular data source. For example, a driver
cannot continue processing after a failure for one batch and not continue processing
for another batch.

If a driver stops processing after the first failure, the array returned by the method
BatchUpdateException.getUpdateCounts will always contain fewer entries
than there were statements in the batch. Since statements are executed in the order
that they are added to the batch, if the array contains N elements, this means that the
first N elements in the batch were processed successfully when executeBatch was
called.

When a driver continues processing in the presence of failures, the number of
elements in the array returned by the method
BatchUpdateException.getUpdateCounts always equals the number of
commands in the batch. When a BatchUpdateException object is thrown and the
driver continues processing after a failure, the array of update counts will contain
the following BatchUpdateException constant:

n Statement.EXECUTE_FAILED — the command failed to execute successfully.
This value is also returned for commands that could not be processed for some
reason—such commands fail implicitly.

JDBC drivers that do not continue processing after a failure never return
Statement.EXECUTE_FAILED in an update count array. Drivers of this type
simply return a status array containing an entry for each command that was
processed successfully.

A JDBC technology-based application can distinguish a JDBC driver that continues
processing after a failure from one that does not by examining the size of the array
returned by BatchUpdateException.getUpdateCounts. A JDBC driver that
continues processing always returns an array containing one entry for each element
in the batch. A JDBC driver that does not continue processing after a failure will
always return an array whose number of entries is less than the number of
commands in the batch.

PreparedStatement Objects

When a PreparedStatement object is used, a command in a batch consists of a
parameterized SQL statement and an associated set of parameters . The batch update
facility is used with a PreparedStatement object to associate multiple sets of
input parameter values with a single PreparedStatement object. The sets of
parameter values together with their associated parameterized update commands
can then be sent to the underlying data source engine for execution as a single unit.
CODE EXAMPLE inserts two new employee records into a database as a single
batch. The PreparedStatement interface setter methods are used to create each
parameter set, one for each employee. The PreparedStatement.addBatch
method adds a set of parameters to the current command.

// turn off autocommit
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
Chapter 15 Batch Updates 131
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();

CODE EXAMPLE Creating and executing a batch of prepared statements
Finally, the method PreparedStatement.executeBatch is called to submit the updates to the underlying data source. Calling this method clears the statement’s associated list of commands. The array returned by PreparedStatement.executeBatch contains an element for each set of
parameters in the batch, similar to the case for Statement objects. Each element contains either an update count or the generic ‘success’ indicator SUCCESS_NO_INFO.
Error handling in the case of PreparedStatement objects is the same as error handling in the case of Statement objects. Some drivers may stop processing as soon as an error occurs, while others may continue processing the rest of the batch. As with Statement objects, the number of elements in the array returned by BatchUpdateException.getUpdateCounts indicates whether or not the driver continues processing after a failure. The same three array element values are
possible: 0 or higher, Statement.SUCCESS_NO_INFO, or Statement.EXECUTE_FAILED. The order of the entries in the array is the same order as the order in which commands were added to the batch.

CallableStatement Objects

The batch update facility works the same with CallableStatement objects as it
does with PreparedStatement objects. Multiple sets of input parameter values
may be associated with a CallableStatement object and sent to the underlying
data source together.

Stored procedures invoked using the batch update facility with a callable statement
must return a maximum of one update counts, if no update count is returned the
array element value will be Statement.SUCCESS_NO_INFO. Additionally, a
batchable stored procedure may not have OUT or INOUT parameters. The
CallableStatement.executeBatch method throws an exception if this restriction is violated. Error handling is analogous to that for PreparedStatement objects.