Mybatis-批量更新-支持判断

批量更新 3种写法

写法1：

void batchUpdateSort(@Param("list") List<ExamPaperCategory> examPaperCategorys);

<update id="batchUpdateSort">
    UPDATE `exam_paper_category`
    SET sort =
    <foreach collection="list" item="item" index="index" separator=" " open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.sort}
    </foreach>
    ,update_time =
    <foreach collection="list" item="item" index="index" separator=" " open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.updateTime}
    </foreach>
    ,updater_id =
    <foreach collection="list" item="item" index="index" separator=" " open="CASE id" close="END">
        WHEN #{item.id} THEN #{item.updaterId}
    </foreach>
    WHERE id IN
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

// 支持字段判空 未具体实战
<update id="batchUpdateUserInfo">
    UPDATE `edu_user_learning_course_record_every_day`
    <set>
        user_status =
         <!-- 这里还是必填  -->
         <!-- open="CASE id" 这里加id -->
        <foreach collection="list" item="item" index="index" separator=" " open="CASE id" close="END">
            WHEN #{item.id} THEN
            <choose>
                <when test="item.userStatus != null ">
                    #{item.userStatus}
                </when>
                <otherwise>
                    user_status
                </otherwise>
            </choose>
        </foreach>
        ,
        user_name =
        <!-- open="CASE " 这里不加id  -->
        <!-- 判断空 或 '' 一定要包一层 ifnull -->
        <foreach collection="list" item="item" index="index" separator=" " open="CASE " close="END">
            WHEN id = #{item.id} and ifnull(#{item.userName},null) is not null  and  ifnull(#{item.userName},'') &lt;&gt; '' THEN #{item.userName}
            <!-- 不要写 ELSE  -->
        </foreach>
        ,
        department_id =
        <!-- open="CASE " 这里不加id  -->
        <!-- 判断空 或 '' 一定要包一层 ifnull -->
        <foreach collection="list" item="item" index="index" separator=" " open="CASE " close="END">
            WHEN id =#{item.id} and ifnull(#{item.departmentId},null) is not null THEN #{item.departmentId}
            <!-- 不要写 ELSE  -->
        </foreach>

    </set>
    WHERE id IN
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

写法2：

update edu_train_course_learning_analysis_t set container_id = case when id = 999999 then '容器id' end, user_name = case when id = 999999 then '姓名' end where id in ( 999999 )

<update id="updateBatchSelective" parameterType="java.util.List">

update edu_train_course_learning_analysis_t
<trim prefix="set" suffixOverrides=",">

  <trim prefix="container_id = case" suffix="end,">
    <foreach collection="list" index="index" item="item">
      <if test="item.containerId != null">
        when id = #{item.id,jdbcType=INTEGER} then #{item.containerId,jdbcType=VARCHAR}
      </if>
    </foreach>
  </trim>

  <trim prefix="user_name = case" suffix="end,">
    <foreach collection="list" index="index" item="item">
      <if test="item.userName != null">
        when id = #{item.id,jdbcType=INTEGER} then #{item.userName,jdbcType=VARCHAR}
      </if>
    </foreach>
  </trim>

</trim>
where id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
  #{item.id,jdbcType=INTEGER}
</foreach>
</update>

写法3： 其实应该多条

<update id="updateMaintainStuffInfo" parameterType="list">
<bind name="shardNum" value="@com.f6car.maintain.core.F6Static@getShardNum()"/>
<foreach collection="list" item="item" open="" close="" separator=";" index="index">
    UPDATE ts_maintain_part_detail_shard_${shardNum}
    <set>
        <if test="item.avgPrice!=null">
            avg_price=#{item.avgPrice}
        </if>
        <if test="item.avgPriceNoTax!=null ">
            ,avg_price_no_tax = #{item.avgPriceNoTax}
        </if>
        <if test="item.stockOutNumber!=null ">
            ,stock_out_number = #{item.stockOutNumber}
        </if>
        <if test="@Ognl@isNotEmpty(item.idEmployee)">
            ,id_employee = #{item.idEmployee}
        </if>
        <if test="@Ognl@isNotEmpty(item.employeeName)">
            ,employee_name = #{item.employeeName}
        </if>
    </set>
    WHERE id = #{item.id}
</foreach>
</update>