Mysql报错:could not execute statement;MySQLSyntaxErrorException: You have an error in your SQL.

╰+哭是因爲堅強的太久メ 2023-05-21 08:44 19阅读 0赞

报错过程如下:1.对数据库执行了一个update操作,就报错了

Hibernate:   
    update  
        user   
    set  
        age=?,  
        dept\_id=?,  
        dept\_name=?,  
        desc=?,  
        email=?,  
        gender=?,  
        is\_secret=?,  
        login\_time=?,  
        look=?,  
        password=?,  
        phone=?,  
        pic=?,  
        qq\_openid=?,  
        real\_name=?,  
        register\_time=?,  
        username=?,  
        wx\_openid=?   
    where  
        id=?  
2020-04-25 03:32:45.759  WARN 16544 --- \[p-nio-80-exec-5\] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000  
2020-04-25 03:32:45.759 ERROR 16544 --- \[p-nio-80-exec-5\] o.h.engine.jdbc.spi.SqlExceptionHelper   : 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 'desc=null, email='110@aliyun.com', gender=null, is\_secret=null, login\_time=null,' at line 1  
2020-04-25 03:32:45.760 ERROR 16544 --- \[p-nio-80-exec-5\] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush \[org.hibernate.exception.SQLGrammarException: could not execute statement\]  
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL \[n/a\]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement  
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242)  
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)  
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:540)  
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)  
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)  
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:532)  
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)  
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)  
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)  
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)  
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)  
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)  
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)  
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)  
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)  
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)  
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)  
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)  
    at com.sun.proxy.$Proxy192.save(Unknown Source)  
    at com.stephen.service.UserService.update(UserService.java:97)  
    at com.stephen.service.UserService$$FastClassBySpringCGLIB$$c7d8d8fb.invoke(<generated>)  
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)  
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685)  
    at com.stephen.service.UserService$$EnhancerBySpringCGLIB$$82f2a9eb.update(<generated>)  
    at com.stephen.controller.UserController.register(UserController.java:223)  
    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:497)  
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)  
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)  
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)  
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877)  
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783)  
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)  
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)  
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)  
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974)  
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877)  
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)  
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851)  
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)  
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109)  
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)  
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)  
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)  
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)  
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)  
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)  
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)  
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)  
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)  
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)  
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)  
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)  
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)  
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)  
    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)  
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement  
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)  
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)  
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)  
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3203)  
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3076)  
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3477)  
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:145)  
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:600)  
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:474)  
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)  
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)  
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1436)  
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:493)  
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3206)  
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2412)  
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)  
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:156)  
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)  
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)  
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68)  
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)  
    ... 76 more  
Caused by: 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 'desc=null, email='110@aliyun.com', gender=null, is\_secret=null, login\_time=null,' at line 1  
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)  
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)  
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)  
    at com.mysql.jdbc.Util.getInstance(Util.java:408)  
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)  
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)  
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)  
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)  
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)  
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)  
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)  
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)  
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)  
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)  
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)  
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)  
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)  
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)  
    ... 94 more

分析与解决方法:

从1.Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement

2.Caused by: 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 'desc=null, email='110@aliyun.com', gender=null, is\_secret=null, login\_time=null,' at line 1

两个Caused by:已经告知了原因,是因为sql语句执行出错了;

而错误发生在'desc=null, email='110@aliyun.com', gender=null, is\_secret=null, login\_time=null,'这句话中.

我一开始以为是数据传入格式出问题了,后来检查了很多次发现不是;

终于看见了desc这个字段,我想起来在Mysql中desc是个关键字,表示降序.

所以我觉得是冲突了,项目中desc字段其实是descripte(描述)的缩写;

于是我就修改了数据库的该字段,就可以了!

果然编写这个数据库的人不太严谨.