1、组合字段批量查询
<select id="batchSelect" resultMap="BaseResultMap">
select
<include refid="BaseColumnList"/>
from good
where mark = 1 and (shop_id,sku_id) in
<foreach collection="list" item="item" open="(" separator="," close=")">
(#{item.shopId},#{item.skuId})
</foreach>
</select>
2、根据id批量更新
<update id="batchUpdate" parameterType="java.util.List">
update good
<trim prefix="set" suffixOverrides=",">
<trim prefix="sku_id =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.skuId!=null">
when id=#{item.id} then #{item.skuId}
</if>
<if test="item.skuId == null">
when id=#{item.id} then good.sku_id
</if>
</foreach>
</trim>
<trim prefix="shop_id =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.shopId!=null">
when id=#{item.id} then #{item.shopId}
</if>
<if test="item.shopId == null">
when id=#{item.id} then good.shop_id
</if>
</foreach>
</trim>
<trim prefix="shop_rate =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.shopRate!=null">
when id=#{item.id} then #{item.shopRate}
</if>
<if test="item.shopRate == null">
when id=#{item.id} then good.shop_rate
</if>
</foreach>
</trim>
<trim prefix="technician_rate =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.technicianRate!=null">
when id=#{item.id} then #{item.technicianRate}
</if>
<if test="item.technicianRate == null">
when id=#{item.id} then good.technician_rate
</if>
</foreach>
</trim>
<trim prefix="update_user_id =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.updateUserId!=null">
when id=#{item.id} then #{item.updateUserId}
</if>
<if test="item.updateUserId == null">
when id=#{item.id} then good.update_user_id
</if>
</foreach>
</trim>
<trim prefix="update_user_name =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.updateUserName!=null">
when id=#{item.id} then #{item.updateUserName}
</if>
<if test="item.updateUserName == null">
when id=#{item.id} then good.update_user_name
</if>
</foreach>
</trim>
<trim prefix="update_time =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.updateTime!=null">
when id=#{item.id} then #{item.updateTime}
</if>
<if test="item.updateTime == null">
when id=#{item.id} then good.update_time
</if>
</foreach>
</trim>
</trim>
where mark = 1
and id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
3、批量插入数据
<insert id="insertBatchSomeColumn" parameterType="java.util.List">
insert into good(
sku_id,
shop_id,
shop_rate,
technician_rate,
type,
update_user_id,
update_user_name,
create_time,
update_time
)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.skuId},
#{item.shopId},
#{item.shopRate},
#{item.technicianRate},
#{item.type},
#{item.updateUserId},
#{item.updateUserName},
now(),
now()
)
</foreach>
</insert>