|Contents | Prev | Next||JDBCTM Guide: Getting Started|
CallableStatementobjects can be used to submit batch updates.
Statementobject 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.
In the example, autocommit mode is disabled to prevent JDBC from committing the transaction when// 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
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.
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
current result set if one is open. The statements's internal list of batch commands
is reset to empty once
ExecuteBatch() throws a
BatchUpdateException if any of the commands in the
batch fail to execute properly. The
method can be called to return an integer array of update counts for the commands in
the batch that were executed successfully. Since
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
contains N elements, this means that the first N commands in the batch
executed successfully when
executeBatch() was called.
PreparedStatementobject. 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.
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
PreparedStatementobjects is analogous to error handling for
CallableStatementobjects as it does with
PreparedStatementobjects. 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.