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 (?))
    ### 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
    ; 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 'rank FROM blade_user bu WHERE is_deleted = 0 AND org = 1 AND id IN (SELECT user_' at line 1
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
    	at com.sun.proxy.$Proxy161.selectList(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    	at com.sun.proxy.$Proxy198.exportUserIntegral(Unknown Source)
    	at org.springblade.system.service.impl.IntegralServiceImpl.exportUser(IntegralServiceImpl.java:118)
    	at org.springblade.system.service.impl.IntegralServiceImpl$$FastClassBySpringCGLIB$$7c056666.invoke()
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
    	at org.springblade.system.service.impl.IntegralServiceImpl$$EnhancerBySpringCGLIB$$7bb6aa7a.exportUser()
    	at org.springblade.system.controller.IntegralController.exportUser(IntegralController.java:114)
    	at org.springblade.system.controller.IntegralController$$FastClassBySpringCGLIB$$13121a48.invoke()
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
    	at org.springblade.core.log.aspect.ApiLogAspect.around(ApiLogAspect.java:45)
    	at sun.reflect.GeneratedMethodAccessor383.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
    	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
    	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
    	at org.springblade.system.controller.IntegralController$$EnhancerBySpringCGLIB$$603dc25e.exportUser()
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:497)
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:584)
    	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
    	at org.springblade.core.log.filter.LogTraceFilter.doFilter(LogTraceFilter.java:39)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at org.springblade.core.boot.request.BladeRequestFilter.doFilter(BladeRequestFilter.java:58)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
    	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
    	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
    	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
    	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    	at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
    	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    	at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
    	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)
    	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
    	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
    	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
    	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
    	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
    	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
    	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    	at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)
    	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:275)
    	at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:79)
    	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:134)
    	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:131)
    	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
    	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
    	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:255)
    	at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:79)
    	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:100)
    	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:387)
    	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:852)
    	at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    	at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)
    	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)
    	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449)
    	at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
    	at java.lang.Thread.run(Thread.java:748)
    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 'rank FROM blade_user bu WHERE is_deleted = 0 AND org = 1 AND id IN (SELECT user_' at line 1
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    	at sun.reflect.GeneratedMethodAccessor325.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
    	at com.sun.proxy.$Proxy292.query(Unknown Source)
    	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    	at com.sun.proxy.$Proxy289.query(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    	at sun.reflect.GeneratedMethodAccessor342.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    	... 113 common frames omitted
    

    解决思路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 bd
    				WHERE 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是关键字，要加` 才行**

    部分关键字

    

     
     怎么自己制作一个网页  申请个人网页  深圳网络营销公司  咸宁网站建设设计  西安专业建站  网站建设开什么发票