开源问答 技术问答
正文

在mysql下批量更新出错！

圊國圊國 发布于 2016/01/26 14:15
阅读 2K+
收藏 0
答案 4

在数据库执行sql是没有问题的：

UPDATE jhope_role_t              SET r_name = ?,     r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_id = ?          ;       UPDATE jhope_role_t              SET r_name = ?,                                                       r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_id = ?

下面是mybatis的配置： 

<update id="updates">  <foreach collection="list" index="index" item="item" separator=";">  UPDATE jhope_role_t <trim prefix="SET" suffixOverrides=",">  <if test="item.r_name!=null">  r_name = #{item.r_name,jdbcType=VARCHAR}, </if>  <if test="item.r_code!=null">  r_code = #{item.r_code,jdbcType=VARCHAR}, </if>  <if test="item.r_state!=null">  r_state = #{item.r_state,jdbcType=INTEGER}, </if>  <if test="item.r_describe!=null">  r_describe = #{item.r_describe,jdbcType=VARCHAR}, </if>  </trim>  WHERE r_id = #{item.r_id,jdbcType=INTEGER} </foreach> </update>


下面是异常信息。


### 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 'UPDATE jhope_role_t

             SET r_name = '12x12',
                
        ' at line 14
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE jhope_role_t              SET r_name = ?,                                                       r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_id = ?          ;              UPDATE jhope_role_t              SET r_name = ?,                                                       r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
; 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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
at org.apache.cxf.service.invoker.AbstractInvoker.createFault(AbstractInvoker.java:162) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:128) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:200) ~[cxf-rt-frontend-jaxrs-3.1.4.jar:3.1.4]
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:99) ~[cxf-rt-frontend-jaxrs-3.1.4.jar:3.1.4]
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) [cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:251) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:180) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:293) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:212) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) [servlet-api.jar:na]
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:268) [cxf-rt-transports-http-3.1.4.jar:3.1.4]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:184) [catalina.jar:9.0.0.M1]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-websocket.jar:9.0.0.M1]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:217) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:184) [catalina.jar:9.0.0.M1]
at com.web.resources.ResourceFilter.doFilter(ResourceFilter.java:61) [classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:217) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:184) [catalina.jar:9.0.0.M1]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121) [spring-web-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:217) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:184) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:105) [catalina.jar:9.0.0.M1]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:9.0.0.M1]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [catalina.jar:9.0.0.M1]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) [catalina.jar:9.0.0.M1]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:9.0.0.M1]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:361) [catalina.jar:9.0.0.M1]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1080) [tomcat-coyote.jar:9.0.0.M1]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:75) [tomcat-coyote.jar:9.0.0.M1]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:757) [tomcat-coyote.jar:9.0.0.M1]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1515) [tomcat-coyote.jar:9.0.0.M1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:9.0.0.M1]
at java.lang.Thread.run(Thread.java:744) [na:1.8.0]
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### 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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE jhope_role_t              SET r_name = ?,                                                       r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_id = ?          ;              UPDATE jhope_role_t              SET r_name = ?,                                                       r_code = ?,                                                       r_state = ?,                                                       r_describe = ?              WHERE r_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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
; 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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~[mybatis-spring-1.2.3.jar:1.2.3]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399) ~[mybatis-spring-1.2.3.jar:1.2.3]
at com.sun.proxy.$Proxy16.update(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:269) ~[mybatis-spring-1.2.3.jar:1.2.3]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) ~[mybatis-3.3.0.jar:3.3.0]
at com.sun.proxy.$Proxy24.updates(Unknown Source) ~[na:na]
at com.jhope.security.service.impl.RoleService.batchExecute(RoleService.java:38) ~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0]
at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180) ~[cxf-core-3.1.4.jar:3.1.4]
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) ~[cxf-core-3.1.4.jar:3.1.4]
... 43 common frames omitted
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 'UPDATE jhope_role_t
             SET r_name = '12x12',
                
        ' at line 14
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0]
at java.lang.reflect.Constructor.newInstance(Constructor.java:408) ~[na:1.8.0]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) ~[druid-1.0.16.jar:1.0.16]
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.0.16.jar:1.0.16]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) ~[druid-1.0.16.jar:1.0.16]
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131) ~[druid-1.0.16.jar:1.0.16]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) ~[druid-1.0.16.jar:1.0.16]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.3.0.jar:3.3.0]
at com.sun.proxy.$Proxy31.execute(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) ~[mybatis-3.3.0.jar:3.3.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170) ~[mybatis-3.3.0.jar:3.3.0]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386) ~[mybatis-spring-1.2.3.jar:1.2.3]
... 55 common frames omitted
14:11:16.558 [http-nio-8080-exec-8] DEBUG o.a.cxf.phase.PhaseInterceptorChain - Adding interceptor org.apache.cxf.ws.policy.ServerPolicyOutFaultInterceptor@105e26b to phase setup
14:11:16.558 [http-nio-8080-exec-8] DEBUG o.a.cxf.phase.PhaseInterceptorChain - Adding interceptor org.apache.cxf.interceptor.MessageSenderInterceptor@4b4ffb to phase prepare-send
14:11:16.558 [http-nio-8080-exec-8] DEBUG o.a.cxf.phase.PhaseInterceptorChain - Adding interceptor org.apache.cxf.jaxrs.interceptor.JAXRSDefaultFaultOutInterceptor@1fad5c3 to phase marshal
14:11:16.559 [http-nio-8080-exec-8] DEBUG o.a.cxf.phase.PhaseInterceptorChain - Chain org.apache.cxf.phase.PhaseInterceptorChain@e382bf was created. Current flow:
  setup [ServerPolicyOutFaultInterceptor]
  prepare-send [MessageSenderInterceptor]
  marshal [JAXRSDefaultFaultOutInterceptor]
