About
The batch update facility allows multiple SQL statements to be submitted to a data source for processing at once.
Submitting multiple SQL statements, instead of individually, can greatly improve performance. Statement, PreparedStatement, and CallableStatement objects can be used to submit batch updates.
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.
Articles Related
Prerequisites
Only DDL and DML commands that return a simple update count may be executed as part of a batch (no result set)
Example
Statement
// 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();
If your database supports returning an update count that may exceed Integer.MAX_VALUE, use the method executeLargeBatch
In 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.
PreparedStatement
// turn off autocommit
con.setAutoCommit(false);
Chapter 14 Batch Updates 125
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();
Management
Suppress
If an application decides not to submit a batch of commands that has been constructed for a statement, it can call the method Statement.clearBatch to clear the batch of all commands.
Return Value
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.
Each element contains either:
- an update count
- or the generic ‘success’ indicator SUCCESS_NO_INFO.
Documentation / Reference
- chapter 14 of the 4.2 specification Batch Updates