Oracle JDBC: Update Batching

http://www.oracle.com/technology/products/oracle9i/daily/jun07.html

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