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

     阅读:21

mysql数据库准备

 

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

jar包准备

引入依赖【不知道什么是依赖的可查看Maven的配置与使用

  <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分钟多

数据库插入结果

方式二:使用批处理插入

    /**
     * 方式二
     * 在方式一的基础上使用批处理
     * 使用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)+"【单位:毫秒】");
    }

用时

 数据库插入结果

方式一、二总结:到此可以看出其实其处理程序是没有起作用的,为此我们使用方式三

方式三:通过连接配置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)+"【单位:毫秒】");
    }

用时:【10秒左右】

 数据库插入结果

 

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

注意

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

异常:【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秒左右】

数据库插入结果

 总结:

1.注意:这四种方式是层层递进,不可跳过

2.使用批量提交数据,url一定要设置允许重写批量提交【rewriteBatchedStatements=true】,以及此时的sql语句一定不能有分号,否则有【BatchUpdateException】异常,

3.其他的就正常使用PreparedStatement ps;的以下三个方法即可
     *      ps.addBatch();      将sql语句打包到一个容器中
     *      ps.executeBatch();  将容器中的sql语句提交
     *      ps.clearBatch();    清空容器,为下一次打包做准备

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

        批量插入之前表a只占用1M不到的内存

         批量插入4百万条数据后占用100M多内存