使用foreach与mybatis进行批量插入 [英] using foreach to do batch insert with mybatis
本文介绍了使用foreach与mybatis进行批量插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用mybatis，我想在某个表中插入ArrayList.
可以在mapper中使用foreach了，那么最终会出现oracle异常ORA_00933.
这是mybatis映射器:
I am using mybatis and i would like to insert an ArrayList to some table.
all right using foreach in mapper, well this ends up with oracle exception ORA_00933 .
this is the mybatis mapper:
<insert id="batchInsert" parameterType="java.util.List">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
<foreach collection="list" item="model" index="index" separator=",">
(#{model.role_id}, #{model.permissiongroup_id})
</foreach>
</insert>
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
### The error may involve com.gaotime.platform.system.mapper.RolePermissiongroupMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , (?, ?) , (?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:95)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
at com.sun.proxy.$Proxy5.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:236)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy15.batchInsert(Unknown Source)
at com.gaotime.platform.system.service.RolePermissiongroupService.batchInsert(RolePermissiongroupService.java:18)
at com.gaotime.platform.system.action.RolePermissiongroupAction.execute(RolePermissiongroupAction.java:54)
at com.gaotime.platform.handler.MqMessageHandler.handle(MqMessageHandler.java:20)
at unitask.ums.activemq.HandlerThread.run(HandlerThread.java:51)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
at com.sun.proxy.$Proxy27.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
请帮帮我，谢谢
另一个映射器配置
<insert id="batchInsert" parameterType="java.util.List">
<foreach collection="list" item="model" index="index" separator=",">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
(#{model.role_id}, #{model.permissiongroup_id})
</foreach>
</insert>
我收到此消息
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
### The error may involve com.gaotime.platform.system.mapper.RolePermissiongroupMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
这是静态插入语句
here is the static insert statement
<insert id="batchInsert" parameterType="java.util.List">
<!-- <foreach collection="list" item="model" index="index" separator=";">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
(#{model.role_id,jdbcType=NUMERIC}, #{model.permissiongroup_id,jdbcType=NUMERIC})
</foreach> -->
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values(5,5);
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values(6,6)
</insert>
以及
19:00:21,531 DEBUG Thread-11 RolePermissiongroupMapper.batchInsert:139 - ==> Preparing: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values(5,5); insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values(6,6)
19:00:21,535 DEBUG Thread-11 RolePermissiongroupMapper.batchInsert:139 - ==> Parameters:
19:00:21,553 DEBUG Thread-11 impl.NewPooledConnection:430 - com.mchange.v2.c3p0.impl.NewPooledConnection@699238ad handling a throwable.
java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
对于更新，我仍然需要帮助.过来 Google网上论坛
For update, I still need help. Come over google group
推荐答案
在Mybatis foreach 中插入不是批处理，这是单个(可能会变大)SQL语句，这带来了缺点:
Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:
- 此处不支持某些数据库，例如Oracle.
- 在相关情况下:将插入大量记录，并且数据库配置的限制(默认情况下，每个语句大约2000个参数)将受到影响，并且如果语句本身变得太大，最终可能会导致数据库堆栈错误. /li>
- some database such as Oracle here does not support.
- in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
不得在mybatis XML中对集合进行迭代. 只需在 Java Foreach 循环中执行一个简单的 Insert 语句即可. 最重要的是会话执行器类型 .
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insert statement in a Java Foreach loop. The most important thing is the session Executor type.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
我认为在这里使用 ExecutorType.REUSE 而不用刷新语句就足够了.
I event think that here it will be enough to use ExecutorType.REUSE without flushing statements.
与默认的 ExecutorType.SIMPLE 不同，该语句将准备一次并针对要插入的每条记录执行.
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
这篇关于使用foreach与mybatis进行批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
