Talk about jdbc’s batch operations

  jdbc

Order

This article mainly studies the use of jdbc’s batch and jpa’s batch settings.

batch

Statement’s batch operation can perform insert or update operations in batches to improve operation performance, especially when large amounts of data are inserted or updated.

Use way

    @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);
        }
    }

The main thing is to call the addBatch method after each operation parameter is set, and then call pstmt.executeBatch () after all operations are completed
A disadvantage of this method is that the amount of data is large and it is easy to consume memory, so it is recommended to process it in batches.

@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);
        }
    }

Batch settings for jpa

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

Set batch by setting spring.jpa.properties.hibernate.jdbc.batch _ size

Case test

    @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());
    }

Test Results of Adjusting batch_size Parameter

     没有设置批量
     * 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%

Summary

Jdbc’s batch parameter is very useful for adding/updating large amounts of data and can improve the efficiency of batch operations.

doc