Array (Batch) Inserts with JDBC

If you have many records to insert into a table, it is almost always much faster to insert batches of records instead of one at a time.

In Oracle this is known as array processing, but in JDBC it is known as batching.

There are two types of batching that achieve the same goal, known as Standard Batching and Oracle Batching.

Standard Batching

In Standard Batching, you prepare a statement as normal. Then instead of running bind and then execute in a loop for each record, you instead bind and call addBatch in a loop. When addBatch is called, the statement is not executed on the database. The bind variables are stored in the statement object, leaving the statement ready to receive another set of bind variables.

When the total number of batches have been added (generally only batch 100 to 1000 statements between executes), the executeBatch method can be used to send the entire batch to the database. For example:

PreparedStatement stmt = 
    conn.prepareStatement("insert into test_java_array (c1, c2, c3) values (?, ?, ?)");

for (int i=0; i<100; i++) {
    // bind
    stmt.setInt(1, 1);
    stmt.setDouble(2, 2000.123);    
    stmt.setString(3, "abcdefghijk");
    // add batch
    stmt.addBatch();
}
int[] totalInserted = new int[100];
totalInserted = stmt.executeBatch();

Provided no exception occurs, the totalInserted array will contain the same number of elements as were in the batch.

Standard Batching Exceptions

When running any database statement, there is a chance it could fail. So what happens if one record in a 100 record batch fails?

Using the 11gr2 JDBC drivers, the executeBatch method will throw a BatchUpdateException which the code must catch and handle.

The important thing to note is that if an exception is raised, either some or none of the batch was successfully added to the database. The records are added to the database in the same order they were added to the batch, and the processing of a batch stops as soon as any error is encountered. This means that in a batch of 100, if record 10 fails, then only 1 to 9 will be on the database, 10 is known to be invalid and 11 to 100 have not even been tried. The only way to insert records 11 to 100 is to build up a new batch.

To find out how many records were inserted successfully, call the getUpdateCounts method on the BatchUpdateException object. This will return an array with an element for each record that was successful, and each element should have the value -2, indicating SUCCESSNOINFO. The indices of the array line up with the indices of the the batches added to the statement, which means that the biggest array index indicates the last batch record that was successfully inserted.

Putting the exception handling and batching together gives code like the following:

/*

To run this demo, you need to create a table as below:

create table test_java_array (
  c1 integer,
  c2 number,
  c3 varchar2(50)
);

This code uses standard JDBC batching

 */

import oracle.jdbc.*;
import java.sql.*;

public class TestDBOracleBatch {

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException
    {
        DriverManager.registerDriver
           (new oracle.jdbc.OracleDriver());

        String url =     "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";

        Connection conn =
        DriverManager.getConnection(url,"sodonnel","sodonnel");

        conn.setAutoCommit(false);

        PreparedStatement stmt = 
            conn.prepareStatement("insert into test_java_array     (c1, c2, c3) values (?, ?, ?)");

        for (int i=0; i<100; i++) { 
            stmt.setInt(1, 1);
            stmt.setDouble(2, 2.123);    
            stmt.setString(3, "abcdefghijk");
            stmt.addBatch();
        }
        int[] totalInserted = new int[100];
        try {
            totalInserted = stmt.executeBatch();
        } catch(BatchUpdateException e) {
            totalInserted = e.getUpdateCounts();
            // handle bad record, and re-batch the remaining?
        }
        System.out.println (totalInserted.length);
        conn.commit();
        stmt.close();
    }
}

With the older ojdbc14.jar Oracle drivers, the exception behaviour is different. If any part of the batch fails, there is no way to tell what record failed, so the entire batch much be rolledback. To work with batching in the older drivers, you probably need to set a savepoint before each batch.

Oracle Batching

Oracle batching is a similar concept to standard batching. Instead of calling addBatch repeatedly followed by executeBatch, the standard execute method is used after setting a property on the statement object. For example:

PreparedStatement stmt = 
    conn.prepareStatement("insert into test_java_array (c1, c2, c3) values (?, ?, ?)");

((OraclePreparedStatement)stmt).setExecuteBatch(100);

int[] totalInserted = new int[100];
for (int i=0; i<100; i++) {
    stmt.setInt(1, 1);
    stmt.setDouble(2, 2.123);   
    stmt.setString(3, "abcdefghijk");
    stmt.execute();
}

When the setExecuteBatch method is set on the statement object, it changes the behaviour of the execute method. When it is called it behaves like addBatch, in that it doesn't actually add the record to the database, but stores the bind variables inside the statement object. After execute has been called 100 times (the number set in setExecuteBatch) the entire batch is sent to the database.

If the loop does not run 100 times (and force the execute against the database), issuing a commit will cause any pending batches to be flushed to the database.

Oracle Batching Exceptions

The exception handling for Oracle Batching is almost the same as for standard batching, and should be handled in the same way.

Note that for the ojdbc14.jar drivers, instead of raising a BatchUpdateException, the execute method can raise an SQLException. Unlike with Standard Batching, the entire batch does not need to be rolled back. Calling getUpdateCount on the statement object will indicate the number of rows successfully processed.

/*

To run this demo, you need to create a table as below:

create table test_java_array (
  c1 integer,
  c2 number,
  c3 varchar2(50)
);

This code uses Oracle batching

*/

import oracle.jdbc.*;
import java.sql.*;

public class TestDBOracleBatch2 {

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException
    {
       DriverManager.registerDriver
           (new oracle.jdbc.OracleDriver());

        String url =     "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";

        Connection conn =
        DriverManager.getConnection(url,"sodonnel","sodonnel");

        conn.setAutoCommit(false);

        PreparedStatement stmt = 
            conn.prepareStatement("insert into test_java_array     (c1, c2, c3) values (?, ?, ?)");

        ((OraclePreparedStatement)stmt).setExecuteBatch(100);

        int[] totalInserted = new int[100];
        for (int i=0; i<100; i++) {
            stmt.setInt(1, 1);
            stmt.setDouble(2, 2.123);    
            stmt.setString(3, "abcdefghijk");

            try {
                stmt.execute();
            } catch(BatchUpdateException e) {
            // } catch(SQLException e) { FOR ojdbc14
                System.out.println ("in exception!");
                totalInserted = e.getUpdateCounts();
            }
        }
        System.out.println ("Row count from the statement handle:");
        System.out.println (stmt.getUpdateCount());
        System.out.println ("Row count from exception handler:");
        System.out.println (totalInserted.length);
        System.out.println (totalInserted[49]);
        conn.commit();
        stmt.close();
    }
}