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

mysql数据库准备

 

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

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

  
    
    
      mysql
      mysql-connector-java
      5.1.49
    

    
    
      junit
      junit
      4.11
      test
    
    
  

方式一：普通插入

    /**
     * 方式一
     * 普通批量插入，直接将插入语句执行多次即可
     */
    @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多内存