Insert a list of objects using MyBatis 3

Question

I tried to insert the list into the database, but I have an error: org.springframework.jdbc.BadSqlGrammarException: SqlSession operation; bad SQL grammar []; The nested exception is java.sql.SQLException: ORA-00913: too many values ​​(...).

The code I 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 method:

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

Where is the parameter List:

 List<MyObjects>. 

Does anyone know what this error is? Or, if there is a better way to do operations with multiple inserts.

Thank you very much!

Answer 1

Set the separator as shown below.

 separator="),(" 

Answer 2

config log4j to mybatis, you may find errors.

trying to

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

Answer 3

using the following query, you can insert multiple records at once 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. Please note that parameterType=map not necessarily a map, it can be anything according to your needs.

Answer 4

I wonder if you can do this with the oracle INSERT statement . INSERT with VALUES (different from the one in the subquery) allows only values ​​for one row!

To do this, try inserting the package. An example of MyBatis can be found here .

Answer 5

something like this at your DAO level might help

 public class MyBatisDao { private SqlSessionFactory mSqlSessionFactory; public void insertUsers(List<User> users) { SqlSession session = mSqlSessionFactory.openSession(ExecutorType.BATCH); try { for(User user:users) { session.insert("com.you.insertUsers",user); } }catch(Exception e) { } finally { session.close(); } } 

}

Answer 6

I suggest you use the package, it is much better and it is standard. This query will not work in some databases (e.g. Oracle).