0

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
Unheilig
  • 16,196
  • 193
  • 68
  • 98
Anurag Narayan
  • 1
  • 1
  • 1

4 Answers4

1

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]

Community
  • 1
  • 1
fspinnenhirn
  • 1,784
  • 1
  • 13
  • 27
  • Great, Union ALL is working for me.Thanks "greenSocksRock" and "pf_miles".And yes, I used ExecutorType.BATCH as my mentioned workaround for this issue and if you have opinion that batch inserting will be more efficient than this option, I will go for that as going forward when I will be inserting 1000s of rows I would not want to have a architectural decision which was not load efficient. – Anurag Narayan Apr 10 '15 at 20:20
  • if you're planning on inserting 1000s of rows in one shot, make sure to check the **maximum sql query statement size** for the database you're using! – fspinnenhirn Apr 10 '15 at 21:34
0

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.

pf_miles
  • 907
  • 1
  • 8
  • 17
  • Thanks. That's what I did as a workaround, but I don't like the idea of iterating in java and making multiple insert calls to myBatis. I'm not sure if myBatis batch session hits database only at commit or flush or if its going to make multiple calls to database? which I don't want. Other thought was that myBatis would have the feature to have me pass a list and it will take care of insert in the best way;which I was trying to do in the example. Trying to use myBatis in maximum. – Anurag Narayan Apr 09 '15 at 18:37
  • Or you may want to follow the answer in this post : http://stackoverflow.com/questions/3380933/how-do-i-insert-collection-of-objects-using-mybatis-3-x – pf_miles Apr 10 '15 at 05:22
  • unfortunately the SQL syntax within the solution you [linked to](http://stackoverflow.com/questions/3380933/how-do-i-insert-collection-of-objects-using-mybatis-3-x) actually doesn't work with Oracle, which is what OP originally was trying to do (albeit incorrectly) – fspinnenhirn Apr 10 '15 at 21:42
0

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.

Md. Naushad Alam
  • 8,011
  • 6
  • 25
  • 23
0

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>
nabeel
  • 319
  • 1
  • 9