Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge points about how to solve the mistakes in the mybatis implementation of update batch updates. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

Performing update batch updates Times error when using Mybatis batch updates

This is usually written in mapper when you want to update in batches:

Define the definition in the Mapper Dao interface

Finally, call in service

There is no problem with running the generated sql directly in the mysql, but an error will be reported when the mybatis is executed:

August 29, 2016 4:17:08 afternoon org.apache.catalina.core.StandardWrapperValve invoke

Serious: 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 = 'Chen Yumei (Hua 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 = 'Chen Yumei (Hua 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 = 'Chen Yumei (Hua 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 = 'Chen Yumei (Hua 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 (NativeMethod)

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 (NativeMethod)

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)

This is because bulk insertion is not enabled when configuring mysql, and if it is not enabled, you can only operate a sql statement, so you only need to modify one place: add & allowMultiQueries=true to the database connection information URL in the properties configuration file, and the problem is solved perfectly.

As shown in the figure:

Note: please do not write the "&" symbol as "&"

The method of executing multiple sql at the same time

1. The easiest way: set the allowMultiQueries parameter to true in the connection string of MySQL. (only supported by MySQL Connector/J version 3.1.1 or later). For example, when setting the connection string under jdbc, set it to the following form:

Jdbc:mysql://172.17.42.197:3306/yihg_openapi?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true can execute multiple statements.

It can also be set under odbc, as follows:

Set ODBC-configure-Detials-Flags 3-hook to Allow multiple statements, so that's fine.

Conclusion: the first method is the simplest.

2. Split the SQL statement into several SQL statements with semicolons in the program, then use the addBatch method of Statement, and finally executeBatch.

Failed to execute update

Today, when I was working on a distributed refactoring project, I encountered a problem. When executing sql, only update can not succeed. Other statements can be executed normally. The error is as follows: version: org.mybatis:mybatis:3.4.5

Interface:

@ UpdateProvider (type = ManagerProvider.class, method = "updateManager") int updateManager (Manager manager) throws Exception

Error message

Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

Org.springframework.jdbc.UncategorizedSQLException: Error getting generated key or setting result to parameter object. Cause: java.sql.SQLException: disallowed operation

Uncategorized SQLException for SQL []; SQL state [99999]; error code [17090]; disallowed operation; nested exception is java.sql.SQLException: disallowed operation

Tell me why.

Because mybatis generates key by default when executing both insert and update, and then injects it, it will report an error when executing update.

Solution.

Add @ Options (useGeneratedKeys = false) to the interface to prevent mybatis from automatically injecting key, and the problem is solved.

@ UpdateProvider (type = ManagerProvider.class, method = "updateManager") @ Options () / / @ Options (useGeneratedKeys = false) int updateManager (Manager manager) throws Exception

Finally, the version: there is no such problem with this version of org.mybatis:mybatis:3.4.4.

These are all the contents of this article entitled "how to solve the problem of mybatis implementing update batch updates Times". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.