MySQL报错详细日志
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 [
### 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
### The error may exist in com/xxx/insurance/dao/mapper/insurUser.xml
### The error may involve insurUser.getPolicyList-Inline
### The error occurred while setting parameters
### 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 ?,?
### 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
]
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]
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]
2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager:843] - Initiating transaction rollback
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]
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]
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
2019-09-12 16:42:29 [http-nio-80-exec-25] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils:332] - Returning JDBC Connection to DataSource
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
org.springframework.jdbc.BadSqlGrammarException:
### 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
### The error may exist in com/xxx/insurance/dao/mapper/insurUser.xml
### The error may involve insurUser.getPolicyList-Inline
### The error occurred while setting parameters
### 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 ?,?
### 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
; 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
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy63.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at com.xxx.insurance.dao.impl.InsurUserDAOImpl.getPolicyList(InsurUserDAOImpl.java:794)
at com.xxx.insurance.service.impl.MccInsurServiceImpl.getPolicyList(MccInsurServiceImpl.java:625)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy67.getPolicyList(Unknown Source)
at com.xxx.insurance.control.InsurFacadeImpl.getPolicyList(InsurFacadeImpl.java:1283)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.remoting.support.RemoteInvocationTraceInterceptor.invoke(RemoteInvocationTraceInterceptor.java:77)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy96.getPolicyList(Unknown Source)
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 com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:302)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:217)
at org.springframework.remoting.caucho.HessianExporter.doInvoke(HessianExporter.java:198)
at org.springframework.remoting.caucho.HessianExporter.invoke(HessianExporter.java:118)
at org.springframework.remoting.caucho.HessianServiceExporter.handleRequest(HessianServiceExporter.java:66)
at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:49)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:669)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:585)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
执行的 SQL：
<select id="getPolicyList" parameterType="map" resultType="java.util.HashMap">
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=#{clCode}
<if test="currentPageNum!=null">
limit #{currentPageNum},#{pageSize}
</if>
</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’ 错误，可以参照上面出现这个错误的原因。
