Insert a list of objects using MyBatis 3

Question

I've tried to insert a list in a database but I've got some the error: org.springframework.jdbc.BadSqlGrammarException: SqlSession operation; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00913: too many values (...).

The code that I've used:

<insert id="insertListMyObject" parameterType="java.util.List" >
INSERT INTO my_table
   (ID_ITEM,
    ATT1,
    ATT2)
    VALUES
   <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
    #{item.idItem, jdbcType=BIGINT},
    #{item.att1, jdbcType=INTEGER},
    #{item.att2, jdbcType=STRING}
       </foreach>   
</insert>

My dao cals the method:

SqlSessionTemplate().insert(MAPPER+".insertListMyObject", parameterList);

Where the parameterList is:

List<MyObjects>.

Does someone have a clue about what's this error? Or if does exists a better way to do multiples inserts operation.

Many thanks!

Solution 1

Set the separator as given below

separator="),("

Solution 2

by using following query you may insert multiple records at a time using Mybatis and Oracle.

<insert id="insertListMyObject" parameterType="map" >
BEGIN
                            insert into table_name values (11,11);
                            insert into table_name2 values (11,112);
            END;
</insert>

this is how i did for oracle and it works. Note that parameterType=map is not necessary a map it can be anything according to your needs.

Solution 3

config log4j to mybatis ,you can find the bugs.

trying

<insert id="insertListMyObject" parameterType="java.util.List" >
INSERT INTO my_table
   (ID_ITEM,
    ATT1,
    ATT2)
    VALUES
   <foreach collection="list" item="item" index="index"  separator=",">
    (#{item.idItem, jdbcType=BIGINT},
    #{item.att1, jdbcType=INTEGER},
    #{item.att2, jdbcType=STRING})
       </foreach>   
</insert>

Comments

[aloplop85]

In my case, I trust in useGeneratedKeys="true" to create automatic IDs for the items in the foreach. Is it possible to retrieve these new IDs?