jdbc批量插入的4种方式【百万条数据插入只需几秒】

mysql数据库准备

#打开数据库test01;
use test01;
#创建表a，表a包含int型的id列、可变长度型的name【长度20个字符】
create table a
(
    id   INT,
    NAME VARCHAR(20)
);

首先，你必须有一个数据表，注意数据表的引擎，在构建表时使用MyISAM引擎，MyISAM插入比InnoDB快得多，因为InnoDB的事务支持要好得多，并且在大多数情况下是default使用InnoDB，因此您可以在插入数据后将其修改为InnoDB。

jar包准备

引入依赖

  <dependencies>
    <!--数据库连接依赖引入-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.49</version>
    </dependency>
 
    <!--单元测试依赖引入-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

方式一：普通插入

    /**
     * 方式一
     * 普通批量插入，直接将插入语句执行多次即可
     */
    @Test
    public void bulkSubmissionTest1() {
        long start = System.currentTimeMillis();//开始计时【单位：毫秒】
        Connection conn = jdbcUtils.getConnection();//获取数据库连接
        String sql = "insert into a(id, name) VALUES (?,null)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);//填充sql语句种得占位符
                ps.execute();//执行sql语句
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn, ps, null);
        }
        //打印耗时【单位：毫秒】
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }

用时：62分钟多

数据库插入结果(100w)

方式二：使用批处理插入

    /**
     * 方式二
     * 在方式一的基础上使用批处理
     * 使用PreparedStatement ps;的
     *      ps.addBatch();      将sql语句打包到一个容器中
     *      ps.executeBatch();  将容器中的sql语句提交
     *      ps.clearBatch();    清空容器，为下一次打包做准备
     * 这三个方法实现sql语句打包，累计到一定数量一次提交
     */
    @Test
    public void bulkSubmissionTest2() {
        long start = System.currentTimeMillis();
        Connection conn = jdbcUtils.getConnection();//获取数据库连接
        String sql = "insert into a(id, name) VALUES (?,null)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();//将sql语句打包到一个容器中
                if (i % 500 == 0) {
                    ps.executeBatch();//将容器中的sql语句提交
                    ps.clearBatch();//清空容器，为下一次打包做准备
                }
            }
            //为防止有sql语句漏提交【如i结束时%500！=0的情况】，需再次提交sql语句
            ps.executeBatch();//将容器中的sql语句提交
            ps.clearBatch();//清空容器
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn, ps, null);
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }

用时 61分钟

方案一、方案二批处理并没有达到理想效果，使用方案三

方式三：通过连接配置url设置【&rewriteBatchedStatements=true】

（设置重写批处理语句）

方式三核心语法：【底层就是使用sql自带的语法，（一般插入value建议用于多个行数据的插入，values建议用于单行数据插入，这样效率可以；

来自大佬的测试：sql中value与values的区别）】

insert into a(id, NAME)
VALUE (1, '张三'),
       (2, '李四'),
       (3, '王二'),
       (4, '刘备'),
       (5, '曹操'),
       (6,'张飞');

url=jdbc:mysql://localhost:3306/test01?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true

    /**
     * 方式三
     * 在方式二的基础上允许重写批量提交语句，获取连接的url需加上
     * 【&rewriteBatchedStatements=true】(重写批处理语句=是)
     */
    @Test
    public void bulkSubmissionTest3() {
        long start = System.currentTimeMillis();
        Connection conn = jdbcUtils.getConnection();//获取数据库连接
        String sql = "insert into a(id, name) VALUES (?,null)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn, ps, null);
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }

用时 10s

到此批处理语句才正是生效

注意：

数据库连接的url设置了【&rewriteBatchedStatements=true】时，java代码种的sql语句不能有分号【;】号，否则批处理语句打包就会出现错误，导致后面的sql语句提交出现【BatchUpdateException】异常

方式四：通过数据库连接取消自动提交，手动提交数据

    /**
     * 方式四
     * 在方式三的基础上，取消自动提交sql语句，当sql语句都提交了才手动提交sql语句
     * 需将Connection conn;连接的【conn.setAutoCommit(false)】(设置自动提交=否)
     */
    @Test
    public void bulkSubmissionTest4() {
        long start = System.currentTimeMillis();
        Connection conn = jdbcUtils.getConnection();//获取数据库连接
        String sql = "insert into a(id, name) VALUES (?,null)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn, ps, null);
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }

用时：【4秒左右】

注意：

适当增加mysql的max_allowed_packet参数值允许系统在客户端到服务器端传递大数据时分配更多扩展内存以进行处理。
修改mysql配置文件：

[mysqld]
net_buffer_length=512k
max_allowed_packet=500M

（修改方法）

查看设置
show variables like '%max_allowed_packet%'
修改大小为1G (修改后重新连接数据库，才会显示修改后的结果)
set global max_allowed_packet = 1024*1024*1024

更改引擎

-- 更改引擎的语句
ALTER TABLE 表名 ENGINE=MyISAM;

-- 更改引擎的语句
ALTER TABLE 表明 ENGINE=InnoDB;

总结：

1.注意：这四种方式是层层递进，不可跳过
2.使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】，以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常，
3.其他的就正常使用PreparedStatement ps;的以下三个方法即可

ps.addBatch(); 将sql语句打包到一个容器中
ps.executeBatch(); 将容器中的sql语句提交
ps.clearBatch(); 清空容器，为下一次打包做准备

4.批量插入不要经常玩【插入的数据占用内存多】

转载自 https://blog.csdn.net/C3245073527/article/details/122071045