记：mybatis <foreach> 语法错误

 关注

发布于: 2020 年 05 月 26 日

问题背景：本猿通过foreach标签实现批量where in更新订单状态，由于SQL过于简单和自信，没使用单元测试断言进行代码测试，所以才碰到此坑。哈哈。。
﻿
一、代码OrderMapper.java
void updateOrderStatus(@Param("orders") List<Order> orders);
OrderMapper.xml
<update id="updateOrderStatus">
	update tz_order set `status`=6, cancel_time=NOW(), update_time=NOW(), initiative_status=3 where order_id in
	<foreach collection="orders" item="order" open="(" separator="," close=")">
		#{order.orderId}
	</foreach>
</update>
﻿
二、异常信息
### 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 '' at line 1
### The error may exist in file [E:\IdeaProjects\xxx\xxx\xxx-service\target\classes\mapper\OrderMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update tz_order set `status`=6, cancel_time=NOW(), update_time=NOW(), initiative_status=3 where order_id in
### 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 '' at line 1
; bad SQL grammar []; nested exception is 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 '' at line 1
﻿
﻿
提示SQL语法错误，最终问题定位到<foreach>.....</foreach> 
﻿
三、问题原因由于接口参数类型为List，而在mybatis中foreach遍历order对象为null，故此在order.orderId取值，得到对象参数无值，jdbc抛出SQLSyntaxErrorException。结论：mybatis不接受对象为null取值。
﻿

发布于: 2020 年 05 月 26 日阅读数: 41

原文链接:【http://xie.infoq.cn/article/2c2791551468e78462b714ae0】。

Kevin Liao

 关注

大家好，我是Kevin 2019.02.15 加入

Java程序员，分别服务行业：电信、跨境电商、政企.... 爱好：看书、撸代码、打个篮球、旅个游。技术特长：Java后端、Spring全家桶、微服务、网络、服务器等等啥之类。

发布

暂无评论