在这篇文章中，我们将讨论如何在MySQL数据库中动态地插入多条记录。在现实世界的项目中，我们基本上会执行这种操作。
考虑到我们有User POJO类，我们将创建一个用户对象的列表，然后我们将用一条INSERT 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);

使用的技术

• JDK - 1.8或更高版本
• MySQL - 5.7.12
• IDE - Eclipse Neon
• jdbc api - 4.2

用JDBC处理插入式批量SQL语句的步骤

1. 建立与MySQL数据库的连接
2. 使用连接对象创建一个语句
3. 遍历用户对象，并作为一个批次动态插入。
4. 使用try-with-resources语句来自动关闭JDBC资源

从JDBC 4.0开始，我们不需要在代码中加入'Class.forName()'来加载JDBC驱动。当调用'getConnection'方法时，'DriverManager'将自动在初始化时加载的JDBC驱动程序和那些使用与当前应用程序相同的类加载器明确加载的JDBC驱动程序中加载合适的驱动程序。

任何在你的classpath中发现的JDBC 4.0驱动都会被自动加载。(然而，你必须用Class.forName方法手动加载任何在JDBC 4.0之前的驱动程序。)

JDBC动态插入多行到MySQL实例

PreparedStatement接口提供了addBatch()和executeBatch()方法来执行批量操作。

addBatch()

• 将一组参数添加到这个PreparedStatement对象的命令批中。

executeBatch()

• 提交一批命令给数据库执行，如果所有命令都成功执行，则返回一个更新计数数组。返回的数组中的int元素是按照批次中的命令来排序的，这些命令是按照它们被添加到批次中的顺序来排序的。

创建一个用户类

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", "denial@gmail.com", "US", "123"));
        list.add(new User(200, "Rocky", "rocky@gmail.com", "US", "123"));
        list.add(new User(300, "Steve", "steve@gmail.com", "US", "123"));
        list.add(new User(400, "Ramesh", "ramesh@gmail.com", "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;
    }
}

输出：

[1, 1, 1, 1]