mybatis 批量更新多个字段数据

1. MySQL

1.1 mapper.xml

<update id="updateBatch">
    UPDATE grid_patrol_condition
    <trim prefix="set" suffixOverrides=",">
      
      <trim prefix="check_item = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.checkItem}
        </foreach>
      </trim>
      <trim prefix="up_to_standard_flag = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.upToStandardFlag}
        </foreach>
      </trim>
      <trim prefix="dept_id = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.deptId}
        </foreach>
      </trim>
      <trim prefix="update_id = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.updateId}
        </foreach>
      </trim>
      <trim prefix="update_by = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.updateBy}
        </foreach>
      </trim>
      <trim prefix="update_time = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when id=#{item.id} then #{item.updateTime}
        </foreach>
      </trim>
    </trim>
    where id in
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
      #{item.id}
    </foreach>
  </update>

1.2 生成的sql语句

UPDATE grid_patrol_condition 
SET check_item =
CASE
		
		WHEN id = 1 THEN
		'居民房屋建筑是否兼顾，墙体外皮、外砖是否开裂松动' 
		WHEN id = 2 THEN
		'消防通道是否畅通' 
		WHEN id = 3 THEN
		'老旧房屋用水、用电、防火等情况' 
		WHEN id = 4 THEN
		'安全生产情况' 
		WHEN id = 5 THEN
		'居民房屋建筑是否兼顾，墙体外皮、外砖是否开裂松动' 
		WHEN id = 6 THEN
		'消防通道是否畅通' 
		WHEN id = 7 THEN
		'老旧房屋用水、用电、防火等情况' 
		WHEN id = 8 THEN
		'安全生产情况' 
		WHEN id = 9 THEN
		'居民房屋建筑是否兼顾，墙体外皮、外砖是否开裂松动' 
		WHEN id = 10 THEN
		'消防通道是否畅通' 
		WHEN id = 11 THEN
		'老旧房屋用水、用电、防火等情况' 
		WHEN id = 12 THEN
		'安全生产情况' 
		WHEN id = 13 THEN
		'居民房屋建筑是否兼顾，墙体外皮、外砖是否开裂松动' 
		WHEN id = 14 THEN
		'消防通道是否畅通' 
		WHEN id = 15 THEN
		'老旧房屋用水、用电、防火等情况' 
		WHEN id = 16 THEN
		'安全生产情况' 
	END,
	up_to_standard_flag =
CASE
		
		WHEN id = 1 THEN
		'1' 
		WHEN id = 2 THEN
		'1' 
		WHEN id = 3 THEN
		'2' 
		WHEN id = 4 THEN
		'2' 
		WHEN id = 5 THEN
		'2' 
		WHEN id = 6 THEN
		'1' 
		WHEN id = 7 THEN
		'2' 
		WHEN id = 8 THEN
		'1' 
		WHEN id = 9 THEN
		'0' 
		WHEN id = 10 THEN
		'0' 
		WHEN id = 11 THEN
		'0' 
		WHEN id = 12 THEN
		'0' 
		WHEN id = 13 THEN
		'0' 
		WHEN id = 14 THEN
		'0' 
		WHEN id = 15 THEN
		'0' 
		WHEN id = 16 THEN
		'0' 
	END,
	dept_id =
CASE
		
		WHEN id = 1 THEN
		103 
		WHEN id = 2 THEN
		103 
		WHEN id = 3 THEN
		103 
		WHEN id = 4 THEN
		103 
		WHEN id = 5 THEN
		103 
		WHEN id = 6 THEN
		103 
		WHEN id = 7 THEN
		103 
		WHEN id = 8 THEN
		103 
		WHEN id = 9 THEN
		103 
		WHEN id = 10 THEN
		103 
		WHEN id = 11 THEN
		103 
		WHEN id = 12 THEN
		103 
		WHEN id = 13 THEN
		103 
		WHEN id = 14 THEN
		103 
		WHEN id = 15 THEN
		103 
		WHEN id = 16 THEN
		103 
	END,
	del_flag =
CASE
		
		WHEN id = 1 THEN
		'0' 
		WHEN id = 2 THEN
		'0' 
		WHEN id = 3 THEN
		'0' 
		WHEN id = 4 THEN
		'0' 
		WHEN id = 5 THEN
		'0' 
		WHEN id = 6 THEN
		'0' 
		WHEN id = 7 THEN
		'0' 
		WHEN id = 8 THEN
		'0' 
		WHEN id = 9 THEN
		'0' 
		WHEN id = 10 THEN
		'0' 
		WHEN id = 11 THEN
		'0' 
		WHEN id = 12 THEN
		'0' 
		WHEN id = 13 THEN
		'0' 
		WHEN id = 14 THEN
		'0' 
		WHEN id = 15 THEN
		'0' 
		WHEN id = 16 THEN
		'0' 
	END,
	update_id =
CASE
		
		WHEN id = 1 THEN
	NULL 
		WHEN id = 2 THEN
	NULL 
		WHEN id = 3 THEN
	NULL 
		WHEN id = 4 THEN
	NULL 
		WHEN id = 5 THEN
	NULL 
		WHEN id = 6 THEN
	NULL 
		WHEN id = 7 THEN
	NULL 
		WHEN id = 8 THEN
	NULL 
		WHEN id = 9 THEN
	NULL 
		WHEN id = 10 THEN
	NULL 
		WHEN id = 11 THEN
	NULL 
		WHEN id = 12 THEN
	NULL 
		WHEN id = 13 THEN
	NULL 
		WHEN id = 14 THEN
	NULL 
		WHEN id = 15 THEN
	NULL 
		WHEN id = 16 THEN
	NULL 
	END,
	update_by =
CASE
		
		WHEN id = 1 THEN
	NULL 
		WHEN id = 2 THEN
	NULL 
		WHEN id = 3 THEN
	NULL 
		WHEN id = 4 THEN
	NULL 
		WHEN id = 5 THEN
	NULL 
		WHEN id = 6 THEN
	NULL 
		WHEN id = 7 THEN
	NULL 
		WHEN id = 8 THEN
	NULL 
		WHEN id = 9 THEN
	NULL 
		WHEN id = 10 THEN
	NULL 
		WHEN id = 11 THEN
	NULL 
		WHEN id = 12 THEN
	NULL 
		WHEN id = 13 THEN
	NULL 
		WHEN id = 14 THEN
	NULL 
		WHEN id = 15 THEN
	NULL 
		WHEN id = 16 THEN
	NULL 
	END,
	update_time =
CASE
		
		WHEN id = 1 THEN
	NULL 
		WHEN id = 2 THEN
	NULL 
		WHEN id = 3 THEN
	NULL 
		WHEN id = 4 THEN
	NULL 
		WHEN id = 5 THEN
	NULL 
		WHEN id = 6 THEN
	NULL 
		WHEN id = 7 THEN
	NULL 
		WHEN id = 8 THEN
	NULL 
		WHEN id = 9 THEN
	NULL 
		WHEN id = 10 THEN
	NULL 
		WHEN id = 11 THEN
	NULL 
		WHEN id = 12 THEN
	NULL 
		WHEN id = 13 THEN
	NULL 
		WHEN id = 14 THEN
	NULL 
		WHEN id = 15 THEN
	NULL 
		WHEN id = 16 THEN
	NULL 
	END 
	WHERE
		id IN (
			1,
			2,
			3,
			4,
			5,
			6,
			7,
			8,
			9,
			10,
			11,
			12,
			13,
			14,
			15,
		16 
	)