转载自:
http://www.5180it.com/f/article/76937.html
http://www.513class.com/f/article/76937.html
mapper接口如下:
int updateGoodsStockNum(@Param("stockDTOS") List<GoodsStockDTO> stockDTOS);
在xml配置如下:
<update id="updateGoodsStockNum">
<foreach collection="stockDTOS" item="goodsItem">
update tb_newbee_mall_goods_info set stock_num = stock_num-#{goodsItem.goodsCount}
where goods_id = #{goodsItem.goodsId} and stock_num>=#{goodsItem.goodsCount} and goods_sell_status = 0;
</foreach>
</update>
在执行一条记录没报错,只有在多条情况下就会如下错误
2022-06-15 10:10:44.215 DEBUG 5756 -- [nio-5180-exec-8] o.s.jdbc.support.SQLErrorCodesFactory : Caching SQL error codes for DataSource [com.king.framework.datasource.DynamicDataSource@4749770c]: database product name is 'MySQL'
2022-06-15 10:10:44.215 DEBUG 5756 -- [nio-5180-exec-8] s.j.s.SQLErrorCodeSQLExceptionTranslator : Translating SQLException with SQL state '42000', error code '1064', message [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update bg_goods set stock_num = stock_num-2
where goods_id = 10003 a' at line 4] for task [
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update bg_goods set stock_num = stock_num-2
where goods_id = 10003 a' at line 4
### The error may exist in file [H:\workspace\java\king5180it\target\classes\mybatis\product\GoodsMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update bg_goods set stock_num = stock_num-? where goods_id = ? and stock_num>=? and goods_sell_status = 0; update bg_goods set stock_num = stock_num-? where goods_id = ? and stock_num>=? and goods_sell_status = 0;
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update bg_goods set stock_num = stock_num-2
where goods_id = 10003 a' at line 4
起初以为是sql拼写问题,但多次检查确定是没问题,最后找到解决方法
数据连接需要加一个允许批量执行的配置
allowMultiQueries=true
最后记录下来,供大家学习分享。