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

╰半橙微兮° 2024-03-30 08:39 267阅读 0赞

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

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

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

问题描述

编辑了多层查询语句，在navicat运行正常，但测试环境报错
在这里插入图片描述

  1. org.springframework.jdbc.BadSqlGrammarException: 
  2. ### 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
  3. ### The error may exist in file [D:\hfyc_project\BladeX\blade-service\blade-system\target\classes\org\springblade\system\mapper\IntegralMapper.xml]
  4. ### The error may involve defaultParameterMap
  5. ### The error occurred while setting parameters
  6. ### 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 (?))
  7. ### 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
  8. ; 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
  9.     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
  10.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
  11.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
  12.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
  13.     at com.sun.proxy.$Proxy161.selectList(Unknown Source)
  14.     at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
  15.     at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
  16.     at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
  17.     at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
  18.     at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
  19.     at com.sun.proxy.$Proxy198.exportUserIntegral(Unknown Source)
  20.     at org.springblade.system.service.impl.IntegralServiceImpl.exportUser(IntegralServiceImpl.java:118)
  21.     at org.springblade.system.service.impl.IntegralServiceImpl$$FastClassBySpringCGLIB$$7c056666.invoke(<generated>)
  22.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  23.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
  24.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  25.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  26.     at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
  27.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  28.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  29.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
  30.     at org.springblade.system.service.impl.IntegralServiceImpl$$EnhancerBySpringCGLIB$$7bb6aa7a.exportUser(<generated>)
  31.     at org.springblade.system.controller.IntegralController.exportUser(IntegralController.java:114)
  32.     at org.springblade.system.controller.IntegralController$$FastClassBySpringCGLIB$$13121a48.invoke(<generated>)
  33.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  34.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
  35.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  36.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  37.     at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
  38.     at org.springblade.core.log.aspect.ApiLogAspect.around(ApiLogAspect.java:45)
  39.     at sun.reflect.GeneratedMethodAccessor383.invoke(Unknown Source)
  40.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  41.     at java.lang.reflect.Method.invoke(Method.java:498)
  42.     at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
  43.     at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
  44.     at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
  45.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
  46.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  47.     at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
  48.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  49.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
  50.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
  51.     at org.springblade.system.controller.IntegralController$$EnhancerBySpringCGLIB$$603dc25e.exportUser(<generated>)
  52.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  53.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  54.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  55.     at java.lang.reflect.Method.invoke(Method.java:498)
  56.     at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
  57.     at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
  58.     at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
  59.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
  60.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
  61.     at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
  62.     at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
  63.     at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
  64.     at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
  65.     at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
  66.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:497)
  67.     at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
  68.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:584)
  69.     at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
  70.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
  71.     at org.springblade.core.log.filter.LogTraceFilter.doFilter(LogTraceFilter.java:39)
  72.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  73.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  74.     at org.springblade.core.boot.request.BladeRequestFilter.doFilter(BladeRequestFilter.java:58)
  75.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  76.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  77.     at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
  78.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
  79.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  80.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  81.     at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
  82.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
  83.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  84.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  85.     at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
  86.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
  87.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  88.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  89.     at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
  90.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
  91.     at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
  92.     at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
  93.     at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
  94.     at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
  95.     at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
  96.     at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
  97.     at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
  98.     at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)
  99.     at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
  100.     at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  101.     at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
  102.     at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
  103.     at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
  104.     at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
  105.     at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
  106.     at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  107.     at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)
  108.     at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
  109.     at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:275)
  110.     at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:79)
  111.     at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:134)
  112.     at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:131)
  113.     at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
  114.     at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
  115.     at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:255)
  116.     at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:79)
  117.     at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:100)
  118.     at io.undertow.server.Connectors.executeRootHandler(Connectors.java:387)
  119.     at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:852)
  120.     at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
  121.     at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)
  122.     at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)
  123.     at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449)
  124.     at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
  125.     at java.lang.Thread.run(Thread.java:748)
  126. 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
  127.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
  128.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  129.     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  130.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  131.     at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  132.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
  133.     at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
  134.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
  135.     at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
  136.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
  137.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
  138.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
  139.     at sun.reflect.GeneratedMethodAccessor325.invoke(Unknown Source)
  140.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  141.     at java.lang.reflect.Method.invoke(Method.java:498)
  142.     at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
  143.     at com.sun.proxy.$Proxy292.query(Unknown Source)
  144.     at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
  145.     at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
  146.     at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
  147.     at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
  148.     at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
  149.     at com.sun.proxy.$Proxy289.query(Unknown Source)
  150.     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
  151.     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
  152.     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
  153.     at sun.reflect.GeneratedMethodAccessor342.invoke(Unknown Source)
  154.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  155.     at java.lang.reflect.Method.invoke(Method.java:498)
  156.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
  157.     ... 113 common frames omitted

解决思路1

怀疑是语句问题，更改了查询语句，还是不行

  1. SELECT bu.id, bu.code, bu.account, bu.real_name, bu.birthday, bu.sex, bu.role_id, bu.dept_id, bu.post_id
  2.         , 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`
  3.         FROM user bu, post bp, dict bd
  4.                 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. 1.检查sql语句，语法是否正确，最好的检查方法就是将sql语句复制到查询器中执行一遍。 
  3. 2.检查字段是否出现sql关键字！（比如delete，call），这个很重要，因为call在java中并不是关键字，但是在sql中是关键字！ 
  5. 3.检查Mapper接口，参数名一定要对上！！！ 否则运行时照样报错。
  7. 4.检查Mapper接口代码是否符合规范，mybaits动态注入参数使用“#”，而不是“$”符号。

**
这里原来rank是关键字，要加` 才行**
部分关键字
在这里插入图片描述

发表评论

表情：
评论列表 （有 0 条评论，267人围观）

还没有评论，来说两句吧...

相关阅读