yzn77
我是洋葱啊
采纳率45.5%
2017-08-11 08:24

Spring mvc 中的mapper.xml的SQL语句报错

Error updating 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 'interval=2,conn_timeout=1,resp_timeout=1,test_retry=1,freq=2,monit_conn_timeout=' at line 1

The error may involve com.security.dao.TemplateNetworkDao.updateTempNetwork-Inline

The error occurred while setting parameters

SQL: update conf_template_network set interval=?,conn_timeout=?,resp_timeout=?,test_retry=?,freq=?,monit_conn_timeout=?,monit_resp_timeout=?,monit_retry=? where id=?

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 'interval=2,conn_timeout=1,resp_timeout=1,test_retry=1,freq=2,monit_conn_timeout=' at line 1

; 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 'interval=2,conn_timeout=1,resp_timeout=1,test_retry=1,freq=2,monit_conn_timeout=' at line 1_

我的SQL语句是

 <update id="updateTempNetwork" parameterType="int">
        update conf_template_network set interval=#{interval},conn_timeout=#{conn_timeout},resp_timeout=#{resp_timeout},test_retry=#{test_retry},freq=#{freq},monit_conn_timeout=#{monit_conn_timeout},monit_resp_timeout=#{monit_resp_timeout},monit_retry=#{monit_retry}
        where id=#{id}  
    </update>

找了半天也没找到哪写错了,而且也没有使用mysql的关键字,提示说monit_conn_timeout附近的错误

## ——————————————————————————————————**

## 更新:好像是SQL语句太长了,把前边的字段改短后,前边的字段就能正确显示,但是后边超出长度的部分还是会报错

我把前边长的字段用a,b替代之后,变短了:

  <update id="updateTempNetwork" parameterType="com.security.entity.TemplateNetwork">
        update conf_template_network set interval=#{interval},conn_timeout=#{conn_timeout},resp_timeout=#{resp_timeout},test_retry=#{test_retry},freq=#{freq},a=#{a},b=#{b},monit_retry=#{monit_retry}
        where id=#{id}
    </update>

错误就移到了后边:
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 'interval=3,conn_timeout=3,resp_timeout=3,test_retry=3,freq=1,a=2,b=1,monit_retry' at line 1
; 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 'interval=3,conn_timeout=3,resp_timeout=3,test_retry=3,freq=1,a=2,b=1,monit_retry' at line 1
好像跟长度又没关系 把后边的字段都砍掉了之后

  <update id="updateTempNetwork" parameterType="com.security.entity.TemplateNetwork">
        update conf_template_network set interval=#{interval},conn_timeout=#{conn_timeout},resp_timeout=#{resp_timeout},test_retry=#{test_retry},freq=#{freq}
        where id=#{id}
    </update>

还是会报错: 移到了最后:

SQL: update conf_template_network set interval=?,conn_timeout=?,resp_timeout=?,test_retry=?,freq=? where id=?

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 'interval=1,conn_timeout=2,resp_timeout=2,test_retry=1,freq=1 where id=1' at line 1

; 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 'interval=1,conn_timeout=2,resp_timeout=2,test_retry=1,freq=1
where id=1' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at $Proxy29.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at $Proxy37.updateTempNetwork(Unknown Source)
at com.security.service.impl.TemplateNetworkImpl.updateTempNetwork(TemplateNetworkImpl.java:35)
at com.security.service.impl.TemplateNetworkImpl$$FastClassBySpringCGLIB$$16455d80.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
at com.security.service.impl.TemplateNetworkImpl$$EnhancerBySpringCGLIB$$a87fea1.updateTempNetwork()
at com.security.controller.TemplateNetworkController.updateTempNetwork(TemplateNetworkController.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
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 'interval=1,conn_timeout=2,resp_timeout=2,test_retry=1,freq=1
where id=1' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at $Proxy48.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

结贴啦结帖啦!

休息两天果然脑细胞就活过来了,周一一大早就找到了bug

interval是mySQL的关键字,用作变量名,所以报错了,换个名字就好了。已经是第二次撞到关键字了,以后起变量名要小心了。

非常感谢各位的帮助!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

13条回答

  • Wyk_yk meRcy94 4年前

    楼主,这个是mybatis的映射文件,sql语句的?你传进去paramaterType你用了int,而你的参数不止一个int,应该用map或者用一个实体类包装。

    点赞 评论 复制链接分享
  • u013240223 如果能跟着心在走 4年前

    这怎么会太长,字段类型一致吗

    点赞 评论 复制链接分享
  • JSXHMQS JSXHMQS 4年前

    public boolean update(D entity);


    <![CDATA[
    UPDATE user SET
    id=#{id},
    username=#{username},
    password=#{password}
    WHERE id=#{id}
    ]]>

    点赞 评论 复制链接分享
  • JSXHMQS JSXHMQS 4年前

    你这sql很有问题 你就一个id 你修改什么

    点赞 评论 复制链接分享
  • zhaojun302563746 小菜鸟向前冲 4年前

    很想知道你的updateTempNetwork()这个方法怎么写的

    点赞 评论 复制链接分享
  • Inverse_fang Inverse_fang 4年前

    parameterType="int"改成parameterType="你的实体类(包名+类名)"

    点赞 评论 复制链接分享
  • Small_Mouse0 鼠小 4年前

    建议 ,你先使用log4j把,运行的sql语句打印出来,,要不单纯看是很难决绝问题的。

    在mybatis配置文件下新建一个 log4j.perporties(复制下面代码进去,保存运行即可看到,运行会打印出 sql 语句)

     ### 把日志信息输出到控制台 ###
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender   
    #log4j.appender.stdout.Target=System.err
    log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout   
    
    log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n  
    
    log4j.logger.org.apache=INFO
    

    然后再,把sql语句拿出来,,,单看sql语句,,进行修改,,再修改mapping文件

    要不真的很改好。

    如果对你有帮助,,谢谢采纳^.^

    点赞 评论 复制链接分享
  • chenguoqing666 Humorist 4年前

    是不是入参有问题,你你那个入参为int,取得值对应吗?

    点赞 评论 复制链接分享
  • sounderme sounderme 4年前

    像你这样的有很多参数,参数类型只是一个int貌似不行吧

    点赞 评论 复制链接分享
  • qq_36755012 Jerry_loon 4年前

    可能是monit_conn_timeout 值没取到吧

    点赞 评论 复制链接分享
  • qq_33727653 砸死接触 4年前

    你把参数类型parameterType="int" 设置了 如果是int 怎么可能还会有 interval 等等参数呢? 去掉他 ,你的参数应该是个对象吧?

    点赞 评论 复制链接分享
  • qq_38717074 KRUN_DU 4年前

    难道是字段太长?再看看标签id的对应关系以及字段名称

    点赞 评论 复制链接分享
  • qq_35728177 Tsui丶 4年前

    先确定#{monit_conn_timeout}是否有值

    点赞 评论 复制链接分享