Mybatis批量更新

具体可参考：https://www.cnblogs.com/eternityz/p/12284760.html

https://blog.csdn.net/yjaspire/article/details/81316885?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165242231316782391842147%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165242231316782391842147&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-2-81316885-null-null.142^v9^pc_search_result_cache,157^v4^control&utm_term=mybatis%E6%89%B9%E9%87%8F%E6%9B%B4%E6%96%B0&spm=1018.2226.3001.4187

  <update id="batchUpdateByPrimaryKey"
            parameterType="java.util.List">
        update device_manage_info
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="dmi_app_version = case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.dmiAppVersion!= null and item.dmiAppVersion!='' ">
                        when dmi_id = #{item.dmiId,jdbcType=BIGINT}
                        then #{item.dmiAppVersion,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="dmi_app_sys =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.dmiAppSys!= null and item.dmiAppSys!='' ">
                        when dmi_id = #{item.dmiId,jdbcType=BIGINT}
                        then #{item.dmiAppSys,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="dmi_member_status =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.dmiMemberStatus!= null">
                        when dmi_id = #{item.dmiId,jdbcType=BIGINT}
                        then #{item.dmiMemberStatus,jdbcType=INTEGER}
                    </if>
                </foreach>
            </trim>
        </trim>
        where dmi_id in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            #{item.dmiId,jdbcType=BIGINT}
        </foreach>

<!--        where-->
<!--        <foreach collection="list" separator="or" item="item" index="index">-->
<!--            dmi_id=#{item.dmiId}-->
<!--        </foreach>-->
    </update>

一、<trim prefix="dmi_app_version = case" suffix="end,"> 

prefix：放入的是需要更新的字段

二、collection="list" 

list需要与dao接口参数名一致

 三、

<if test="item.dmiAppVersion!= null and item.dmiAppVersion!='' ">

when dmi_id = #{item.dmiId,jdbcType=BIGINT}

then #{item.dmiAppVersion,jdbcType=VARCHAR}

</if>

when后面dmi_id这个是数据库字段，#{item.dmiId,jdbcType=BIGINT}必须是item.字段这种形式

then后面是需要更新的字段。

这句话的意思是，更新id在集合中的数据的dmiAppVersion字段。

注意：整型不需要：and item.dmiAppVersion!=''

四、如果有过个需要更新的条件可以使用多个

 <trim prefix="dmi_member_status =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.dmiMemberStatus!= null">
                        when dmi_id = #{item.dmiId,jdbcType=BIGINT}
                        then #{item.dmiMemberStatus,jdbcType=INTEGER}
                    </if>
                </foreach>
            </trim>

五、需要注意最后的循环

      where dmi_id in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            #{item.dmiId,jdbcType=BIGINT}
        </foreach>

<!--        where-->
<!--        <foreach collection="list" separator="or" item="item" index="index">-->
<!--            dmi_id=#{item.dmiId}-->
<!--        </foreach>-->


不能够dmi_id= #{item.dmiId,jdbcType=BIGINT}，否则不会更新数据，也不会报错（踩坑）

这里面有两种形式循环，但是结果都一样

结果如下：

UPDATE device_manage_info 
					SET dmi_app_version =
				CASE
						
						WHEN dmi_id = ? THEN
						? 
						WHEN dmi_id = ? THEN
						? 
					END,
					dmi_app_sys =
				CASE
						
						WHEN dmi_id = ? THEN
						? 
						WHEN dmi_id = ? THEN
						? 
					END,
					dmi_member_status =
				CASE
						
						WHEN dmi_id = ? THEN
						? 
						WHEN dmi_id = ? THEN
						? 
					END 
					WHERE
					dmi_id =? 
OR dmi_id =?