接手项目中,有部分表字段包含了MySQL的关键字,譬如:condition…,操作时制造了一些小麻烦,记录一下。
问题
目标表中包含数据库关键字 CONDITION
(切记切记切记,设计表字段时一定要避开关键字。),导致新增报错:
### 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 'condition, `execute_condition`, `next_condition`, `is_del`, `creator_id`, `labor' 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 'condition, `execute_condition`, `next_condition`, `is_del`, `creator_id`, `labor' at line 1] with root 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 'condition, `execute_condition`, `next_condition`, `is_del`, `creator_id`, `labor' at line 1
解决办法
其实也好解决,将涉及到的报错字段加上`` 符号即可;
比如:
INSERT INTO TEST(`CONDITION`) values(#{CONDITION})
对于使用 Mybatis-plus 的朋友,可以在实体用使用 @TableField
注解来进行标注;
比如:
TestEntity.class
public class TestEntity{
...
@TableField("`condition`")
private String condition;
...
}
TestMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xx.xx.xx.dao.TestMapper">
<resultMap type="xx.xx.xx.entity.TestEntity" id="TestEntityMap">
<result column="`condition`" property="condition" />
</resultMap>
</mapper>