原创博文,欢迎转载,转载时请务必附上博文链接,感谢您的尊重
报错情况
intell IDEA调试台报错
Caused by: 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 'describe,state FROM sys_role
WHERE (id_role IN (select id_role from sys_use' at line 1
POSTMAN 请求测试
Body.pretty
{
"msg": "\r\n### Error querying 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 'describe,state FROM sys_role \n \n WHERE (id_role IN (select id_role from sys_use' at line 1\r\n### The error may exist in com/vuespringboot/mapper/RoleMapper.java (best guess)\r\n### The error may involve defaultParameterMap\r\n### The error occurred while setting parameters\r\n### SQL: SELECT id_role,roleName,roleCode,describe,state FROM sys_role WHERE (id_role IN (select id_role from sys_user_role where id_user = 1))\r\n### 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 'describe,state FROM sys_role \n \n WHERE (id_role IN (select id_role from sys_use' at line 1\n; 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 'describe,state FROM sys_role \n \n WHERE (id_role IN (select id_role from sys_use' at line 1",
"code": 400
}
错误分析
报错信息提取
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
'describe,
state
FROM
sys_role
WHERE
(id_role IN
(select
id_role
from
sys_use' at line 1
分析报错
从报错上来看,是SQL语言写错了,但是我在项目中找了很久,关于报错中提出的sql代码,我好像自己并没有写过啊,这让我感到很奇怪,是不是系统拼接sql语句的时候出问题了。
但是通过逐语句调试,我发现我出现错误的语句是这样一句话:
final List<Role> roles = this.roleService.list(new QueryWrapper<Role>()
.inSql("id_role", "select id_role from sys_user_role where id_user = " + userId));
而且当我测试this.roleService.list()语句时,依然出现报错,但是这句语句按道理不应该有错误呀,我也没有覆写过list()方法,于是我突然意识到,可能是自己在UserMapper.xml中写的sql语句出现了问题。
但是经过反复测试,确实没有在UserMapper中有错误的sql语句,最终,我还是回归到RoleService本身,再次观察报错信息:describe, state FROM ……
我突然意识到了说明,describe和state是我在表中列的列名,会不会是列名个mybitas冲突了呢?state在各个表中都有使用,我尝试了其它几个表,没有出现任何问题,于是我把目光锁定到了describe上。
解决
我进入sql表,讲describe列名改为了roleDescribe,果不其然,之后程序就恢复了正常,list()方法也可以正常使用了。