jdbc 大数据批量插入很慢问题解决
程序员文章站
2022-07-12 22:07:28
...
普通的执行过程是:每处理一条数据,就访问一次数据库;
而批处理是:累积到一定数量,再一次性提交到数据库,减少了与数据库的交互次数,所以效率会大大提高
但是使用了 addBatch() -> executeBatch() 还是很慢
解决:
a.需配置 参数 在数据库连接URL后面加上这个参数:
dbUrl "jdbc:mysql://localhost:3306/User?rewriteBatchedStatements=true";
b.conn.setAutoCommit(false); 需要设置 手动提交
完整例子:
1.正常JDBC 版本
String sql = "insert into table *****"; con.setAutoCommit(false); ps = con.prepareStatement(sql); for(int i=1; i<65536; i++){ ps.addBatch(); // 1w条记录插入一次 if (i % 10000 == 0){ ps.executeBatch(); con.commit(); } } // 最后插入不足1w条的数据 ps.executeBatch(); con.commit();
2.Spring boot jdbc版本
@Autowired
private JdbcTemplate jdbcTemplate;
private void batchInsertOrUpdateGoodsTemp(List<GoodsTemp> list){ StringBuilder sql = new StringBuilder("INSERT INTO `gds_goods_temp` (`CAT_CODE`, `BRAND_CODE`, `GOODS_SN`, `GOODS_STATUS`, `GOODS_NAME` ").append( ", `IS_MARKET`, `IS_DELETE`, `GOODS_INTRO`, `GOODS_DETAIL`, `UNIT`, `WEIGHT`, `SPEC`, `BARCODE`, `GOODS_IMG`, `GOODS_THUMB`, `GOODS_GALLERY_IMGS`") .append(", `MARKET_PRICE`, `REMARK`, `ADD_TYPE`, `ADD_MCH_NO`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); KeyHolder key = new GeneratedKeyHolder(); this.jdbcTemplate.update(con -> { con.setAutoCommit(false); PreparedStatement preState =null; try { preState = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS); for(GoodsTemp temp : list){ preState.setString(1, temp.getCatCode()); preState.setString(2, temp.getBrandCode()); preState.setString(3, temp.getGoodsSn()); preState.setString(4, temp.getGoodsStatus()); preState.setString(5, temp.getGoodsName()); preState.setString(6, temp.getIsMarket()); preState.setString(7, temp.getIsDelete()); preState.setString(8, temp.getGoodsIntro()); preState.setString(9, temp.getGoodsDetail()); preState.setString(10, temp.getUnit()); preState.setBigDecimal(11, temp.getWeight()); preState.setString(12, temp.getSpec()); preState.setString(13, temp.getBarcode()); preState.setString(14, temp.getGoodsImg()); preState.setString(15, temp.getGoodsThumb()); preState.setString(16, temp.getGoodsGalleryImgs()); preState.setBigDecimal(17, temp.getMarketPrice()); preState.setString(18, temp.getRemark()); preState.setString(19, temp.getAddType()); preState.setString(20, temp.getAddMchNo()); preState.addBatch(); } preState.executeBatch(); con.commit(); } catch (SQLException e) { log.error("sql语句{},批量执行错误:{}", sql,e.getMessage()); } return preState; }, key); }
上一篇: jdbc 大数据批量插入很慢问题解决