Because the project needs to generate multiple data and save it to the database, a list collection object is encapsulated in the program, then the entities in the collection need to be inserted into the database, the project uses Spring+mybatis, so it is intended to use MyBatis BULK INSERT, Should be better than the effect of the loop insert, because previously did not use BULK INSERT, after finding some information on the net finally realized, the detailed process to paste out. For later viewing and learning. Java code: NOTE: In this loop, new objects need to be created, instead of changing the value of the property through the loop to think that this is a new object, popularly said that the new Reddemcode () should be placed in the for loop inside.
Xxxmapper.xml
1 <!--BULK INSERT generated redemption codes-2 <insert id = "Insertcodebatch" parametertype= "Java.util.List" > 3 < Selectkey Resulttype = "Java.lang.Integer" keyproperty= "id" 4 order= "after" > 5 SELECT last_insert_id () 6 </selectkey > 7 INSERT INTO Redeem_code 8 (bach_id, code, type, Facevalue,create_user,create_time) 9 VALUES10 <foreach collection = "List" item= "Reddemcode" index= "index" separator = "," >11 ( # {Reddemcode.batchid}, #{reddemcode.code},13 #{reddemcode.type},14 #{reddemcode.facevalue},15 #{ Reddemcode.createuser}, #{reddemcode.createtime}16 ) </foreach >18 </insert >
Xxxmapper.java (part)
1 int insertcodebatch (List<reddemcode > Reddemcodelist);
The explanation of the foreach label refers to the information on the Internet, as follows:
The main use of foreach is in the build in condition, which can iterate a collection in an SQL statement.
The properties of the Foreach element are mainly item,index,collection,open,separator,close.
The item represents the alias of each element in the collection when it iterates, and index specifies a name that represents the position at which each iteration occurs during the iteration, and open indicates what the statement begins with, and separator indicates what symbol is used as the delimiter between each iteration. Close means the end, the most critical and error-prone when using foreach is the collection property, which must be specified, but in different cases the value of the property is not the same, there are 3 main cases:
1. If a single parameter is passed in and the parameter type is a list, the collection property value is List
2. If a single parameter is passed in and the parameter type is an array, the value of the collection property is array
3. If the parameters passed in are multiple, we need to encapsulate them into a map, of course, the single parameter can also be encapsulated into a map
SQL statements that are executed using BULK INSERT should be equivalent to:
Insert into Redeem_code (batch_id, code, type, Facevalue,create_user,create_time) VALUES (?,?,?,?,?,?), (?,?,?,?,?,?), ( ?,?,?,?,?,? ),(?,?,?,?,?,? )
MyBatis BULK Insert data (MYSQL)