聊聊jdbc的batch操作

本文主要研究一下jdbc的batch的使用以及jpa的batch设置

batch

statement的batch操作,可以批量进行insert或update操作,提升操作性能,特别是在大数据量的insert或update的时候。

使用方式

    @Test
    public void testSqlInjectSafeBatch(){
        String sql = "insert into employee (name, city, phone) values (?, ?, ?)";

        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);

            for (int i=0;i<3;i++) {
                pstmt.setString(1,"name"+i);
                pstmt.setString(2,"city"+i);
                pstmt.setString(3,"iphone"+i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

            conn.commit();

        }catch (SQLException e){
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DbUtils.closeQuietly(pstmt);
            DbUtils.closeQuietly(conn);
        }
    }

主要就是每条操作参数设置完之后,调用addBatch方法,然后再所有操作都pstmt.addBatch()完之后,调用pstmt.executeBatch() 这种方式有个缺陷就是数据量大容易消耗内存,因此建议再分批次处理

@Test
    public void testSqlInjectSafeAndOOMSafeBatch(){
        String sql = "insert into employee (name, city, phone) values (?, ?, ?)";

        Connection conn = null;
        PreparedStatement pstmt = null;

        final int batchSize = 1000;
        int count = 0;

        try{
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);

            for (int i=0;i<10000;i++) {
                pstmt.setString(1,"name"+i);
                pstmt.setString(2,"city"+i);
                pstmt.setString(3,"iphone"+i);
                pstmt.addBatch();

                //小批量提交,避免OOM
                if(++count % batchSize == 0) {
                    pstmt.executeBatch();
                }
            }

            pstmt.executeBatch(); //提交剩余的数据

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DbUtils.closeQuietly(pstmt);
            DbUtils.closeQuietly(conn);
        }
    }

jpa的batch设置

spring:
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: update
      naming:
        implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        jdbc:
          batch_size: 5000
          batch_versioned_data: true
        order_inserts: true
        order_updates: true

通过设置spring.jpa.properties.hibernate.jdbc.batch_size来设置批量

实例测试

    @Test
    public void testJpaBatch() {
        List<DemoUser> demoUsers = new ArrayList<>();
        for(int i=0;i<10;i++){
            DemoUser demoUser = new DemoUser();
            demoUser.setPrincipal("demo");
            demoUser.setAccessToken(UUID.randomUUID().toString());
            demoUser.setAuthType(UUID.randomUUID().toString());
            demoUser.setDeptName(UUID.randomUUID().toString());
            demoUser.setOrgName(UUID.randomUUID().toString());
            demoUsers.add(demoUser);
        }
        StopWatch stopWatch = new StopWatch("jpa batch");
        stopWatch.start();
        demoUserDao.save(demoUsers);
        stopWatch.stop();
        System.out.println(stopWatch.prettyPrint());
    }

调整batch_size参数的测试结果

     没有设置批量
     * StopWatch 'jpa batch': running time (millis) = 21383
     -----------------------------------------
     ms     %     Task name
     -----------------------------------------
     21383  100%

     设置批量500
     StopWatch 'jpa batch': running time (millis) = 16790
     -----------------------------------------
     ms     %     Task name
     -----------------------------------------
     16790  100%

     批量1000
     StopWatch 'jpa batch': running time (millis) = 12317
     -----------------------------------------
     ms     %     Task name
     -----------------------------------------
     12317  100%

     批量5000
     StopWatch 'jpa batch': running time (millis) = 13190
     -----------------------------------------
     ms     %     Task name
     -----------------------------------------
     13190  100%

小结

jdbc的batch参数对于大数据量的新增/更新操作来说,非常有用,可以提升批量操作的效率。

doc

  • Batch Processing in JDBC
  • JDBC: Batch Updates
  • 三种JDBC批量插入编程方法的比较
  • Batch Insert In Java – JDBC
  • JDBC PreparedStatement example – Batch Update
  • JDBC - Batch Processing
  • Java Jdbc减少交互提升批量处理性能,到底该如何优化才好?
  • Spring Data JPA: Batch insert for nested entities
  • Spring JPA Hibernate - JpaRepository Insert (Batch)

原文发布于微信公众号 - 码匠的流水账(geek_luandun)

原文发表时间:2018-01-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据和云计算技术

SparkStreaming入门

黄文辉同学第二篇,请大家支持! 1.SparkStreaming简介 Spark Streaming属于核心Spark API的扩展,支持实时数据流的可扩展、高...

3564
来自专栏Albert陈凯

Spark详解06容错机制Cache 和 Checkpoint Cache 和 Checkpoint

Cache 和 Checkpoint 作为区别于 Hadoop 的一个重要 feature,cache 机制保证了需要访问重复数据的应用(如迭代型算法和交互式应...

47812
来自专栏大数据-Hadoop、Spark

Spark Streaming + Kafka整合

2605
来自专栏Hadoop实操

SparkStreaming读Kafka数据写Kudu

1.8K2
来自专栏祝威廉

Spark Streaming 误用.transform(func)函数导致的问题解析

特定情况你会发现UI 的Storage标签上有很多新的Cache RDD,然后你以为是Cache RDD 不被释放,但是通过Spark Streaming 数据...

903
来自专栏芋道源码1024

分布式作业系统 Elastic-Job-Lite 源码分析 —— 作业执行

Lite调度作业( LiteJob ),作业被调度后,调用 #execute() 执行作业。

6022
来自专栏Albert陈凯

Spark系列课程-00xxSpark RDD持久化

我们这节课讲一下RDD的持久化 ? RDD的持久化 这段代码我们上午已经看过了,有瑕疵大家看出来了吗? 有什么瑕疵啊? 大家是否还记得我在第二节课的时候跟大...

4088
来自专栏木东居士的专栏

用MPI进行分布式内存编程(入门篇)

4283
来自专栏Hadoop实操

Spark2Streaming读Kerberos环境的Kafka并写数据到HDFS

在前面的文章Fayson介绍了一些关于Spark2Streaming的示例如《Spark2Streaming读Kerberos环境的Kafka并写数据到HBas...

2601
来自专栏JavaEdge

史上最快! 10小时大数据入门实战(五)-分布式计算框架MapReduce1 MapReduce概述2 MapReduce编程模型之通过wordcount词频统计分析案例入门MapReduce执行流程

1843

扫码关注云+社区

领取腾讯云代金券