MyBatis BULK INSERT, batch update

The rational use of batch inserts and updates has a great effect on performance optimization, and the speed is obviously N times faster.

Pay attention to the new addition of the database connection string: & allowMultiQueries = true, which means that a SQL can be split into multiple independent SQLs by a semicolon.

The maximum limit of batch insertion is mainly based on the length of your entire SQL, so you can configure the number of each batch to be batched according to the size of your own SQL length.

Bulk insert

Dao

public Integer saveStudents (List <Student> students) {
Super.getSqlSession (). Insert ("StudentMapper.insertStudents", students);
}
Mapper
<insert ID = "insertStudents" parameterType = "java.util.List">
Insert_student (
City_id
)
Value
<Foreach collection = "list" item = "itm" index = "index" separator = ",">
(
# {Itm.city_id}
()
</ Foreach>
</ insert>

Bulk update

Dao
public Integer updateStudents (List <Student> students) {
Super.getSqlSession (). Update ("StudentMapper.updateStudents", students);
}
Mapper

<update ID = "updateStudents" parameterType = "java.util.List">
<Foreach collection = "list" itemite = "item" index = "index" open = ""
Close = "" separator = ";">
UPDATE t_studnet
<Set>
If <if test = "item.name! = Null"
Name = name # {item.name}
... if it is </ if>
... set </ set>
WHERE ID = # {item.id}
AND AND age = = # {item.age}
</ Foreach>
</ update>

List batched tools
/ **
* Batch list
*
@Param sourceList
* List to be batched
@Param @batchCount
* The number of lists in each batch
@Return List <List <Object >>
* /
public staticList <List <? >> batchList (List <?> sourceList, int batchCount) {
List <List <? >> returnList = newArrayList <> ();
Int startIndex = 0; // start from 0th subscript
... (startIndex, <sourceList.size ()) {
Int index = 0;
(SourceList.size ()-batchCount <startIndex) {
EndIndex = sourceList.size ();
... else {
EndIndex = startIndex + batchCount;
}}
ReturnList.add (sourceList.subList (startIndex, endIndex));
StartIndex = startIndex + batchCount; // next batch
}
Return list;
}