收藏 (0)
举报
加载中
最多投票 最新
0
圊國圊國
圊國圊國
2016/01/26 15:26
谢谢大家！ 问题已经解决了。http://my.oschina.net/jsonavaj/blog/265112
评论 (2) 引用此答案 举报
圊國圊國
圊國圊國
2016/07/26 22:47
参考：http://my.oschina.net/jsonavaj/blog/265112 mysql 库做了限制需要配置参数 allowMultiQueries=true
回复 举报
skyler1
skyler1
2016/07/26 21:37
你好，我遇到和你相同的问题，请问你的解决办法是什么，急求答案，感谢
回复 举报
0
BabyMason
BabyMason
2016/01/26 14:53
### The error may involve defaultParameterMap 
### The error occurred while setting parameters ，注意下这两句应该是你在传递参数的时候 数据库语句是没办法运行 的 ，你debug下，会console 数据库语句，经现实的数据库语句在mysql执行下试试
评论 (1) 引用此答案 举报
圊國圊國
圊國圊國
2016/01/26 15:06
恩，我测试了一条修改没事。估计是遇到了 符号 ;
回复 举报
0
lyle_luo
lyle_luo
2016/01/26 14:54
SQL语法错误 ，at line 14
评论 (0) 引用此答案 举报
0
skyler1
skyler1
2016/07/26 21:39
你好，我遇到和你相同的问题，请问你的解决办法是什么，急求答案，感谢
评论 (2) 引用此答案 举报
圊國圊國
圊國圊國
2016/07/26 22:59
参考：http://my.oschina.net/jsonavaj/blog/265112 mysql 库做了限制需要配置参数 allowMultiQueries=true
回复 举报
圊國圊國
圊國圊國
2016/07/26 22:58
参考：http://my.oschina.net/jsonavaj/blog/265112 mysql 库做了限制需要配置参数 allowMultiQueries=true
回复 举报
圊國圊國
圊國圊國
最近登录：2020/09/05 10:16
全部

圊國圊國的其他提问

github服务端缺少很多文件
1 回答
286 阅读
sina 后台httpclient模拟登入，绑定微博同步消息发送
3 回答
450 阅读
新浪httpclient模拟登入，调用api
5 回答
750 阅读
模仿qq评论表设计。。。。。。
2 回答
394 阅读
jni java.lang.UnsatisfiedLinkError
1 回答
294 阅读

类似问题

spring模拟请求测试报错
4 回答
517 阅读
Mybatis执行批量更新，或者插入时，执行成功但是返回结果是-1，怎么处理
0 回答
1130 阅读
mybatis,mysql,诡异的错误. sql语句可以执行,但是用mybatis操作就报错.
17 回答
18563 阅读
mybatis list参数问题
1 回答
105 阅读
oracle sql防重失效问题 ，请大家帮忙看看
0 回答
324 阅读
druid merge sql error QUES %问题（非常感谢大家能够帮忙）
0 回答
1333 阅读
kettle 在SQLserver2008新建资源库，会报错字段类型型错误，是个BUG？？？
0 回答
1518 阅读
spring+mybatis update时问题求救（菜鸟求救）
3 回答
846 阅读
springMVC+Mybatis整合 Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
7 回答
24571 阅读
(急)通用mapper3.4，根据主键查询或删除报错，类型转换出错或者不能识别主键
1 回答
2033 阅读
springmvc+mybatis
3 回答
718 阅读
Jeesite框架mysql数据库初始报错
2 回答
2514 阅读
如何向resultMap中的collection中配置的select传递条件
4 回答
8011 阅读
Proftpd(MySQL+Quotas)
0 回答
1056 阅读
memcache安装
0 回答
928 阅读
OSCHINA
登录后可查看更多优质内容
使用微信快捷登录
ad close
返回顶部
顶部