全部插入-超过1000行 [英] INSERT ALL - For more than 1000 rows

问题描述

我的程序中有一个 INSERT ALL 查询，

I have an INSERT ALL query in my program like:

<insert id="insertRecord" parameterType="java.util.List">
        INSERT ALL
        <foreach collection="myList" item="addrElement" index="index">
            INTO MYTABLE (COLUMN1,COLUMN2,COLUMN3) values (#{addrElement.element1},#{addrElement.element2},#{addrElement.element3})
        </foreach>
        SELECT * FROM dual
</insert>

该列表将至少保存10000条记录.

The list will hold a minimum of 10000 records.

很明显，这会引发异常，因为 INSERT ALL 不能处理1000条以上的记录.

Obviously, this is throwing an exception since INSERT ALL cannot handle more than 1000 records.

;错误的SQL语法[];嵌套的异常是java.sql.SQLSyntaxErrorException:ORA-00913:值太多

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00913: too many values

我已经检查了SO和其他网站中的许多答案，以检查是否仅对 SELECT 查询指定了超过1000行的记录，而对于 INSERT 没有指定记录查询.

I have checked many answers in SO as well as other sites to check that, picking records more than 1000 rows is specified only for SELECT query and not for an INSERT query.

有人可以帮我吗?会很有帮助的.

Can someone lend me a hand on this? Would be much helpful.

推荐答案

您需要执行批量插入.

int batchSize = 100;
try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  YourMapper mapper = sqlSession.getMapper(YourMapper.class);
  int size = list.size();
  for (int i = 0; i < size;) {
    mapper.insertRecord(list.get(i));
    i++;
    if (i % batchSize == 0 || i == size) {
      sqlSession.flushStatements();
      sqlSession.clearCache();
    }
  }
  sqlSession.commit();
}

您应该为batchSize找到一个合适的值(取决于各种因素).

You should find an appropriate value for the batchSize (it depends on various factors).

insert语句非常简单

The insert statement is pretty straight forward.

<insert id="insertRecord">
  INSERT INTO MYTABLE (COLUMN1, COLUMN2, COLUMN3)
  VALUES (#{addrElement.element1}, #{addrElement.element2}, #{addrElement.element3})
</insert>

我们有一个常见问题解答输入.

这篇关于全部插入-超过1000行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！