1.知道多个ID 并且多个对象,转化成sql语句为
UPDATE table1 SET name = CASE id
WHEN 1 THEN 'new name1'
WHEN 2 THEN 'new name2'
WHEN 3 THEN 'new name3'
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
mybatis xml文件表达式为
<!-- 批量更新数据 -->
<update id="updateBatch">
update table1 set
name =
<foreach collection="list" item="item" index="index"
separator=" " open="case id" close="end">
when #{item.id} then
#{item.name}
</foreach>,
title =
<foreach collection="list" item="item" index="index"
separator=" " open="case id" close="end">
when #{item.id} then
#{item.title }
</foreach>
where id in
<foreach collection="list" item="item" index="index"
separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
当更新关系表的时候,并且过滤条件有两个,其中一个(roleId)是不变的
UPDATE role_right_rela,
(
SELECT
9999 RIGHT_ID ,1 is_enable
FROM
DUAL
UNION ALL
SELECT
99998 RIGHT_ID ,0 is_enable
FROM
DUAL
) tmp
SET role_right_rela.is_enable = tmp.is_enable
WHERE
role_right_rela.ROLE_ID = 1
AND role_right_rela.right_id = tmp.RIGHT_ID
mybatis xml文件的书写
<update id="updateRoleRightRealByRoleAndRightId">
UPDATE role_right_rela,
<foreach collection="rightDtos" close=")" open="(" separator=" UNION ALL " item="vo" >
SELECT #{vo.id} RIGHT_ID,#{vo.status} is_enable FROM DUAL
</foreach>
tmp
SET role_right_rela.is_enable = tmp.is_enable
WHERE
role_right_rela.ROLE_ID = #{roleId}
AND role_right_rela.right_id = tmp.RIGHT_ID
</update>