int batchInsertOrUpdate(@Param("list") List<AuntRestInfo> list);
<insert id="batchInsertOrUpdate" parameterType="map">
<!--@mbg.generated generated on Tue Oct 20 10:53:28 CST 2020.-->
insert into aunt_rest_info
(rest_id, aunt_id, rest_day, rest1_start_time, rest1_end_time, rest2_start_time,
rest2_end_time, creater, modifier, yn)
values
<foreach collection="list" item="item" separator=",">
(#{item.restId,jdbcType=BIGINT}, #{item.auntId,jdbcType=BIGINT}, #{item.restDay,jdbcType=TINYINT},
#{item.rest1StartTime,jdbcType=TIME}, #{item.rest1EndTime,jdbcType=TIME}, #{item.rest2StartTime,jdbcType=TIME},
#{item.rest2EndTime,jdbcType=TIME}, #{item.creater,jdbcType=VARCHAR}, #{item.modifier,jdbcType=VARCHAR}, #{item.yn,jdbcType=TINYINT}
)
</foreach>
ON DUPLICATE KEY UPDATE
rest1_start_time = values(rest1_start_time),
rest1_end_time = values(rest1_end_time),
rest2_start_time = values(rest2_start_time),
rest2_end_time = values(rest2_end_time),
modifier = values(modifier),
yn = values(yn)
</insert>
MyBatils批量更新
<update id="batchUpdate" parameterType="java.util.List">
update time_stock
<trim prefix="set" suffixOverrides=",">
<trim prefix="time_stock_type =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.timeStockType != null">
when time_stock_id = #{item.timeStockId} then #{item.timeStockType}
</if>
<if test="item.timeStockType == null">
when time_stock_id = #{item.timeStockId} then time_stock.time_stock_type
</if>
</foreach>
</trim>
<trim prefix="time_stock_status =case" suffix="end,">
<foreach collection="list" item="item">
<if test="item.timeStockStatus != null">
when time_stock_id = #{item.timeStockId} then #{item.timeStockStatus}
</if>
<if test="item.timeStockStatus == null">
when time_stock_id = #{item.timeStockId} then time_stock.time_stock_status
</if>
</foreach>
</trim>
</trim>
<where>
<foreach collection="list" separator="or" item="item">
time_stock_id = #{item.timeStockId}
and time_stock_status = #{item.preTimeStockStatus}
and time_stock_type = #{item.preTimeStockType}
</foreach>
</where>
</update>
<update id="batchUpdate" parameterType="java.util.List">
update ls_voucher_bind
<trim prefix="set" suffixOverrides=",">
<trim prefix="expired_status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.expiredStatus !=null">
when voucher_num=#{item.voucherNum} then #{item.expiredStatus}
</if>
</foreach>
</trim>
<trim prefix="verified_status=case" suffix="end">
<foreach collection="list" item="item" index="index">
<if test="item.verifiedStatus != null">
when voucher_num=#{item.voucherNum} then #{item.verfiedStatus}
</if>
</foreach>
</trim>
<trim prefix="invalid_status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.invalidStatus != null" >
when voucher_num=#{item.voucherNum} then #{item.invalidStatus}
</if>
</foreach>
</trim>
</trim>
where voucher_num in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.voucherNum,jdbcType=BIGINT}
</foreach>
</update>
知识点:
trim:具有动态拼接SQL的能力
trim具有四个属性:
prefix:表示在trim包裹的SQL前添加指定内容
suffix:表示在trim包裹的SQL末尾添加内容
prefixOverrides:表示去掉(覆盖)trim包裹的SQL的指定首部内容
suffixOverrides:表示去掉(覆盖)trim包裹的SQL的指定尾部内容