The CData JDBC-ODBC Bridge enables you to take advantage of the bulk load support in ODBC data sources through the JDBC batch API. You can use the batch API to execute related SQL data manipulation statements simultaneously.

Using the JDBC Batch API

The following examples show how to execute bulk operations with the PreparedStatement class.

Bulk Insert

To perform a bulk insert with a PreparedStatement, call addBatch for each set of parameters you want to execute as part of the bulk insert. After adding all the sets of parameters to the batch, you can execute the bulk insert by calling executeBatch.

The executeBatch method returns an array that contains the update counts for each statement. For example:

To retrieve the primary keys for new records, set the Statement.RETURN_GENERATED_KEYS flag when you call prepareStatement and then call the getGeneratedKeys method after you call executeBatch.

String query = "INSERT INTO Account (Name) VALUES (?)"; 
PreparedStatement pstmt = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);

pstmt.setString(1, "Jon Doe"); 
pstmt.addBatch();

pstmt.setString(1, "John"); 
pstmt.addBatch();

int[] r = pstmt.executeBatch();
for(int i: r)
  System.out.println(i);

ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()){    
  System.out.println(rs.getString("Id"));
}

Bulk Update

To perform a bulk update with a PreparedStatement, call addBatch for each set of parameters you want to execute as part of the bulk update. After adding all the sets of parameters to the batch, call executeBatch.

The executeBatch method returns the update counts for each statement in an array. For example:

String query = "UPDATE Account SET Name = ? WHERE Id = ?"; 
PreparedStatement pstmt = conn.prepareStatement(query);

pstmt.setString(1,"Jon Doe");
pstmt.setString(2,"MyId"); 
pstmt.addBatch();		

pstmt.setString(1,"John");
pstmt.setString(2,"MyId2"); 
pstmt.addBatch();		

int[] r = pstmt.executeBatch();
for(int i: r)
  System.out.println(i);

Bulk Delete

To perform a bulk delete with a PreparedStatement, call addBatch for each set of parameters you want to execute as part of the bulk delete. After adding all the sets of parameters to the batch, call executeBatch.

The executeBatch method returns the update counts for each statement in an array. For example:

String query = "DELETE FROM Account WHERE Id = ?";
PreparedStatement pstmt = conn.prepareStatement(query);

pstmt.setString(1,"MyId1"); 
pstmt.addBatch();		

pstmt.setString(1,"MyId2");
pstmt.addBatch();		

int[] r = pstmt.executeBatch();
for(int i: r)
  System.out.println(i);

Setting the Batch Size

Setting the maximum batch size can be necessary when the server has limitations on the size of the request that can be submitted. Set the BatchSize property to split the entire batch into batches of the specified value. Each batch is submitted to the server individually.

Or, set BatchSize to 0 to submit the entire batch.