由于系统中一次性需要导入1000条数据,为防止超时以及长时间占用数据库连接,采取了批量更新插入。
1.后台代码每50条,与数据库进行一次交互
//批量插入/更新
int dataLimitNum = 50;// 限制分批条数
int i = 0;
while (i < list.size()) {
int from = i;
int to = i + dataLimitNum;
if (to >= list.size()) {
to = list.size();
}
List<User> partList = list.subList(from, to);
if (partList.isEmpty()) {
break;
}
i = to;
userDao.insertSelectiveBatch(partList);
}
2.批量插入
<insert id="insertSelectiveBatch" parameterType="net.su.User">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
insert into User
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.id != null">
ID,
</if>
<if test="item.userName!= null">
USER_NAME,
</if>
<if test="item.age!= null">
AGE,
</if>
<if test="item.mobile!= null">
Mobile,
</if>
<if test="item.created != null">
CREATED,
</if>
<if test="item.createBy != null">
CREATE_BY,
</if>
<if test="item.updated != null">
UPDATED,
</if>
<if test="item.updateBy != null">
UPDATE_BY,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.id != null">
#{item.id,jdbcType=BIGINT},
</if>
<if test="item.userName!= null">
#{item.userName,jdbcType=VARCHAR},
</if>
<if test="item.age!= null">
#{item.age,jdbcType=BIGINT},
</if>
<if test="item.mobile!= null">
#{item.mobile,jdbcType=VARCHAR},
</if>
<if test="item.created != null">
#{item.created,jdbcType=TIMESTAMP},
</if>
<if test="item.createBy != null">
#{item.createBy,jdbcType=VARCHAR},
</if>
<if test="item.updated != null">
#{item.updated,jdbcType=TIMESTAMP},
</if>
<if test="item.updateBy != null">
#{item.updateBy,jdbcType=VARCHAR},
</if>
</trim>
</foreach>
</insert>
3.批量更新
<update id="updateByPrimaryKeyBatch" parameterType="net.su.User">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update USER
<set>
<if test="item.id!= null">
ID= #{item.id,jdbcType=BIGINT},
</if>
<if test="item.userName!= null">
UserName= #{item.userName,jdbcType=VARCHAR},
</if>
<if test="item.age!= null">
AGE= #{item.age,jdbcType=BIGINT},
</if>
<if test="item.mobile!= null">
Mobile= #{item.mobile,jdbcType=VARCHAR},
</if>
<if test="item.created != null">
CREATED = #{item.created,jdbcType=TIMESTAMP},
</if>
<if test="item.createBy != null">
CREATE_BY = #{item.createBy,jdbcType=VARCHAR},
</if>
<if test="item.updated != null">
UPDATED = #{item.updated,jdbcType=TIMESTAMP},
</if>
<if test="item.updatedBy != null">
UPDATED_BY = #{item.updatedBy,jdbcType=VARCHAR},
</if>
</set>
where ID = #{item.id,jdbcType=BIGINT}
</foreach>
</update>