Using prepared statements for wide inserts

The PreparedStatement.addBatch() method is useful 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 Connection.prepareStatement() methods.

// 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 should then be executed using the PreparedStatement.executeUpdate() method.

It should be noted that only the PreparedStatement.addBatch() method is supported and that the PreparedStatement.executeUpdate() method needs to be called to execute the batch. None of batch methods for the Statement object (i.e. Statement.addBatch(), Statement.clearBatch(), Statement.executeBatch() ) are supported since these methods are completely optional and not very useful. For such static batches, it is best to call Statement.execute() or Statement.executeQuery() on a single string with the batched statements wrapped inside a BEGIN...END.

Notes

BLOB parameters are not supported in batches.
String/Binary parameters are supported but the size of the string/binary parameter is an issue. By default the string/binary parameter is restricted to 255 characters or 510 bytes. The reason for the restriction is due to the underlying ODBC protocol and will not be discussed here. For further information, it is best to view the documentation on passing arrays of parameters in ODBC. If, however, an application needs to pass larger string or binary parameters within a batch then an additional method, setBatchStringSize, has been provided to increase the size of the string/binary parameter. Note that this method must be called prior to the first addBatch() call. If the method is called after the first addBatch() call, then the new size setting will be ignored. So, when calling this method to change the size of a string/binary parameter, the application needs to know ahead of time what the maximum string or binary value for that parameter will be.

To use the setBatchStringSize method, you must modify the "code" above as follows:

// You need to cast "stmt" to an IPreparedStatment object 
// to change the size of string/binary parameters.
ianywhere.ml.jdbcodbc.IPreparedStatement _stmt =
    (ianywhere.ml.jdbcodbc.IPreparedStatement)stmt;

// Now, for example, change the size of string parameter 4 
// from the default 255 characters to 300 characters.
// Note that string parameters are measured in "characters".
_stmt.setBatchStringSize( 4, 300 ); 

// Change the size of binary parameter 6 
// from the default 510 bytes to 750 bytes.
// Note that binary parameters are measured in "bytes".
_stmt.setBatchStringSize( 6, 750 ); 

// loop to batch "n" sets of parameters 
// where n should not be too large
for( i=0; i < n; i++ )
{
    // stmt is the 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(); 
}

The maximum string/binary size of a parameter should be modified with caution. If the maximum is set too high, then the additional memory allocation cost will probably override any performance gained from using the batch. Also, there is a good chance the application will not know ahead of time what the maximum string or binary value for a particular parameter is. The recommendation then is to not change the maximum string or binary size of a parameter, but rather to use the batch method until a string or binary value larger than the current/default maximum is encountered. The application can, at that point, call executeBatch() to execute the parameters that are currently batched, then call the regular set and executeUpdate() method to execute until the large string/binary parameters have been handled, then switch back into batch mode when smaller string/binary parameters are encountered.

Discuss this page in DocCommentXchange.
Send feedback about this page using email.