欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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