合理的使用批量插入、更新对性能优化有很大的作用,速度明显快了N倍。
要注意数据库连接串后面要新增:&allowMultiQueries=true,表示一个sql可以通过分号分割为多个独立sql。
批量插入的最大限制主要是看你整条sql的长度大小,所以可以根据自身sql长度的大小来配置这个要分批的每批的个数。
批量插入
Dao
1
2
3
|
public Integer saveStudents(List<Student> students) {
return super .getSqlSession().insert( "StudentMapper.insertStudents" , students);
}
|
Mapper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
< insert id = "insertStudents" parameterType = "java.util.List" >
insert into t_student(
city_id
)
values
< foreach collection = "list" item = "itm" index = "index" separator = "," >
(
#{itm.city_id}
)
</ foreach >
</ insert >
|
批量更新
Dao
1
2
3
|
public Integer updateStudents(List<Student> students) {
return super .getSqlSession().update( "StudentMapper.updateStudents" , students);
} |
Mapper
1
2
3
4
5
6
7
8
9
10
11
12
13
|
< update id = "updateStudents" parameterType = "java.util.List" >
< foreach collection = "list" item = "item" index = "index" open = ""
close = "" separator = ";" >
UPDATE t_studnet
< set >
< if test = "item.name != null" >
name= #{item.name}
</ if >
</ set >
WHERE id = #{item.id}
AND age = #{item.age}
</ foreach >
</ update >
|
List分批的工具类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/** * 分批list
*
* @param sourceList
* 要分批的list
* @param batchCount
* 每批list的个数
* @return List<List<Object>>
*/
public static List<List<?>> batchList(List<?> sourceList, int batchCount) {
List<List<?>> returnList = new ArrayList<>();
int startIndex = 0 ; // 从第0个下标开始
while (startIndex < sourceList.size()) {
int endIndex = 0 ;
if (sourceList.size() - batchCount < startIndex) {
endIndex = sourceList.size();
} else {
endIndex = startIndex + batchCount;
}
returnList.add(sourceList.subList(startIndex, endIndex));
startIndex = startIndex + batchCount; // 下一批
}
return returnList;
} |