mybatis动态sql中批量指定更新的使用

mybatis动态sql中批量指定更新的使用

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    /**
    * 批量更新
    * 根据id更新字段
    */
    void updateBatch(List<Student> list);
}

对应的xml

<update id="updateBatch">
    UPDATE STUDENT
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="NAME = CASE" suffix="END,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name!=null and item.name!=''">
                    when ID=#{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="SEX = CASE" suffix="END,">
            <foreach collection="list" item="item" index="index">
                <if test="item.sex!=null and item.sex!=''">
                    when ID=#{item.id} then #{item.sex}
                </if>
            </foreach>
        </trim>
        <trim prefix="AGE = CASE" suffix="END,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age!=null">
                    when ID=#{item.id} then  #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    WHERE
    <foreach collection="list" item="item" index="index" separator="or">
        ID=#{item.id}
    </foreach>
</update>

对应测试

@Test
public void updateBatch() {
    List<Student> list = new ArrayList<Student>();
    list.add(new Student(1,"小明","男",18));
    list.add(new Student(2,"小华","女",17));
    list.add(new Student(3,"小红","女",17));
    studentMapper.updateBatch(list);
}

生成对应sql

UPDATE STUDENT
SET 
NAME = CASE 
WHEN ID  = 1 THEN '小明'
WHEN ID  = 2 THEN '小华'
WHEN ID  = 3 THEN '小红'
END,
SEX = CASE 
WHEN ID  = 1 THEN '男'
WHEN ID  = 2 THEN '女'
WHEN ID  = 3 THEN '女'
END,
AGE = CASE 
WHEN ID  = 1 THEN 18
WHEN ID  = 2 THEN 17
WHEN ID  = 3 THEN 17
END
WHERE 
ID = 1 OR ID = 2 OR ID =3