1、批量执行SQL语句

在Java中，当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更高效。

JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch()：清空缓存的数据。

通常我们会遇到两种批量执行SQL语句的情况：

• 多条SQL语句的批量处理；
• 一个SQL语句的批量传参；

2、数据库表结构准备

数据库中提供一个users表，创建语句如下：

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

3、数据的批量插入

以下测试均以向数据表中插入20000条数据为例，对程序执行时间作比较。

3.1、实现层次一：使用Statement

测试代码如下：

@Test
public void testBatchInsert1() {
    long start = System.currentTimeMillis();
    Connection conn = null;
    Statement st = null;
    try {
        // 1.获取数据库连接
        conn = JdbcUtils.getConnection();
        // 2.创建Statement实例
        st = conn.createStatement();
        // 3.插入数据
        for (int i = 1; i <= 20000; i++) {
            String sql = "insert into users(name) values('name_ + " + i + "')";
            st.executeUpdate(sql);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 4.关闭资源
        JdbcUtils.closeResource(conn, st);
    }
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));
}

测试结果如下，合计用时17.554秒：

3.2、实现层次二：使用PreparedStatement

测试代码如下：

@Test
public void testBatchInsert2() {
    long start = System.currentTimeMillis();
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        // 1.获取数据库连接
        conn = JdbcUtils.getConnection();
        // 2.创建PreparedStatement实例
        String sql = "insert into users(name) values(?)";
        ps = conn.prepareStatement(sql);
        // 3.插入数据
        for (int i = 1; i <= 20000; i++) {
            ps.setString(1, "name_" + i);
            ps.executeUpdate();
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 4.关闭资源
        JdbcUtils.closeResource(conn, ps);
    }
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));
}

测试结果如下，合计用时17.762秒：

3.3、实现层次三：在层次二的基础上做优化

修改配置文件，url后面添加?rewriteBatchedStatements=true，mysql服务器默认是关闭批处理的，我们需要通过该参数，让mysql开启批处理的支持：

user=root
password=root
url=jdbc:mysql://172.16.2.105:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

测试代码如下：

/**
 * 使用 addBatch() / executeBatch() / clearBatch()
 */
@Test
public void testBatchInsert3() {
    long start = System.currentTimeMillis();
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        // 1.获取数据库连接
        conn = JdbcUtils.getConnection();
        // 2.创建PreparedStatement实例
        String sql = "insert into users(name) values(?)";
        ps = conn.prepareStatement(sql);
        // 3.插入数据
        for (int i = 1; i <= 20000; i++) {
            ps.setString(1, "name_" + i);
            //每满500条数据执行一次更新
            ps.addBatch();
            if (i % 500 == 0) {
                // 执行批量更新
                ps.executeBatch();
                // 清空执行过的sql
                ps.clearBatch();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 4.关闭资源
        JdbcUtils.closeResource(conn, ps);
    }
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));
}

测试结果如下，20000条数据合计用时1.561秒：

1000000条数据合计用时10.761秒：

3.4、实现层次四：在层次三的基础上进一步优化

测试代码如下：

/**
 * 层次四：在层次三的基础上操作，使用Connection的setAutoCommit(false)关闭自动提交，进行手动提交
 */
@Test
public void testBatchInsert4() {
    long start = System.currentTimeMillis();
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        // 1.获取数据库连接
        conn = JdbcUtils.getConnection();
        // 2.设置为不自动提交数据
        conn.setAutoCommit(false);
        // 3.创建PreparedStatement实例
        String sql = "insert into users(name) values(?)";
        ps = conn.prepareStatement(sql);
        // 4.插入数据
        for (int i = 1; i <= 20000; i++) {
            ps.setString(1, "name_" + i);
            //每满500条数据执行一次更新
            ps.addBatch();
            if (i % 500 == 0) {
                // 执行批量更新
                ps.executeBatch();
                // 清空执行过的sql
                ps.clearBatch();
            }
        }
        // 5.提交数据
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 6.关闭资源
        JdbcUtils.closeResource(conn, ps);
    }
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));
}

测试结果如下，20000条数据合计用时1.586秒：

1000000条数据合计用时8.701秒：

说明：以上测试结果用时仅供参考，会因电脑配置不同而存在差异。

经过上述实践不难发现，最后的层次四的执行效率最高，也说明在大批量插入数据时使用PreparedStatement比Statement的效率要高很多，但是要记得配置文件中开启数据库对批处理的支持，且不要频繁的执行更新和提交。