mybatis xml 多条件多字段批量更新
1. dao 层:
void commUpdateBatchData(@Param("tableName") String tableName,
@Param("mapList") List<Map<String, Object>> mapList,
@Param("cols") List<String> cols,
@Param("mof") String mof,
@Param("fiscal") String fiscal);
2. mapper层
2.1. 方式1:
<update id="commUpdateBatchData">
update ${tableName}
<trim prefix="set" suffixOverrides=",">
<foreach collection="cols" item="key">
${key} = case guid
<foreach collection="mapList" item="item" separator=" " open=" " close="end,">
when #{item.guid} then #{item.${key}}
</foreach>
</foreach>
</trim>
where guid in
<foreach collection="mapList" index="index" item="item" separator="," open="(" close=")">
#{item.guid}
</foreach>
<if test="mof!= null and mof!= '' ">
and mof = #{mof}
</if>
<if test="fiscal != null and fiscal!= '' ">
and fiscal= #{fiscal}
</if>
</update>
2.2. 方式2:
<update id="commUpdateBatchData">
merge into ${tableName} t
using (
<trim prefix="" suffixOverrides="union">
<foreach collection="mapList" item="item">
<foreach collection="cols" item="key" separator="," open=" select" close="from dual union">
#{item.${key}} as ${key}
</foreach>
</foreach>
</trim>
) s
on (t.guid = s.guid and t.mof = s.mofand t.fiscal = s.fiscal)
when matched then
update set
<foreach collection="cols" item="key" separator="," open=" " close=";">
<if test="key != 'guid'.toString() and key != 'mof'.toString() and key != 'fiscal'.toString()">
t.${key} = s.${key}
</if>
</foreach>
</update>