Using JDBC batch methods

The addBatch method of the PreparedStatement class is used for performing batched (or wide) inserts. The following are some guidelines to using this method.

An INSERT statement should be prepared using one of the prepareStatement methods of the Connection class.

// Build the INSERT statement
String sqlStr = "INSERT INTO Departments " +
            "( DepartmentID, DepartmentName ) " +
            "VALUES ( ? , ? )";
// Prepare the statement
PreparedStatement stmt =
     con.prepareStatement( sqlStr );

The parameters for the prepared insert statement should be set and batched as follows:

// loop to batch "n" sets of parameters
for( i=0; i < n; i++ ) 
{
    // Note "stmt" is the original prepared insert statement from step 1.
    stmt.setSomeType( 1, param_1 );
    stmt.setSomeType( 2, param_2 );
    .
    .
    .
    // Note that there are "m" parameters in the statement.
    stmt.setSomeType( m , param_m );

    // Add the set of parameters to the batch and 
    // move to the next row of parameters.
    stmt.addBatch(); 
}

Example:

for( i=0; i < 5; i++ ) 
{    
    stmt.setInt( 1, idValue );
    stmt.setString( 2, name );
    stmt.addBatch();
}

The batch must be executed using the executeBatch method of the PreparedStatement class.

Note that BLOB parameters are not supported in batches.

When using the SQL Anywhere JDBC driver to perform batched inserts, it is recommended that you use a small column size. Using batched inserts to insert large binary or character data into long binary or long varchar columns is not recommended and may degrade performance. The performance can decrease because the SQL Anywhere JDBC driver must allocate large amounts of memory to hold each of the batched insert rows. In all other cases, using batched inserts should provide better performance than using individual inserts.

Discuss this page in DocCommentXchange.