2016-03-28 17:23:38.420 main DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory:463] - Finished creating instance of bean 'Sybase'
2016-03-28 17:23:38.420 main INFO [org.springframework.jdbc.support.SQLErrorCodesFactory:126] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2016-03-28 17:23:38.420 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:199] - Looking up default SQLErrorCodes for DataSource [[email protected]]
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:217] - Database product name cached for DataSource [[email protected]]: name is 'MySQL'
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:175] - SQL error codes for 'MySQL' found
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator:399] - Translating SQLException with SQL state '42000', error code '1064', message [
--- The error occurred while applying a parameter map.
--- Check the mall.org.updateById-InlineParameterMap.
--- Check the statement (update failed).
--- 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 'WHERE id = 12' at line 1]; SQL was [] for task [SqlMapClient operation]
2016-03-28 17:23:38.424 main ERROR [com.jd.dd.mall.service.waiter.impl.OrgServiceImpl:97] - IM data access exception
com.jd.im.data.exception.IMDataAccessException: IM data access exception
at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.handleUserException(IMSqlMapClientTemplateForMultDs.java:280)
at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update(IMSqlMapClientTemplateForMultDs.java:160)
at com.jd.dd.mall.service.waiter.impl.OrgServiceImpl.updateById(OrgServiceImpl.java:106)
at com.jd.dd.mall.service.waiter.impl.OrgServiceImpl.add(OrgServiceImpl.java:92)
at com.jd.dd.mall.web.controller.waiter.OrgManageController.addOrg(OrgManageController.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:746)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:687)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:822)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:796)
at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:66)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:168)
at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:136)
at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:134)
at com.jd.service.waiter.OrgControllerTest.add(OrgControllerTest.java:53)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the mall.org.updateById-InlineParameterMap.
--- Check the statement (update failed).
--- 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 'WHERE id = 12' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:206)
at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:381)
at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update(IMSqlMapClientTemplateForMultDs.java:134)
... 54 more
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the mall.org.updateById-InlineParameterMap.
--- Check the statement (update failed).
--- 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 'WHERE id = 12' at line 1
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:383)
at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:381)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
... 56 more
and my Ibatis SQL
<update id="updateById" parameterClass="Org">
UPDATE org
SET name = #name:VARCHAR#,
<isNotNull property="tree_level">tree_level = #tree_level#, </isNotNull>
<isNotNull property="yn">yn = #yn#, </isNotNull>
<isNotNull property="route">route = #route:VARCHAR#, </isNotNull>
`version` = `version` + 1,
<isNotNull property="editor">editor = #editor:VARCHAR# </isNotNull>
WHERE id = #id#
</update>
like this stack does not clearly indicate the cause of SQL problems, troubleshooting problems must be according to the error code to check. Baidu checked the 1064 error code. The column name of the table overlaps with the Mysql keyword. OK, I took this SQL close to the mysql client query interface and found that the name field changed to the color of the keyword. I thought I found the problem, so I put back quotes around the name. Run the unit test again and report an error of 1064.
There is no way out, only ibATIS executed SQL typing out, put into mysql client to see. OK, log4J plus log, the execution of IBATIS SQL typing out. Run the unit test again and the SQL doesn’t come out.
There is a feeling of frustration that should not be called indolent every day!
You have no choice but to look at SQL with your eyes. If the Editor field is null, then there are extra commas before the WHERE. Adjust the SQL.
<update id="updateById" parameterClass="Org">
UPDATE org
SET id = #id# ,
<isNotNull property="name">`name` = #name:VARCHAR#, </isNotNull>
<isNotNull property="tree_level">tree_level = #tree_level#, </isNotNull>
<isNotNull property="yn">yn = #yn#, </isNotNull>
<isNotNull property="route">route = #route:VARCHAR#, </isNotNull>
<isNotNull property="editor">editor = #editor:VARCHAR# , </isNotNull>
`version` = `version` + 1
WHERE id = #id#
</update>
Then run the unit test and the SQL executes successfully. Just log can not type out SQL, this time to type out. Where have you been when I need you?Mysql’s error code is not reliable at all! The SQL syntax is obviously wrong, but the code of 1064 is quoted.
Remove the back quotes from the Name and Version fields and the SQL will still execute normally. Fun!
Read More:
- Spring error: ora-01017: invalid username / password; login denied
- How to Fix error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarE
- [Jackson exception] com.fasterxml.jackson . databind.JsonMappingException Exception handling method
- How to Fix The error may exist in com/kuang/dao/UserMapper.xml
- Spring configuration transaction, JUnit unit test error “failed to load ApplicationContext”
- Spring boot running appears java.lang.IllegalStateException : Failed to load ApplicationContext
- Failed to determine a suitable driver class error in mybatis
- SQL state [null]; error code [0]; Error; nested exception is java.sql.SQLException: Error] with root
- Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure (How to Fix)
- Failed to obtain JDBC Connection
- MongoDB Error: Query failed with error code 2 and error message ‘Field ‘locale’ is invalid
- SQL Error: 0, SQLState: 08S01 & Communications link failure
- Resolve rabbitmq declarationexception: failed to declare queue exception
- Related configuration of mybatis project
- Spring jacjson version conflict “nosuchme” thodError:com.fasterxml.jackson.databind.ObjectMapper.canSerialize “
- com.netflix.zuul.exception.ZuulException: Forwarding error
- Error: java.lang.NumberFormatException : multiple points
- When feign is called, the solution of request method ‘post’ not supported appears
- ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’
- Error: Failed to execute goal org.mybatis.generator:mybatis-generator-maven-plugin:1.3.2