JDBC Batch/Bulk Insert, Update, Delete Java Example

SQL JDBC Batch/Bulk Insert, Update, Delete Java Example

JDBC Statement/PreparedStatament Batch/Bulk Insert, Update and Delete operation in Java.

PreparedStatement allows insert a record one by one using the execute() method. Say for example, there is a batch of records that need to be inserted into the database.

There will be a big performace hit as shown in the below implementation because in order to insert 2 records in the database the execute() method is getting called twice. This is a real performance hit in case of insertion of million records.

Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
try {
 dbConnection = getDBConnection();
 preparedStatement = dbConnection.prepareStatement(insertQuery
   .toString());
 preparedStatement.setString(1, "John");
 preparedStatement.setString(2, "New York");
 preparedStatement.execute();
 
 preparedStatement.setString(1, "Andy");
 preparedStatement.setString(2, "Canada");
 preparedStatement.execute();
 
} catch (SQLException e) {
} 

Instead of using the above implemention, we will use the addBatch() and executeBatch() implementation.

Steps to do so :

1) Create the PreparedStatement Object
2) For each insert record perform SQL Injection and call addBatch() method on PreparedStatement Object
3) Once done, make a call to executeBatch() method

Below is sample implementation

package test;

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

public class BatchUpdateInsertDelete {
 public static void main(String[] argv) {
  try {
   Connection dbConnection = null;
   PreparedStatement preparedStatement = null;
   String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
   try {
    dbConnection = getDBConnection();
    preparedStatement = dbConnection.prepareStatement(insertQuery
      .toString());

    for (int i = 0; i < 100; i++) {
     preparedStatement.setString(1, "John");
     preparedStatement.setString(2, "New York");
     preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();

   } catch (SQLException e) {
   } finally {
    if (preparedStatement != null) {
     preparedStatement.close();
    }
    if (dbConnection != null) {
     dbConnection.close();
    }
   }
  } catch (SQLException e) {
  }

 }

 private static Connection getDBConnection() {
  Connection dbConnection = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   dbConnection = DriverManager.getConnection(
     "jdbc:oracle:thin:@localhost:1521:javacodeimpl", "user",
     "password");
   return dbConnection;
  } catch (SQLException e) {
   System.out.println(e.getMessage());
  } catch (ClassNotFoundException e) {
   System.out.println(e.getMessage());
  }
  return dbConnection;
 }
}

The above code has a small limitation. In case of million records, if executeBatch() will be called then it will throw OutOfMemoryError. In order to resolve this, batch update should/must be done in a batch of 1000's or 2000's. Add 1000 records in a batch and call executeBatch().

Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertQuery = "Insert Into Employee(name, address) Values (? , ?)";
try {
 dbConnection = getDBConnection();
 preparedStatement = dbConnection.prepareStatement(insertQuery
   .toString());
 int batchSize = 1000;
 int count = 0;
 for (int i = 0; i < 100000; i++) {
  preparedStatement.setString(1, "John");
  preparedStatement.setString(2, "New York");
  preparedStatement.addBatch();

  if (++count % batchSize == 0) {
   preparedStatement.executeBatch();
  }
 }
 preparedStatement.executeBatch();// Insert rest of the records

} catch (SQLException e) {
}