JDBC - Batch insert example

Posted on April 11, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Neon.3

The following examples show you how to perform a batch operation to insert bulk records into a relational database using the JDBC API.

The PreparedStatement#addBatch() method add a statement into a batch and PreparedStatement# executeBatch() method submits a batch of statements to the database for execution.

The following example inserts 200 records in a single batch using the JDBC batch operation.

JDBCBatchExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class JDBCBatchExample {
   private static final String INSERT_SQL = "INSERT INTO BOOKS " 
           + "(NAME, AUTHOR) VALUES (?,?)";

   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

         conn.setAutoCommit(false);
         try (PreparedStatement stmt = conn.prepareStatement(INSERT_SQL);) {

            // Insert sample records
            for (int i = 0; i < 200; i++) {
               stmt.setString(1, "Java");
               stmt.setString(2, "Sunil Singh");
               
               //Add statement to batch
               stmt.addBatch();
            }
            //execute batch
            stmt.executeBatch();
            conn.commit();
            System.out.println("Transaction is commited successfully.");
         } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
               try {
                  System.out.println("Transaction is being rolled back.");
                  conn.rollback();
               } catch (Exception ex) {
                  ex.printStackTrace();
               }
            }
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

This program may generate an OutOfMemoryError, if we insert large number of records in a single batch.

To avoid this problem, we can divide large number of records into n batch and execute every batch one-by-one.

The following example creates a batch of 1000 records and send to database for execution until all records are processed.

JDBCBatchExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class JDBCBatchExample {
   private static final String INSERT_SQL = "INSERT INTO BOOKS " 
           + "(NAME, AUTHOR) VALUES (?,?)";

   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

         conn.setAutoCommit(false);
         try (PreparedStatement stmt = conn.prepareStatement(INSERT_SQL);) {

            // Insert sample records
            int counter=1;
            for (int i = 0; i < 10000; i++) {
               stmt.setString(1, "Java");
               stmt.setString(2, "Sunil Singh");
               
               //Add statement to batch
               stmt.addBatch();
               
               //Execute batch of 1000 records
               if(i%1000==0){
                  stmt.executeBatch();
                  conn.commit();
                  System.out.println("Batch "+(counter++)+" executed successfully");
               }
            }
            //execute final batch
            stmt.executeBatch();
            conn.commit();
            System.out.println("Final batch executed successfully");
         } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
               try {
                  System.out.println("Transaction is being rolled back.");
                  conn.rollback();
               } catch (Exception ex) {
                  ex.printStackTrace();
               }
            }
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Output

Batch 1 executed successfully
Batch 2 executed successfully
Batch 3 executed successfully
Batch 4 executed successfully
Batch 5 executed successfully
Batch 6 executed successfully
Batch 7 executed successfully
Batch 8 executed successfully
Batch 9 executed successfully
Batch 10 executed successfully
Final batch executed successfully