内容简介:聊聊jdbc的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)
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 聊聊flink DataStream的join操作
- 聊聊flink DataStream的split操作
- 聊聊flink DataStream的iterate操作
- 聊聊flink Table的where及filter操作
- 聊聊从逻辑门到操作系统的计算机
- 聊聊动态规划(2) -- 特征
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
ANSI Common Lisp
Paul Graham / Prentice Hall / 1995-11-12 / USD 116.40
For use as a core text supplement in any course covering common LISP such as Artificial Intelligence or Concepts of Programming Languages. Teaching students new and more powerful ways of thinking abo......一起来看看 《ANSI Common Lisp》 这本书的介绍吧!