MyBatis中的insert\update\delete标签中批量执行多条SQL

mybatis原则上是不能够一次批量执行多条SQL语句，比如新增、删除、更新等，但是修改jdbc url连接带参数可以实现相同效果

JDBC连接配置：

    
spring:datasource:username: rootpassword: mysql# allowMultiQueries 是否允许一条SQL语句包含多个执行SQL以分号;分隔url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC&allowMultiQueries=truedriver-class-name: com.mysql.cj.jdbc.Driver

其中：

allowMultiQueries=true 允许一条SQL语句包含多个执行SQL以分号;分隔，是必须添加的

比如：批量新增（一个标签里面可以存放多个 insert 语句，同时执行批量新增）

<!-- 批量插入用户 -->
<insert id="batchInsert"><foreach collection="list" item="item" index="index"  separator=";" >insert into user(username,password,isadmin) values(#{item.userName}, #{item.password}, #{item.isAdmin})</foreach></insert>

比如：批量更新（一个标签中可以执行多条SQL语句，以分号;分隔）

<!-- 批量 更新用户 -->
<update id="batchUpdate">update user set password = #{password} where id = #{id} ;update user_info set user_name = #{user_name}, addr = #{addr} where where id = #{id}</update>

同理：批量删除也是可以的

若是出现以下错误：则是jdbc中的URL没有配置开启参数：allowMultiQueries = true

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 'insert into user(username,password,isadmin) values('曾文轩', '93030' at line 4
### The error may exist in file [E:\javawork\spring-boot-learn\spring-boot-learn-mybatis\target\classes\mybatis\mapper\IUserMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into user(username,password,isadmin)           values(?, ?, ?)      ;           insert into user(username,password,isadmin)           values(?, ?, ?)      ;           insert into user(username,password,isadmin)           values(?, ?, ?)      ;           insert into user(username,password,isadmin)           values(?, ?, ?)      ;           insert into user(username,password,isadmin)           values(?, ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: 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 'insert into user(username,password,isadmin) values('曾文轩', '93030' at line 4
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'insert into user(username,password,isadmin) values('曾文轩', '93030' at line 4at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)at com.sun.proxy.$Proxy83.insert(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)at com.sun.proxy.$Proxy84.batchInsert(Unknown Source)at com.tianya.springboot.mybatis.test.UserBeanTest.batchInsert(UserBeanTest.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.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:132)at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:124)at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:74)at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)at java.util.ArrayList.forEach(ArrayList.java:1255)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)at java.util.ArrayList.forEach(ArrayList.java:1255)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:542)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:770)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:464)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)
Caused by: java.sql.SQLSyntaxErrorException: 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 'insert into user(username,password,isadmin) values('曾文轩', '93030' at line 4at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)at com.sun.proxy.$Proxy109.execute(Unknown Source)at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)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 org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)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:433)... 71 more