最近遇到一个需求,会频繁的去更新数据库。于是做了优化处理,把多单次请求修改成一次批量插入或者更新。可以参考下语法。
批量插入:
<insert id="batchInsertTrackLog" >
insert into track_log(businessNum,trackingNumber,`status`,visit_trackdog_count,createdTime,carrier) values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.businessNum},#{item.trackingNumber},#{item.status},#{item.visitTrackdogCount},now(),#{item.carrier})
</foreach>
</insert>
批量更新:
<update id="batchUpdateTrackLog" parameterType="java.util.List">
UPDATE track_log
<trim prefix="set" suffixOverrides=",">
<trim prefix="status=case" suffix="end,">
<foreach collection="list" item="item" index="index" >
when trackingNumber = #{item.trackingNumber} then #{item.status}
</foreach>
</trim>
<trim prefix="visit_trackdog_count=case" suffix="end,">
<foreach collection="list" item="item" index="index" >
when trackingNumber = #{item.trackingNumber} then #{item.visitTrackdogCount}
</foreach>
</trim>
</trim>
<where>
trackingNumber in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.trackingNumber}
</foreach>
</where>
</update>