The batch update facility allows multiple SQL statements to be submitted to a data source for processing at once.
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.
Only DDL and DML commands that return a simple update count may be executed as part of a batch (no result set)
// 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, 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.
// 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();
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.
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