Considerations regarding addBatch(String)

java, jdbc, prepared-statement

Solution

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.

When you send several SQL statements to the database at once, you reduce the amount of communication overhead, resulting in improving performance.

`addBatch(String sql)` is defined in the interface `java.sql.Statement` and `addBatch()` is defined in the `java.sql.PreparedStatement` is one of the subinterface which `extends Statement`. (Other is `java.sql.CallableStatement`)

According to JAVA Doc

`addbatch(String sql)` method cannot be called on a `PreparedStatement` or `CallableStatement` and sql - typically this is a SQL `INSERT` or `UPDATE` statement.

`addBatch()` is preferred If you want bulk insertion or bulk update operations.

There are few blocking factors on batch exections which are

DB make/version.
JDBC driver make/version.

For Example: If you're using MySQL, use the latest driver add rewriteBatchedStatements=true to your connection string to make statement.addBatch() actually create batch inserts.

Also, You should make sure that auto commit is false. Maintain a counter which and reaching on that counter executeBatch which help when Available heap memory in Java is constraint.

Example:

conn.setAutoCommit(false);  
PreparedStatement pStmt = conn.prepareStatement("INSERT INTO mytable (field1,field2) VALUES (?,?)");

for all values:
  pStmt.setString(1,val1);
  pStmt.setString(2,val2);
  pStmt.addBatch();    
  if ( i % 1000 == 0) {  
            pstmt.executeBatch();// Execute every 1000 items  
  } 
pstmt.executeBatch(); 
conn.commit();

Problem

Next to the `addBatch()` method of `PreparedStatement` there is also an `addBatch(String)` method in the `Statement` class. I want to process a series of different sql statements and am looking for some clarification on what addBatch(String) means performance-wise. Is it safe (and fast) to use this method or is it better to group similar sql-statements in Java and execute them in groups?

Original source