Mybatis Query to insert a list of java objects is failing

Question

I'm trying to insert a list of objects using Mybatis and getting the below error.

I did some research on inserting a list and few examples were for Insert ALL queries, which I think is not the right approach.

I'm using Insert with for each and cannot get it to work. I will really appreciate it if someone can point out why it is not working and what is correct way.Below is my code and stack Trace:

1) Domain Object:-

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) Method which makes call to 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 mapper:-

<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's dependency :-

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

5) Stack Trace :-

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

it's more efficient to insert multiple value in the format:

INSERT INTO NAME (LAST , FIRST)
VALUES (
    (L1 , F1),
    (L2 , F2),
    (L3 , F3),
    ..
)

Not sure about the exact format of this XML code, but should be like this:

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

Answer 2

One Hints

In Mybatis, When ORA 00933 ERROR occurs then there are several reason for this.

Among them one is due to semicolon(;) related.

You need to remove semicolon from end of query.

Answer 3

For Oracle, the INSERT ALL syntax is the correct approach to insert multiple rows with a single statement, even though it looks different than the multi-row inserts in other RDBMSs.
To construct an INSERT ALL statement with MyBatis, give the following a try:

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

If you're running an older version of Oracle, an alternative would be the INSERT INTO syntax with "UNION'd" SELECT statements, found in this answer, which you could try with this:

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

As @pf_miles pointed out, if you're opting to loop over the list of names from Java, be sure to use the ExecutorType.BATCH when opening your MyBatis session.

[Disclaimer: I haven't tried any of the above code snippets, so please regard them as a starting point for refinement]

Answer 4

Note that :

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

is definitely an illegal sql statement.

It looks like that you want to insert every element of the list into separate lines of table 'NAME'.
You could write one insert statement with a single list element as its parameter, something like this:

<insert id="insertName" parameterType="your list element type">
    INSERT INTO NAME (LAST, First) VALUES ( #{last}, #{first} )
</insert>

and loop-over the list in java code, do all the inserts in a batch operation.