关闭

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

标签: mybatismybatis update批量更新
5295人阅读 评论(0) 收藏 举报
分类:

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


定义Mapper  Dao接口中定义:


最后在service中调用:


生成的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就可以了,问题完美解决。如图:


注意:请不要将“&”符号写成“&amp;”

同时执行多条sql的办法:

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




1
0
查看评论

MyBatis Batch Update Exception 使用foreach 批量update 出错

源地址   http://quabr.com/22829539/mybatis-batch-update-exception 使用如下Mybatis  map xml文件  UPDATE testcase_node ...
  • jingshuaizh
  • jingshuaizh
  • 2015-02-16 16:20
  • 16736

mybatis批量更新报错问题解决

最近在做一个会员营销项目,其中有业务涉及到批量update问题,我用mybatic foreach实现批量update,但sql一直没问题,但是偏偏报以下奇葩问题: org.springframework.jdbc.BadSqlGrammarException: ### Error updatin...
  • qq_25838503
  • qq_25838503
  • 2016-09-01 15:21
  • 1548

mybatis执行update批量更新时报错

在使用Mybatis 批量更新时,报如下错误### The error may involve cn.jointwisdom.vUniversity.dao.updateAccessNum-Inline ### The error occurred while setting parameter...
  • liufei198613
  • liufei198613
  • 2016-11-15 20:49
  • 549

Mybatis批量更新

Mybatis批量更新 批量操作就不进行赘述了。减少服务器与数据库之间的交互。网上有很多关于批量插入还有批量删除的帖子。但是批量更新却没有详细的解决方案。 实现目标 这里主要讲的是1张table中。根据不同的id值,来update不同的property。 数据表:1张。Tblsupertit...
  • cyd1919
  • cyd1919
  • 2012-10-19 09:38
  • 10063

Mybatis中进行批量更新(updateBatch)

背景描述:通常如果需要一次更新多条数据有两个方式,(1)在业务代码中循环遍历逐条更新。(2)一次性更新所有数据(更准确的说是一条sql语句来更新所有数据,逐条更新的操作放到数据库端,在业务代码端展现的就是一次性更新所有数据)。两种方式各有利弊,下面将会对两种方式的利弊做简要分析,主要介绍第二种方式在...
  • xyjawq1
  • xyjawq1
  • 2017-07-02 16:17
  • 30561

mybatis的批量插入与更新

一:Oracle数据库 1:批量插入     insert into RECIPEDETAIL (RDID, ROID, TYPE,        NAME, MEDIWEIGHT, MEDINUM,     ...
  • jackyxwr
  • jackyxwr
  • 2016-01-27 11:48
  • 22697

mybatis执行批量更新update

mybatis执行批量更新update Mybatis的批量插入这里有http://ljhzzyx.blog.163.com/blog/static/38380312201353536375/。目前想批量更新,如果update的值是相同的话,很简单,组织 update table set col...
  • twj13162380953
  • twj13162380953
  • 2017-03-13 16:53
  • 3287

FAQ(40):org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: com.m

FAQ(40):org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorExcepti...
  • qq_29166327
  • qq_29166327
  • 2018-01-04 09:39
  • 217

mybatis 的批量操作,查询、更新、插入

mybatis 的批量更新 主要依靠 foreach 标签拼接sql 实现批量操作。第一种方法,拼装成一条sql<update id="batchUpdateRawEventStatus" parameterType="java.util.List"&...
  • u012373815
  • u012373815
  • 2017-02-14 00:09
  • 1951

springboot整合mybatis中的坑

项目中,在使用mybatis进行批量的更新删除操作的时候,会遇到一个大坑。 你会发现你后台打印的sql语句一点问题都没没有,然后你复制到你的小海豚或者navicat里面执行也是没有任何毛病的,但就是你的项目启动起来,执行批量操作的时候会出BUG 最后才发现,问题出在了连接数据库语...
  • fan510988896
  • fan510988896
  • 2017-01-13 17:14
  • 1200
    个人资料
    • 访问:258764次
    • 积分:3233
    • 等级:
    • 排名:第12523名
    • 原创:111篇
    • 转载:43篇
    • 译文:0篇
    • 评论:65条
    最新评论