【java项目实践】mybatis执行update批量更新时报错

深藏阁楼爱情的钟 2022-07-17 03:22 389阅读 0赞

在使用Mybatis 批量更新时，想要批量更新时通常在mapper中这么写：

20160829163503875

定义Mapper Dao接口中定义：

20160829163514156

最后在service中调用：

20160829163524516

生成的sql直接放到mysql中运行完全没有问题，但是mybatis执行的时候却会报错：

  1. <span style="color:#ff0000;">八月 29, 2016 4:17:08 下午 org.apache.catalina.core.StandardWrapperValve invoke
  2. 严重: Servlet.service() for servlet [springMVC] in context with path [/yihg-travel-api] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
  3. ### 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 assistant_group_guide
  4.              SET guide_name = '陈玉梅（华1）',
  5.                 gui' at line 15
  6. ### The error may involve com.yihg.travel.api.dao.AssistantGroupGuideMapper.updateByGuideGroupId-Inline
  7. ### The error occurred while setting parameters
  8. ### SQL: update assistant_group_guide     SET guide_name = ?,     guide_certificate_no = ?,     guide_license_no = ?,     guide_mobile = ?,          guide_photo = ?,     driver_id = ?,     driver_name = ?,           driver_license_car = ?,          driver_mobile = ?,           driver_photo = ?,          state = ?     where group_id = ? and guide_id = ?    ;     update assistant_group_guide     SET guide_name = ?,     guide_certificate_no = ?,     guide_license_no = ?,     guide_mobile = ?,          guide_photo = ?,     driver_id = ?,     driver_name = ?,           driver_license_car = ?,          driver_mobile = ?,           driver_photo = ?,          state = ?     where group_id = ? and guide_id = ?
  9. ### 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 assistant_group_guide
  10.              SET guide_name = '陈玉梅（华1）',
  11.                 gui' at line 15
  12. ; 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 assistant_group_guide
  13.              SET guide_name = '陈玉梅（华1）',
  14.                 gui' at line 15] with root cause
  15. 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 assistant_group_guide
  16.              SET guide_name = '陈玉梅（华1）',
  17.                 gui' at line 15
  18.     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  19.     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  20.     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  21.     at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  22.     at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
  23.     at com.mysql.jdbc.Util.getInstance(Util.java:387)
  24.     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
  25.     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
  26.     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
  27.     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
  28.     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
  29.     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
  30.     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
  31.     at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
  32.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
  33.     at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
  34.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
  35.     at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131)
  36.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
  37.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
  38.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  39.     at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  40.     at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  41.     at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  42.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
  43.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  44.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  45.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  46.     at java.lang.reflect.Method.invoke(Method.java:498)
  47.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
  48.     at com.sun.proxy.$Proxy18.update(Unknown Source)
  49.     at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295)
  50.     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
  51.     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
  52.     at com.sun.proxy.$Proxy23.updateByGuideGroupId(Unknown Source)
  53.     at com.yihg.travel.api.service.impl.AssistantGroupServiceImpl.findToErpGroupInfo(AssistantGroupServiceImpl.java:192)
  54.     at com.yihg.travel.api.controller.query.AssistantGuestController.toYihgErpGroupList(AssistantGuestController.java:37)
  55.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  56.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  57.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  58.     at java.lang.reflect.Method.invoke(Method.java:498)
  59.     at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
  60.     at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
  61.     at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
  62.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
  63.     at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
  64.     at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
  65.     at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
  66.     at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
  67.     at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
  68.     at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
  69.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
  70.     at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
  71.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
  72.     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
  73.     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
  74.     at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
  75.     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
  76.     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
  77.     at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
  78.     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
  79.     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
  80.     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
  81.     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
  82.     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
  83.     at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
  84.     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
  85.     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
  86.     at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
  87.     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
  88.     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
  89.     at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
  90.     at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
  91.     at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
  92.     at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
  93.     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
  94.     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
  95.     at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
  96.     at java.lang.Thread.run(Thread.java:745)</span>

这是因为配置mysql的时候没有开启批量插入，不开启的话只能允许操作一条sql语句，所以只需要修改一处即可：将properties配置文件中的数据库连接信息URL后面添加上&allowMultiQueries=true就可以了，问题完美解决。如图：

20160829163539391

注意：请不要将“&”符号写成“&”

同时执行多条sql的办法：

1、最简单的办法：在MySQL的连接字符串中设置allowMultiQueries参数置为true。(只有MySQL Connector/J 3.1.1以上版本才支持) 。例如：在jdbc下设置连接字符串的时候设成如下的形式：
jdbc:mysql://172.17.42.197:3306/yihg_openapi?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true就可以执行多条语句了。
在odbc下也是可以设置的，方法如下：
设置 ODBC — 配置 –Detials — Flags 3 — 钩上 Allow multiple statements，这样就可以了。
结论：第一种方式最简单。
2、在程序中对SQL语句以分号拆分成多条SQL语句，然后使用Statement的addBatch方法，最后executeBatch就行。
希望对以后遇到此类问题的朋友有所帮助。

发表评论

表情：
评论列表 （有 0 条评论，389人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Java--Mybatis批量更新

    一、for each 标签 想要实现批量更新我们就需要使用到for each标签 对于foreach标签的解释参考了网上的资料，具体如下： 1、foreach的主要

    Dear 丶Dear 丶/ 0 赞/ 130 阅读