JDBC Dynamically Insert Multiple Rows to MySQL Example

In this article, we will discuss how to insert multiple records dynamically in the MySQL database. In real-world projects, we basically perform this kind of operation.
Consider we have User POJO class and we will create a list of user objects, then we will dynamically insert all the list of user objects with single INSERT SQL Statement.
Here is sample code:
connection.setAutoCommit(false);
for (Iterator<User> iterator = list.iterator(); iterator.hasNext();) {
    User user = (User) iterator.next();
    preparedStatement.setInt(1, user.getId());
    preparedStatement.setString(2, user.getName());
    preparedStatement.setString(3, user.getEmail());
    preparedStatement.setString(4, user.getCountry());
    preparedStatement.setString(5, user.getPassword());
    preparedStatement.addBatch();
}
int[] updateCounts = preparedStatement.executeBatch();
System.out.println(Arrays.toString(updateCounts));
connection.commit();
connection.setAutoCommit(true);

Video

This tutorial is explained in below YouTube video:

Technologies used

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

Steps to process insert Batch SQL statements with JDBC

  1. Establishing a connection with the MySQL database
  2. Create a statement using a connection object
  3. Iterate over Users object and dynamically insert as a batch.
  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 Dynamically Insert Multiple Rows to MySQL Example

PreparedStatement interface provides addBatch() and executeBatch() methods to perform batch operations.

addBatch()

  • Adds a set of parameters to this PreparedStatement object's batch of commands.

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.

Create a User Class

class User {
 private int id;
 private String name;
 private String email;
 private String country;
 private String password;

 public User(int id, String name, String email, String country, String password) {
  super();
  this.id = id;
  this.name = name;
  this.email = email;
  this.country = country;
  this.password = password;
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getCountry() {
  return country;
 }

 public void setCountry(String country) {
  this.country = country;
 }

 public String getPassword() {
  return password;
 }

 public void setPassword(String password) {
  this.password = password;
 }
}
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.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

public class InsertMultipleRows {

    public static void main(String[] args) {
        List < User > list = new ArrayList < > ();
        list.add(new User(100, "Denial", "[email protected]", "US", "123"));
        list.add(new User(200, "Rocky", "[email protected]", "US", "123"));
        list.add(new User(300, "Steve", "[email protected]", "US", "123"));
        list.add(new User(400, "Ramesh", "[email protected]", "India", "123"));

        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);
            for (Iterator < User > iterator = list.iterator(); iterator.hasNext();) {
                User user = (User) iterator.next();
                preparedStatement.setInt(1, user.getId());
                preparedStatement.setString(2, user.getName());
                preparedStatement.setString(3, user.getEmail());
                preparedStatement.setString(4, user.getCountry());
                preparedStatement.setString(5, user.getPassword());
                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 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] + "   ");
        }
    }

}

class User {
    private int id;
    private String name;
    private String email;
    private String country;
    private String password;

    public User(int id, String name, String email, String country, String password) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.country = country;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}
Output:
[1, 1, 1, 1]

Comments