java - Mybatis 查询插入 java 对象列表失败

Question

我正在尝试使用 Mybatis 插入对象列表并收到以下错误。

我对插入列表进行了一些研究，并且很少有关于插入所有查询的示例，我认为这不是正确的方法。

我正在使用 Insert with for every ，但无法让它工作。如果有人能指出它为什么不起作用以及正确的方法是什么，我将非常感激。下面是我的代码和堆栈跟踪:

1) 域对象:-

public class TestName {
  String first;
  String last;

  public String getFirst() {
    return first;
  }

  public void setFirst(String first) {
    this.first = first;
  }

  public String getLast() {
    return last;
  }

  public void setLast(String last) {
    this.last = last;
  }
}

2) 调用 myBatis 的方法:-

 public void insertSplyDmndList(List<SplyDmndDo> list) throws DppException {
    String methodName = "insertSplyDmndList";
    LogHelper.begin(logger, methodName);

    try {
      if (list.size() > 0) {

        List<TestName> testNameList = new ArrayList<TestName>();
        for (int i = 0; i < list.size(); i++) {
          TestName testName = new TestName();
          testName.setFirst("First");
          testName.setLast("Last");
          testNameList.add(testName);
        }

        System.out.println("SIZE Test Name" + testNameList.size());

        session.insert("com.pepsico.dpp.webservices.rest.drpload.DrpLoadMapper.insertName",
            testNameList);


      }
    } catch (Exception e) {
      throw new DppException(e);
    }
    LogHelper.end(logger, methodName);
  }

3) Mybatis 映射器:-

<insert id="insertName" parameterType="java.util.List">
  <foreach item="name" collection="list"  separator=",">
  INSERT INTO NAME (LAST, First) VALUES ( #{name.last}, #{name.first} )</foreach> 
  </insert>

4) Pom.xml 的依赖:-

<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.8</version>
        </dependency>

5) 堆栈跟踪:-

### The error may involve com.pepsico.dpp.webservices.rest.drpload.DrpLoadMapper.insertName-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? ) ,        INSERT INTO NAME (LAST, First) VALUES ( ?, ? )         
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
    ... 43 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Answer 1

对于 Oracle，INSERT ALL 语法是使用单个语句插入多行的正确方法，尽管它看起来与其他 RDBMS 中的多行插入不同。
要使用 MyBatis 构造 INSERT ALL 语句，请尝试以下操作:

<insert id="insertName" parameterType="java.util.List">
INSERT ALL
  <foreach item="name" collection="list"  separator=" ">
    INTO NAME (LAST, First) VALUES ( #{name.last}, #{name.first} )
  </foreach> 
SELECT * FROM DUAL;
</insert>

如果您运行的是旧版本的 Oracle，另一种选择是使用带有“UNION'd”的 INSERT INTO 语法 SELECT语句，可在 this answer 中找到，你可以尝试这样做:

<insert id="insertName" parameterType="java.util.List">
INSERT INTO NAME (LAST, First)
  <foreach item="name" collection="list"  separator=" UNION ALL ">
    SELECT #{name.last}, #{name.first} FROM DUAL
  </foreach> 
</insert>

正如 @pf_miles 所指出的，如果您选择循环遍历 Java 中的名称列表，请务必在打开 MyBatis session 时使用 ExecutorType.BATCH。

[免责声明:我还没有尝试过上述任何代码片段，因此请将它们视为改进的起点]