MySQL批量更新操作

最新推荐文章于 2024-05-30 11:38:10 发布

烟火缠过客

最新推荐文章于 2024-05-30 11:38:10 发布

阅读量421

点赞数

分类专栏： MySQL 文章标签： java

本文链接：https://blog.csdn.net/LuckFairyLuckBaby/article/details/100665776

版权

MySQL 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

 public void reviseOrderAmount(NamedParameterJdbcTemplate namedJdbcTemplate, ReportTradingKu tradingKu) {
        //查询语句
        String selectSql = "select t1.merchant_bn,t1.order_id, t1.total_amount,t1.final_amount,t1.cost_item,t1.source_name from sdb_b2c_orders as t1 where  t1.total_amount != t1.cost_item and t1.total_amount = '0.000'";
        //查询数据
        List<ReviseOrderAmount> list = namedJdbcTemplate.query(selectSql, new BeanPropertyRowMapper<>(ReviseOrderAmount.class));
        Connection connection = null;
        PreparedStatement pstm = null;
        try {
            StringBuffer sbf = getJdbcUrl(tradingKu);
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(sbf.toString(), tradingKu.getUserName(), tradingKu.getUserPassword());
            connection.setAutoCommit(false);
            String updateSql = "update sdb_b2c_orders set total_amount = ?,final_amount = ? where order_id = ?";
            pstm = connection.prepareStatement(updateSql);
            if(list != null && list.size() >0){
                for (int i = 0; i < list.size(); i++) {
                    ReviseOrderAmount reviseOrderAmount = list.get(i);
                    pstm.setString(1,reviseOrderAmount.getCost_item());
                    pstm.setString(2,reviseOrderAmount.getCost_item());
                    pstm.setString(3,reviseOrderAmount.getOrder_id());
                    //添加批量处理sql
                    pstm.addBatch();
                    //每200条执行一次，避免内存不够
                    if(i > 0 && i % 200 == 0){
                        pstm.executeBatch();
                    }
                }
                //最后执行剩余不足200条的
                pstm.executeBatch();
                connection.commit();
                logger.info("------------更新【"+tradingKu.getMerKuName()+"】总计【"+list.size()+"】条数据------------");
            }else{
                logger.info("无数据可操作");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeConnect(connection);
            closePstm(pstm);
        }