oracle 使用foreach与mybatis进行批量插入

using foreach to do batch insert with mybatis

提问by wqzhou

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:

我正在使用 mybatis，我想在某个表中插入一个 ArrayList。
好吧，在映射器中使用 foreach ，这最终会导致 oracle 异常 ORA_00933 。
这是 mybatis 映射器：

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

请帮帮我，谢谢

one more mapper configuration

另一种映射器配置

<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 命令未正确结束

这是静态插入语句

<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 group谷歌组

回答by blackwizard

Insertinside Mybatis foreachis not batch, this is a single (could become giant) SQL statement and that brings drawbacks:

在 Mybatis foreach 中插入不是批处理，这是一个单一的（可能变成巨大的）SQL 语句并且带来了缺点：

• 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.

• 一些数据库比如这里的Oracle 不支持。
• 在相关情况下：将有大量记录要插入并且数据库配置的限制（默认情况下每个语句大约 2000 个参数）将被命中，如果语句本身变得太大，最终可能会出现 DB 堆栈错误。

Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreachloop. The most important thing is the session Executor type.

不能在 mybatis XML 中对集合进行迭代。只需在Java Foreach循环中执行一个简单的Insert语句。 最重要的是session Executor 类型。

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();

I event think that here it will be enough to use ExecutorType.REUSEwithout flushing statements.

我认为在这里使用ExecutorType.REUSE而不刷新语句就足够了。

Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.

与默认的ExecutorType.SIMPLE不同，该语句将准备一次并为要插入的每条记录执行。

回答by Diego Martinoia

Try extracting the foreach (and change the separator):

尝试提取 foreach（并更改分隔符）：

<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>

I think that your current code creates a new role of values for each element, but a single insert statement (which is not what you want, you want an insert for each element)

我认为您当前的代码为每个元素创建了一个新的值角色，但是只有一个 insert 语句（这不是您想要的，您想要为每个元素插入一个）

回答by freeն

Oracle does not support

甲骨文不支持

insert into xxx values (xxx,xxx),(xxx,xxx)

插入 xxx 值 (xxx,xxx),(xxx,xxx)

maybe you can use insert all like this

也许你可以像这样使用 insert all

    <insert id="batchInsert">
    INSERT ALL
    <foreach collection="list" item="model">
        INTO
        SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id)
        VALUES
        (#{model.role_id}, #{model.permissiongroup_id})
    </foreach>
   </insert>