SqlServer批量插入数据时最多不能超过2100条，记录一下解决办法
Java代码：
public void batchInsert(List<Student> list) {
Integer BATCH_SIZE = 100;
if (CollectionUtils.isNotEmpty(list)) {
int insertListSize = list.size();
int pageNum = insertListSize % BATCH_SIZE == 0 ? insertListSize / BATCH_SIZE : (insertListSize / BATCH_SIZE + 1);
if (pageNum > 0) {
for (int i = 1; i <= pageNum; i++) {
studentMapper.batchInsert(list.stream().skip(BATCH_SIZE * (i - 1)).limit(BATCH_SIZE).collect(Collectors.toList()));
}
}
}
}
mapper.xml中的sql语句：
<insert id="batchInsert" parameterType="java.util.List">
insert into student
(
id,
name,
del_flag,
create_time, create_by
)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id},
#{item.name},
#{item.delFlag},
#{item.createTime},#{item.createBy}
)
</foreach>
</insert>
顺便记录一下批量更新，与批量插入基本一致
Java代码：
public void batchUpdate(List<Student> list) {
final int BATCH_UPDATE_SIZE = 80;
if (CollectionUtils.isNotEmpty(list)) {
int updateListSize = list.size();
int pageNum = updateListSize % BATCH_UPDATE_SIZE == 0 ? updateListSize / BATCH_UPDATE_SIZE : (updateListSize / BATCH_UPDATE_SIZE + 1);
if (pageNum > 0) {
for (int i = 1; i <= pageNum; i++) {
studentMapper.batchUpdate(list.stream().skip(BATCH_UPDATE_SIZE * (i - 1)).limit(BATCH_UPDATE_SIZE).collect(Collectors.toList()));
}
}
}
}
mapper.xml中的sql语句：
<update id="batchUpdate">
update student
<trim prefix="set" suffixOverrides=",">
<trim prefix=" name = case" suffix=" end, ">
<foreach collection="updateList" item="item" index="index">
<if test="item.name != null">
when id = #{item.id} then #{item.name}
</if>
</foreach>
</trim>
<trim prefix=" del_flag = case" suffix=" end, ">
<foreach collection="updateList" item="item" index="index">
<if test="item.delFlag != null">
when id = #{item.id} then #{item.delFlag}
</if>
</foreach>
</trim>
<trim prefix=" update_time = case" suffix=" end, ">
<foreach collection="updateList" item="item" index="index">
<if test="item.updateTime != null">
when id = #{item.id} then #{item.updateTime}
</if>
</foreach>
</trim>
<trim prefix=" update_by = case" suffix=" end, ">
<foreach collection="updateList" item="item" index="index">
<if test="item.updateBy != null">
when id = #{item.id} then #{item.updateBy}
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="updateList" index="index" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
