MyBatis 批量更新小结

原来的代码

mapper.xml

    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update student set name = #{item.name}
            <where>
                <if test="item.id != null and item.id != ''">
                    id = #{item.id}
                </if>
            </where>
        </foreach>
    </update>

实际执行的sql

  update student set name = 'zhangsan' where id =  1;
  update student set name = 'lisi' where id =  2;
  update student set name = 'wangwu' where id =  3;

用时

大于 30s。

优化的写法

mappper.xml

 <update id="batchUpdate" >
        update student set
        <trim prefix="name = case" suffix="end">
            <foreach collection="list" item="item">
                when id =#{item.id} then #{item.}
            </foreach>
        </trim>
        <where>
            id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item.id}
            </foreach>
        </where>
    </update>

实际执行的sql

UPDATE student  SET `name` =
	CASE
			
			WHEN id = 1 THEN
			'zhangsan' 
			WHEN id = 2 THEN
			'lisi' 
			WHEN id = 3 THEN
			'wangwu' 
		END 
		WHERE
			id IN (1,2,3);

用时

2s。