To do a bulk INSERT database, you first need to know the syntax that the database supports for bulk INSERT. The syntax for bulk inserts for each database is different, and I introduce two.
Mysql:1, INSERT into table_name (id,name) VALUES (1, ' Zhang San '), (2, ' John Doe ')
2. INSERT INTO table_name (ID,NAME) VALUES (1, ' Zhang San ') and insert INTO table_name (ID,NAME) VALUES (2, ' John Doe ')
Oracle:1, INSERT into table_name (id,name) (select 1, "Zhang San ' from dual") union All (select 2, ' John Doe ' from dual)
2. INSERT INTO table_name (ID,NAME) VALUES (1, ' Zhang San ') and insert INTO table_name (ID,NAME) VALUES (2, ' John Doe ')
4 SQL is a database-supported notation. The second type of the two is not recommended, because both efficiency and readability are far worse than the first
Of course, more importantly, MyBatis does not support the second notation because a transaction in MyBatis only supports one SQL
So, MyBatis can only take the first of the two, the code is as follows
Mysql
<select id= "BatchSave" parametertype= "Java.util.List" >
INSERT into table_name (id,name) VALUES
<foreach collection= "list" item= "ITM" separator= "," >
(#{itm.id},#{itm.name})
</foreach>
</select>
ORACLE:
<select id= "BatchSave" parametertype= "Java.util.List" >
INSERT into table_name (ID,NAME)
<foreach collection= "list" item= "ITM" separator= "union All" >
(SELECT #{itm.id},#{itm.name} from DUAL)
</foreach>
</select>
Here to pay attention, the label must be <select>, not <INSERT> or <UPDATE>, or you will get an error.
Don't ask elder brother why will error, elder brother also don't know, hahaha haha ha ha haha ... (Too lazy, do not want to check the information, interested can go to check)
"Go" mybatis mybatis BULK Insert Oracle, MySQL