JDBC Batch Insert Update Example

In this tutorial we’ll see how to do a batch insert or update using JDBC batch processing. If you have to run a large number of SQL statements it is much more efficient to run them as a batch rather than as individual commands hitting the DB for each command.

Methods for batch processing in JDBC

In JDBC Statement interface there are following methods for batch support-

addBatch(String sql)- Adds the given SQL command to the list of SQL commands for this Statement object to be executed as a batch.
clearBatch()- Empties this Statement object's current list of SQL commands.
executeBatch()- Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

In PreparedStatement there is one more method-

addBatch()- Add the parameterized SQL to the list of commands to be executed as batch.

JDBC batch insert example

In this batch insert example we’ll insert records in Employee table using PreparedSatement, DB is MySQL.

In MySQL you will have to add rewriteBatchedStatements=true in the connection string which rewrites prepared statements for INSERT into multi-value inserts when executeBatch() is called.

So the queries which are added to batch like individual queries-

INSERT INTO TABLE_NAME (COL1, COL2) VALUES ("val1", "val2") INSERT INTO TABLE_NAME (COL1, COL2) VALUES ("val3", "val4") INSERT INTO TABLE_NAME (COL1, COL2) VALUES ("val5", "val6")

are converted into multi-value insert as-

INSERT INTO TABLE_NAME (COL1, COL2) VALUES ("val1", "val2"), ("val3", "val4"), ("val5", "val6")

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCBatchDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode?rewriteBatchedStatements=true", 
        "root", "admin");
      String insertSQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, DEPARTMENT) values (?, ?, ?)";
      int batchSize = 5;
      // create Statement object
      PreparedStatement preparedStatement = connection.prepareStatement(insertSQL);
      for(int i = 1; i <= 10; i++){
        preparedStatement.setString(1, "TestName"+i);               
        preparedStatement.setString(2, "LName"+i);
        preparedStatement.setString(3, "Dept");
        // Keep adding to batch
        preparedStatement.addBatch();
        // Execute batch in chunks
        if(i%batchSize == 0){
          preparedStatement.executeBatch();
        }
      }
      // For the left over records
      preparedStatement.executeBatch();  

    } catch (ClassNotFoundException | SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }	      	     
  }
}

In the program some important points to note are-

Use of rewriteBatchedStatements=true in the URL- jdbc:mysql://localhost:3306/knpcode?rewriteBatchedStatements=true
In the program total 10 records are inserted in the batch of 5 records. It is a good idea to divide and execute a large batch in smaller chunks of batches. For example suppose you are running a batch of 1000 inserts, rather than sending all the 1000 records to the DB in a single batch it is better to execute batches of 100 records.
If one of the commands in a batch update fails to execute properly, executeBatch() method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch.
In MySQL DB if BatchUpdateException is thrown remaining commands in the batch are not processed but the changes made before the exception are persisted (If you have executed executeBatch() command). For example if you are executing batch for the chunk of 5 records and 7th insert statement throws an exception then the records 1-5 that are already processed are persisted. If you want to rollback all the changes then you should run batch with in a transaction.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCBatchDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode?rewriteBatchedStatements=true", 
        "root", "admin");
      connection.setAutoCommit(false);
      String insertSQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, DEPARTMENT) values (?, ?, ?)";
      int batchSize = 5;
      // create Statement object
      PreparedStatement preparedStatement = connection.prepareStatement(insertSQL);
      for(int i = 1; i <= 10; i++){
        preparedStatement.setString(1, "TestName"+i);    
        preparedStatement.setString(2, "LName"+i);        
        preparedStatement.setString(3, "Dept");
        // Keep adding to batch
        preparedStatement.addBatch();
        // Execute batch in chunks
        if(i%batchSize == 0){
          preparedStatement.executeBatch();
        }
      }
      // For the left over records
      preparedStatement.executeBatch();  
      connection.commit();

    } catch (ClassNotFoundException | SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }	      	     
  }
}

That's all for the topic JDBC Batch Insert Update Example. If something is missing or you have something to share about the topic please write a comment.

You may also like

KnpCode

August 22, 2022