Contrary to what the name suggests, batch updates or
update batching allows invoking multiple DML operations
including Insert, Update, Delete, in one database call,
minimizing network roundtrips. Oracle JDBC supports two distinct
models of update batching: the standard model and Oracle-specific
model.
Standard
Update Batching (explicit model)
In the standard update batching, you manually
add operations to the batch and then explictly choose when
to execute the batch. Recommended when code portability across
different database and JDBC drivers is a higher priority than
performance.
Oracle
Update Batching (implicit and faster model)
Defines a batch value, the number of operations
you want to process per round trip, which causes the
statements to be automatically and implicitly processed as
soon as that many operations have been added to the batch.
Oracle update batching is usually faster than the standard
update batching but only supports PreparedStatement.
Best
Practices
- Always disable auto-commit mode
with 'Update Batching';
- Use a batch size of around 10;
- Don't mix the standard and Oracle
models of 'Update Batching'.
- Explicitly commit the transaction
Example
of Standard Update Batching
The following example illustrates how
you use the Standard JDBC 'Update Batching' feature.
It assumes you have imported the oracle.driver.*interfaces.
//ds is a DataSource object
Connection conn = ds.getConnection();
//Always disable auto-commit when using update batching
conn.setAutoCommit(false);
Statement s = conn.createStatement();
s.addBatch("insert into dept values ('23', 'Sales', 'USA')");
s.addBatch("insert into dept values ('24', 'Blue Sky', 'Montana')");
s.addBatch("insert into dept values ('25', 'Applications',
'India')");
//Manually execute the bacth
s.executeBatch();
s.addBatch("insert into dept values ('26', 'HR', 'Mongolia')");
s.executeBatch();
conn.commit();
ps.close();
...
Example
of Oracle Update Batching
The same example using the Oracle 'Update Batching'
extension. It assumes you have imported the oracle.driver.*interfaces.
//ds is a DataSource object
Connection conn = ds.getConnection();
//Always disable auto-commit when using update batching
conn.setAutoCommit(false);
PreparedStatement ps =
conn.prepareStatement("insert into dept values (?, ?, ?)");
//Change batch size for this statement to 3
((OraclePreparedStatement)ps).setExecuteBatch (3);
//--------#1------------
ps.setInt(1, 23);
ps.setString(2, "Sales");
ps.setString(3, "USA");
ps.executeUpdate(); //JDBC queues this for later execution
//--------#2------------
ps.setInt(1, 24);
ps.setString(2, "Blue Sky");
ps.setString(3, "Montana");
ps.executeUpdate(); //JDBC queues this for later execution
//--------#3------------
ps.setInt(1, 25);
ps.setString(2, "Applications");
ps.setString(3, "India");
ps.executeUpdate(); //The queue size equals the batch value
of 3
//JDBC sends the requests to the database
//--------#1------------
ps.setInt(1, 26);
ps.setString(2, "HR");
ps.setString(3, "Mongolia");
ps.executeUpdate(); //JDBC queues this for later execution
((OraclePreparedStatement)ps).sendBatch(); // JDBC sends
the queued request
conn.commit();
ps.close();
...
Bookmark
