The error occurred while setting parameters，MyBatis使用报错原因及解决办法

  • 问题描述
    • 解决思路1
    • 解决思路2

The error occurred while setting parameters， MyBatis框架使用报错原因及解决办法

问题描述

编辑了多层查询语句，在navicat运行正常，但测试环境报错

org.springframework.jdbc.BadSqlGrammarException: ### 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 'rank FROM blade_user bu WHERE is_deleted = 0 AND org = 1 AND id IN (SELECT user_' at line 1### The error may exist in file [D:\hfyc_project\BladeX\blade-service\blade-system\target\classes\org\springblade\system\mapper\IntegralMapper.xml]### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: SELECT bu.id, bu.code, bu.account, bu.real_name, bu.birthday, bu.sex, bu.role_id, bu.dept_id, bu.post_id, bu.job_date, bu.remark, bu.start_date, bu.class1, bu.class2, bu.other_job_year, bu.other_rank_year, bu.clean_date, (SELECT bd.dict_value FROM blade_dict bd WHERE bd.code = 'post_category' AND (bd.dict_key = (SELECT bp.category FROM blade_post bp WHERE bp.id = bu.post_id AND bp.is_deleted = 0 AND bp.tenant_id = '000000'))) AS rank FROM blade_user bu WHERE is_deleted = 0 AND org = 1 AND id IN (SELECT user_id FROM blade_user_dept WHERE dept_id IN (" />解决思路1 
怀疑是语句问题，更改了查询语句，还是不行
 
SELECT bu.id, bu.code, bu.account, bu.real_name, bu.birthday, bu.sex, bu.role_id, bu.dept_id, bu.post_id, bu.job_date, bu.remark, bu.start_date, bu.class1, bu.class2, bu.other_job_year, bu.other_rank_year, bu.clean_date, bd.dict_value as `rank`FROM user bu, post bp, dict bdWHERE bp.id = bu.post_id and bd.dict_key = bp.category and bd.code = 'post_category' and bu.is_deleted = 0 and bu.org = 1 AND bp.tenant_id = '000000'
解决思路2
1.检查sql语句，语法是否正确，最好的检查方法就是将sql语句复制到查询器中执行一遍。 2.检查字段是否出现sql关键字！（比如delete，call），这个很重要，因为call在java中并不是关键字，但是在sql中是关键字！ 3.检查Mapper接口，参数名一定要对上！！！ 否则运行时照样报错。4.检查Mapper接口代码是否符合规范，mybaits动态注入参数使用“#”，而不是“$”符号。
**
 这里原来rank是关键字，要加` 才行**
 部分关键字