MySQL 报错：Translating SQLException with SQL state '42000', error code '1064', message
MySQL报错详细日志
1 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator:399] - Translating SQLException with SQL state '42000', error code '1064', message [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 ''10'' at line 9]; SQL was [] for task [ 2 ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''10'' at line 9 3 ### The error may exist in com/xxx/insurance/dao/mapper/insurUser.xml 4 ### The error may involve insurUser.getPolicyList-Inline 5 ### The error occurred while setting parameters 6 ### SQL: SELECT pii.SERIAL_NO, pii.POLICY_NUM, pii.INSUR_PRODUCT_NUM, pii.INSUR_AMOUNT, pii.STATUS, pii.TOOTH_POSITION, hui.HOLDER_USER_NAME userName, hui.CREATE_TIME holderTime FROM patient_insurance_info pii LEFT JOIN patient_detail pd ON pii.PID = pd.PID LEFT JOIN holder_user_info hui ON pd.HOLDER_ID = hui.HOLDER_ID WHERE 1=1 AND pd.CLCODE=? limit ?,? 7 ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''10'' at line 9 8 ] 9 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.mybatis.spring.SqlSessionUtils:287] - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a52c61e] 10 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.mybatis.spring.SqlSessionUtils:292] - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a52c61e] 11 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager:843] - Initiating transaction rollback 12 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager:279] - Rolling back JDBC transaction on Connection [jdbc:mysql://127.0.0.1:3306/chubb_2?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true, UserName=root@localhost, MySQL-AB JDBC Driver] 13 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils:222] - Resetting read-only flag of JDBC Connection [jdbc:mysql://127.0.0.1:3306/chubb_2?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true, UserName=root@localhost, MySQL-AB JDBC Driver] 14 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager:322] - Releasing JDBC Connection [jdbc:mysql://127.0.0.1:3306/chubb_2?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction 15 2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils:332] - Returning JDBC Connection to DataSource 16 2019-09-12 16:42:29 [http-nio-80-exec-25] WARN [org.springframework.remoting.support.RemoteInvocationTraceInterceptor:87] - Processing of HessianServiceExporter remote call resulted in fatal exception: com.xxx.insurance.control.InsurFacade.getPolicyList 17 org.springframework.jdbc.BadSqlGrammarException: 18 ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''10'' at line 9 19 ### The error may exist in com/xxx/insurance/dao/mapper/insurUser.xml 20 ### The error may involve insurUser.getPolicyList-Inline 21 ### The error occurred while setting parameters 22 ### SQL: SELECT pii.SERIAL_NO, pii.POLICY_NUM, pii.INSUR_PRODUCT_NUM, pii.INSUR_AMOUNT, pii.STATUS, pii.TOOTH_POSITION, hui.HOLDER_USER_NAME userName, hui.CREATE_TIME holderTime FROM patient_insurance_info pii LEFT JOIN patient_detail pd ON pii.PID = pd.PID LEFT JOIN holder_user_info hui ON pd.HOLDER_ID = hui.HOLDER_ID WHERE 1=1 AND pd.CLCODE=? limit ?,? 23 ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''10'' at line 9 24 ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''10'' at line 9 25 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) 26 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) 27 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) 28 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) 29 at com.sun.proxy.$Proxy63.selectList(Unknown Source) 30 at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198) 31 at com.xxx.insurance.dao.impl.InsurUserDAOImpl.getPolicyList(InsurUserDAOImpl.java:794) 32 at com.xxx.insurance.service.impl.MccInsurServiceImpl.getPolicyList(MccInsurServiceImpl.java:625) 33 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 34 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 35 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 36 at java.lang.reflect.Method.invoke(Method.java:498) 37 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) 38 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) 39 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) 40 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) 41 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 42 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90) 43 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 44 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) 45 at com.sun.proxy.$Proxy67.getPolicyList(Unknown Source) 46 at com.xxx.insurance.control.InsurFacadeImpl.getPolicyList(InsurFacadeImpl.java:1283) 47 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 48 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 49 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 50 at java.lang.reflect.Method.invoke(Method.java:498) 51 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) 52 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) 53 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) 54 at org.springframework.remoting.support.RemoteInvocationTraceInterceptor.invoke(RemoteInvocationTraceInterceptor.java:77) 55 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 56 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) 57 at com.sun.proxy.$Proxy96.getPolicyList(Unknown Source) 58 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 59 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 60 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 61 at java.lang.reflect.Method.invoke(Method.java:498) 62 at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:302) 63 at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:217) 64 at org.springframework.remoting.caucho.HessianExporter.doInvoke(HessianExporter.java:198) 65 at org.springframework.remoting.caucho.HessianExporter.invoke(HessianExporter.java:118) 66 at org.springframework.remoting.caucho.HessianServiceExporter.handleRequest(HessianServiceExporter.java:66) 67 at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:49) 68 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790) 69 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) 70 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:669) 71 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:585) 72 at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) 73 at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) 74 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) 75 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 76 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 77 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) 78 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 79 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) 80 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) 81 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) 82 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) 83 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) 84 at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) 85 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) 86 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521) 87 at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096) 88 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674) 89 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500) 90 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456) 91 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 92 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 93 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 94 at java.lang.Thread.run(Thread.java:745)
执行的 SQL：
1 <select parameterType="map" resultType="java.util.HashMap">
2 SELECT pii.SERIAL_NO, pii.POLICY_NUM, pii.INSUR_PRODUCT_NUM, pii.INSUR_AMOUNT, pii.STATUS, pii.TOOTH_POSITION,
3 hui.HOLDER_USER_NAME userName, hui.CREATE_TIME holderTime
4 FROM patient_insurance_info pii
5 LEFT JOIN patient_detail pd ON pii.PID = pd.PID
6 LEFT JOIN holder_user_info hui ON pd.HOLDER_ID = hui.HOLDER_ID
7 WHERE 1=1
8 AND pd.CLCODE=#{clCode}
9 <if test="currentPageNum!=null">
10 limit #{currentPageNum},#{pageSize}
11 </if>
12 </select>报错原因：
传入到 mybatis 的 参数类型为 Map paramMap ，但是在 Controller 层只是将 currentPageNum 转为了整数类型，没有将 pageSize 转为整数类型，pageSize 默认值为 10，所以才会在上面的报错信息第一行中出现如下信息：
Translating SQLException with SQL state '42000', error code '1064', message [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 ''10'' at line 9]
MySQL 的 limit 后面必须要传两个整数，否者就会报这个错。
解决方案：
将 currentPageNum 和 pageSize 转为整数传入。
PS：还有可能是其他问题导致出现相同 error code '1064' 错误，可以参照上面出现这个错误的原因。