| Contents | Prev | Next | JDBCTM Guide: Getting Started |
Statement,
PreparedStatement, and CallableStatement objects can be used to submit batch
updates.
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
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();
Statement.executeBatch() is called. Disabling autocommit allows
the 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 fail to execute. For this reason, autocommit
should usually be turned off when batch updates are done.
In JDBC 2.0, a Statement object has the ability to keep track of a list of commands that
can be submitted together for execution. When a statement is created, its associated list
of commands is empty. The Statement.addBatch() method adds an element to the
calling statement's list of commands. An SQLException is thrown when Statement.
executeBatch() is called if the batch contains a command that 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. 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 batch of commands to the underlying
DBMS for execution. Commands are executed in the order in which they were
added to the batch. ExecuteBatch() returns an array of update counts for the commands
that were executed. The array contains one entry for each command in the batch,
and the elements in the array are ordered according to the order in which the commands
were executed (which, again, is the same as the order in which commands were originally
added to the batch). Calling executeBatch() closes the calling Statement object's
current result set if one is open. The statements's internal list of batch commands
is reset to empty once executeBatch() returns.
ExecuteBatch() throws a BatchUpdateException if any of the commands in the
batch fail to execute properly. The BatchUpdateException.getUpdateCounts()
method can be called to return an integer array of update counts for the commands in
the batch that were executed successfully. Since Statement.executeBatch() stops
when the first command returns an error, and commands are executed in the order that
they are added to the batch, if the array returned by BatchUpdateException.getUpdateCounts()
contains N elements, this means that the first N commands in the batch
executed successfully when executeBatch() was called.
PreparedStatement object. The sets of parameter
values together with the associated parameterized update command can then be sent
to the underlying DBMS engine for execution as a single unit.
The example below inserts two new employee records into a database as a single batch.
The PreparedStatement.setXXX() methods are used to create each parameter set
(one for each employee), while the PreparedStatement.addBatch() method adds a
set of parameters to the current batch.
Finally,// 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(); // submit the batch for execution int[] updateCounts = stmt.executeBatch();
PreparedStatement.executeBatch() is called to submit the updates to the
DBMS. Error handling in the case of PreparedStatement objects is analogous to error
handling for Statement objects.
CallableStatement objects as it does
with PreparedStatement objects. Multiple sets of input parameter values may be associated
with a callable statement and sent to the DBMS together. Stored procedures
invoked using the batch update facility with a callable statement must return an update
count, and may not have out or inout parameters. The CallableStatement.executeB
atch() method should throw an exception if this restriction is violated.