问题之PreparedStatement executeBatch() 更新仅部分有效
问题背景
某业务场景下,需要批量对订单信息进行更新,分成功和失败,更新不同的字段。涉及两张表,订单表和订单扩展表,一共四条不同的SQL。 一开始想的是,PreparedStatement也支持不同SQL的批量处理,直接遍历orderList集合,将所有要更新的sql都通过addbatch()添加进行,然后每100条执行一次。
注:两个表是分库分表的【shardingJDBC】
问题
代码成功运行,没报错,也打印出了执行SQL,数量和数据都对上了。但是查看数据库,真正更新了的,只有最后2个SQL。
测试中,集合有10个order,全部走【OrderConstant.OrderStatus.FAIL != orderPO.getOrderStatus()】分支。百度也没有找到原因,感觉应该是可以成功的才对。
解决办法:
分拆SQL,遍历4次list,分成4个SQL批次执行
代码如下:
private void batchUpdateOrder(List<OrderPO> orderList){
log.info("批量更新订单信息--->准备批量更新订单信息");
Connection connection = null;
PreparedStatement psOrder = null;
try {
SqlSessionFactory sqlSessionFactory = ApplicationContextUtil.getBean(SqlSessionFactory.class);
connection = sqlSessionFactory.openSession().getConnection();
connection.setAutoCommit(false);
// 根据状态来判断校验失败还是成功,进而选择SQL进行执行
int sqlNum = 0;
for (OrderPO orderPO : orderList){
if (OrderConstant.OrderStatus.FAIL != orderPO.getOrderStatus()) {
psOrder = connection.prepareStatement(SQL_SUCCESS_ORDER_EXT);
psOrder.setString(1, orderPO.getExt3());
/*...*/
psOrder.addBatch();
psOrder = connection.prepareStatement(SQL_SUCCESS_ORDER);
psOrder.setBigDecimal(1, orderPO.getSubBatchNo());
/*...*/
psOrder.addBatch();
} else {
psOrder = connection.prepareStatement(SQL_FAIL_ORDER_EXT);
psOrder.setString(1, orderPO.getChannelErrorCode());
/*...*/
psOrder.addBatch();
psOrder = connection.prepareStatement(SQL_FAIL_ORDER);
psOrder.setInt(1, orderPO.getOrderStatus());
/*...*/
psOrder.addBatch();
}
sqlNum = sqlNum + 2;
if(sqlNum % 100 == 0){
psOrder.executeBatch();
log.info("批量更新订单信息--->已更新 {} 笔订单数据", sqlNum/2);
}
}
psOrder.executeBatch();
log.info("批量更新订单信息--->批量更新订单信息全部更新完毕,共更新 {} 笔", sqlNum/2);
connection.commit();
connection.setAutoCommit(true);
} catch (Exception ex) {
log.error("批量更新订单信息--->,批量更新订单信息数据出现异常", ex);
throw new BizException(ErrorCodeEnum.BATCH_UPDATE_ORDER_EXT_FAIL);
} finally {
if (psOrder != null) {
try {
psOrder.close();
} catch (Exception ex) {
log.error("psOrder close error.", ex);
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception ex) {
log.error("connection close error.", ex);
}
}
}
}