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

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

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

![20160829163503875][]

定义Mapper  Dao接口中定义：

![20160829163514156][]

最后在service中调用：

![20160829163524516][]

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

<span style="color:#ff0000;">八月 29, 2016 4:17:08 下午 org.apache.catalina.core.StandardWrapperValve invoke
    严重: Servlet.service() for servlet [springMVC] in context with path [/yihg-travel-api] threw exception [Request processing failed; nested exception is 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 assistant_group_guide
    			 SET guide_name = '陈玉梅（华1）',
    				gui' at line 15
    ### The error may involve com.yihg.travel.api.dao.AssistantGroupGuideMapper.updateByGuideGroupId-Inline
    ### The error occurred while setting parameters
    ### 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 = ?
    ### 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
    			 SET guide_name = '陈玉梅（华1）',
    				gui' at line 15
    ; 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
    			 SET guide_name = '陈玉梅（华1）',
    				gui' at line 15] with root 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
    			 SET guide_name = '陈玉梅（华1）',
    				gui' at line 15
    	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:423)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    	at com.mysql.jdbc.Util.getInstance(Util.java:387)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
    	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(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    	at com.sun.proxy.$Proxy18.update(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    	at com.sun.proxy.$Proxy23.updateByGuideGroupId(Unknown Source)
    	at com.yihg.travel.api.service.impl.AssistantGroupServiceImpl.findToErpGroupInfo(AssistantGroupServiceImpl.java:192)
    	at com.yihg.travel.api.controller.query.AssistantGuestController.toYihgErpGroupList(AssistantGuestController.java:37)
    	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:498)
    	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
    	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
    	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:963)
    	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    	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:240)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
    	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
    	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
    	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)</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就行。  
希望对以后遇到此类问题的朋友有所帮助。

[20160829163503875]: /images/20220717/648a94766eea42c7b51ba168c270ebf2.png
[20160829163514156]: /images/20220717/2530ce81d68242efbad0a41d4393a6ff.png
[20160829163524516]: /images/20220717/1b131162e91c42b284b7819c97b8284e.png
[20160829163539391]: /images/20220717/50227daf8af7497a8abfd37eb26b6931.png