JDK 5 Documentation v1.2.2, Java 2 SDK 英文文档

JDBC^TM Guide: Getting Started

6 Batch Updates

The batch update facility allows multiple update operations to be submitted to a database for processing at once. Submitting multiple updates together, instead of individually, can greatly improve performance in some situations. Statement, PreparedStatement, and CallableStatement objects can be used to submit batch updates.

6.1 Use of batch updates

6.1.1 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 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();

In the example, autocommit mode is disabled to prevent JDBC from committing the transaction when 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.

6.1.2 Prepared Statements

The batch update facility is used with prepared statements to associate multiple sets of input parameter values with a single 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.

// 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();

Finally, 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.

6.1.3 Callable Statements

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

Contents | Prev | Next

jdbc@eng.sun.com or jdbc-business@eng.sun.com