JDBC Batch Insert MySQL Example

In this article, We will discuss JDBC Batch insert examples in MySQL database. Sometimes we need to run bulk queries of a similar kind for a database, for example, loading data from CSV files to relational database tables. As we know that we have the option to use Statement or PreparedStatement to execute queries. Apart from that JDBC provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.
You can batch both SQL inserts, updates, and deletes. It does not make sense to batch select statements.
There are two ways to execute batch inserts:
  1. Using a Statement
  2. Using a PreparedStatement
This article explains both ways.

Technologies used

  • JDK - 1.8 or later
  • MySQL - 5.7.12
  • IDE - Eclipse Neon
  • JDBC API - 4.2

Steps to process insert SQL statement with JDBC

  1. Establishing a connection.
  2. Create a statement.
  3. Execute the query.
  4. Using try-with-resources statements to automatically close JDBC resources
From JDBC 4.0, we don't need to include 'Class.forName()' in our code, to load JDBC driver. When the method 'getConnection' is called, the 'DriverManager' will automatically load the suitable driver among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.
Any JDBC 4.0 drivers that are found in your classpath are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName.)

JDBC Statement Batch Insert Example

The Statement interface provides below two methods to perform batch operations
  1. addBatch(StringSQLl)
  2. executeBatch()

Statement.addBatch(String sql)

Adds the given SQL command to the current list of commands for this Statement object. The commands in this list can be executed as a batch by calling the method executeBatch.

Statement.executeBatch()

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.
The below example demonstrates the batch insertion operations using Statement interface:
package com.javaguides.jdbc.batch;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

/**
 * Insert Batch operation using Statement Interface
 * @author Ramesh Fadatare
 *
 */
public class BatchInsertExample {

    public static void main(String[] args) {
        batchUpdate();
    }
    private static void batchUpdate() {
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement()) {
            connection.setAutoCommit(false);
            statement.addBatch("INSERT INTO Users VALUES (2, 'Pramod', 'pramod@gmail.com', 'India', '123');");
            statement.addBatch("INSERT INTO Users VALUES (3, 'A', 'a@gmail.com', 'India', '123');");
            statement.addBatch("INSERT INTO Users VALUES (4, 'B', 'b@gmail.com', 'India', '123');");
            statement.addBatch("INSERT INTO Users VALUES (5, 'C', 'c@gmail.com', 'India', '123');");
            statement.addBatch("INSERT INTO Users VALUES (6, 'D', 'd@gmail.com', 'India', '123');");
            int[] updateCounts = statement.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
            connection.commit();
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

    public static void printBatchUpdateException(BatchUpdateException b) {

        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();

        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }
}
Output:
[1, 1, 1, 1, 1]

JDBC Batch Insert Exmaple using PreparedStatement

It is also possible to have a parameterized batch insert or update, as shown in the following code fragment, where con is a Connection object:
package com.javaguides.jdbc.batch;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

/**
 * Insert Batch operation using PreparedStatement Interface
 * @author Ramesh Fadatare
 *
 */
public class BatchInsertExample {

    public static void main(String[] args) {
        parameterizedBatchUpdate();
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

    private static void parameterizedBatchUpdate() {

        String INSERT_USERS_SQL = "INSERT INTO users" + "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            connection.setAutoCommit(false);

            preparedStatement.setInt(1, 20);
            preparedStatement.setString(2, "a");
            preparedStatement.setString(3, "a@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 21);
            preparedStatement.setString(2, "b");
            preparedStatement.setString(3, "b@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 22);
            preparedStatement.setString(2, "c");
            preparedStatement.setString(3, "c@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 23);
            preparedStatement.setString(2, "d");
            preparedStatement.setString(3, "d@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
            connection.commit();
            connection.setAutoCommit(true);
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printBatchUpdateException(BatchUpdateException b) {

        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();

        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }
}
Output:
[1, 1, 1, 1]
The Connection.commit method makes the batch of updates to the Users table permanent. This method needs to be called explicitly because the auto-commit mode for this connection was disabled previously.
The following line enables auto-commit mode for the current Connection object.
connection.setAutoCommit(true);
Now each statement in the example will automatically be committed after it is executed, and it no longer needs to invoke the method commit.

Comments