com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

MyBatis使用过程中实现的语法异常

分页：limit start,limit

异常内容

---------------------------------------------
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''0','20'' at line 9
### The error may involve getP2pTdProjectAdvanceRepaymentList-Inline
### The error occurred while setting parameters
### SQL: select      projectId,deadline,deadline_unit     from p2p_td_project_advance_repayment     where 1 = 1            and repayment_date = ?                  limit ?,?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''0','20'' at line 9
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''0','20'' at line 9

原因分析

• 分页查询接口，mapper.xml 中该接口以 java.util.Map 作为入参；在 Service 层进行接口调用时，limit start,limit 中起始量与偏移量，都按照字符串传值
• #{} 按照字符串解析，带有引号，导致不符合语法规则

解决方案

• Service 中调用时将传入的参数的类型改为 Integer
• Mapper.xml 中 limit 后用 ${} 而不使用 #{}

MyBatis 中 #{}与${} 的区别

---

in 操作

异常内容

select * from t_table where id in () 

原因分析

• Mapper.xml 中没有对 foreach 的集合做相应的判空处理，认为此处传入的数据不会为空（本地测试时当然不会为空），生产环境没有数据，导致语法异常

解决方案

• Mapper.xml 中对应接口加入 <if test="list != null"></if> 的判断；而如果可以确认此集合为空时不查询此接口，则在 Service 层中调用时就添加判断是否调用此 Mapper 接口