mybatis 批量动态更新sql语句 但是不支持回滚慎用,一般可用于自己可控制的回滚

 

 1.实体

public class FarmerInfo {
    /** 序列*/
    private String guid;


    /** 可售数据*/
    private Integer canSold;



    /**支付金额*/
    private int payNum;


}

 2.mapper接口方法

int updateList(@Param("list") List<FarmerInfo> farmerInfosUpdate);

3.mysql 对于语句

UPDATE t_farmer_info
    SET 
    can_sold = CASE id 
        WHEN 1 THEN '10'
        WHEN 2 THEN '20'
        WHEN 3 THEN '30'
    END, 
    pay_num = CASE id 
        WHEN 1 THEN '123'
        WHEN 2 THEN '321'
        WHEN 3 THEN '213'
    END
WHERE id IN (1,2,3)

  4.mapper.xml 映射

<update id="updateList" parameterType="java.util.List">
    update t_farmer_info
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="can_sold =case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.canSold !=null and item.canSold != ''">
            when guid=#{item.guid} then #{item.canSold}
          </if>
        </foreach>
      </trim>
      <trim prefix="pay_num =case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.payNum !=null and item.payNum!= ''">
            when guid=#{item.guid} then #{item.payNum}
          </if>
        </foreach>
      </trim>
    </trim>
    where guid in
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
      #{item.guid}
    </foreach>
  </update>