10. 批量插入

Devops海洋的渔夫

发布于 2022-01-17 14:53:01

1K0

发布于 2022-01-17 14:53:01

文章被收录于专栏：Devops专栏Devops专栏

10. 批量插入

前言

上一章节，我们使用 PreparedStatement 操作了 BLOB 字段，下面我们再来看看批量插入的操作。

批量插入

1. 批量执行SQL语句

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

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

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

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

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

2. 高效的批量插入

举例：向数据表中插入20000条数据

数据库中提供一个goods表。创建如下：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

测试插入数据

mysql> insert into goods(name) values('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into goods(name) values('test1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | test  |
|  2 | test1 |
+----+-------+
2 rows in set (0.00 sec)

mysql>

2.1 实现层次一：使用Statement插入20000条数据

    //实现层次一：使用Statement
    //向数据表中插入20000条数据
    @Test
    public void test01() throws Exception {
        //1.获取连接
        Connection conn = JDBCUtils.getConnection();
        Statement st = conn.createStatement();

        //2.执行插入数据
//        String sql = "insert into goods(name) values('test1')";
//        st.executeUpdate(sql);
        //向数据表中插入20000条数据
        for (int i = 1; i <= 20000; i++) {
            String sql = "insert into goods(name) values('test " + i +"')";
            st.executeUpdate(sql);
        }
    }

测试执行如下：

执行之后的mysql数据：

mysql> select * from goods limit 10;
+----+--------+
| id | NAME   |
+----+--------+
|  3 | test1  |
|  4 | test1  |
|  5 | test 1 |
|  6 | test 2 |
|  7 | test 3 |
|  8 | test 4 |
|  9 | test 5 |
| 10 | test 6 |
| 11 | test 7 |
| 12 | test 8 |
+----+--------+
10 rows in set (0.00 sec)

mysql> select count(1) from goods limit 10;
+----------+
| count(1) |
+----------+
|    20002 |
+----------+
1 row in set (0.00 sec)

2.2 实现层次二：使用PreparedStatement插入20000条数据

在上面可以看到耗时挺久的，下面我们改用 PreparedStatement 来插入数据看看。

//实现层次二：使用PreparedStatement插入20000条数据
@Test
public void test02() throws Exception {
    //1.记录执行开始时间
    long start = System.currentTimeMillis();

    //2.获取数据库连接
    Connection conn = JDBCUtils.getConnection();

    //3.预编译SQL
    String sql = "insert into goods(name) values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    //4.设置插入数据以及执行
    for (int i = 1; i <= 20000; i++) {
        ps.setString(1, "test" + i);
        ps.executeUpdate();
    }

    //5.计算结束时间
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));//

    //6.关闭资源
    JDBCUtils.closeResource(conn, ps);
}

测试执行如下：

2.3 实现层次三：优化效率，将逐条SQL执行改为批处理执行

配置mysql启用批处理执行 rewriteBatchedStatements=true

user=root
password=*****
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driverClass=com.mysql.cj.jdbc.Driver

测试代码

/**
 * 实现层次三：使用PreparedStatement插入20000条数据
 * 修改1：使用 addBatch() / executeBatch() / clearBatch()
 * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
 *         ?rewriteBatchedStatements=true 写在配置文件的url后面
 *
 *
 * @throws Exception
 */
@Test
public void test03() throws Exception {
    //1.记录执行开始时间
    long start = System.currentTimeMillis();

    //2.获取数据库连接
    Connection conn = JDBCUtils.getConnection();

    //3.预编译SQL
    String sql = "insert into goods(name) values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    //4.设置插入数据以及执行
    for (int i = 1; i <= 20000; i++) {
        ps.setString(1, "test" + i);

        //执行批处理
        //1.“攒”sql
        ps.addBatch();
        if(i % 500 == 0){ // 当达到500，则开始批处理
            //2.执行
            ps.executeBatch();
            //3.清空
            ps.clearBatch();
        }
    }

    //5.计算结束时间
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));//

    //6.关闭资源
    JDBCUtils.closeResource(conn, ps);
}

执行测试如下：

2.4 实现层次四：优化效率，将批处理的多次提交设置为最后统一 commit() 提交

/**
 * 实现层次四：优化效率，将批处理的多次提交 设置为 最后统一 commit() 提交
 * 使用Connection 的 setAutoCommit(false)  /  commit()
 *
 * @throws Exception
 */
@Test
public void test04() throws Exception {
    //1.记录执行开始时间
    long start = System.currentTimeMillis();

    //2.获取数据库连接
    Connection conn = JDBCUtils.getConnection();

    //1.设置为不自动提交数据
    conn.setAutoCommit(false);

    //3.预编译SQL
    String sql = "insert into goods(name) values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    //4.设置插入数据以及执行
    for (int i = 1; i <= 20000; i++) {
        ps.setString(1, "test" + i);

        //执行批处理
        //1.“攒”sql
        ps.addBatch();
        if(i % 500 == 0){ // 当达到500，则开始批处理
            //2.执行
            ps.executeBatch();
            //3.清空
            ps.clearBatch();
        }
    }

    //2.提交数据
    conn.commit();

    //5.计算结束时间
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));//

    //6.关闭资源
    JDBCUtils.closeResource(conn, ps);
}

测试执行如下：