mybatis批量修改

一、mybatis批量修改

1.1 根据id批量更新

<update id="batchUpdate">
    update
    ${dbName}.tableName tn
    set
    <trim prefix="tn.column_one = case id" suffix="end">
        <foreach collection="list" item="item" index="index">
            when #{item.id} then #{item.columnOne}
        </foreach>
    </trim>
    <trim prefix=", tn.column_two = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnTwo}
        </foreach>
    </trim>
    <trim prefix=", tn.column_three = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnThree}
        </foreach>
    </trim>
    <trim prefix=", tn.column_four = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnFour}
        </foreach>
    </trim>
    <trim prefix=", tn.column_five = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnFive}
        </foreach>
    </trim>
    <trim prefix=", tn.column_six = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnSix}
        </foreach>
    </trim>
    <trim prefix=", tn.column_seven = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnSeven}
        </foreach>
    </trim>
    <trim prefix=", tn.column_eight = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnEight}
        </foreach>
    </trim>
    <trim prefix=", tn.column_nine = case id" suffix="end">
        <foreach collection="list" item="item">
            when #{item.id} then #{item.columnNine}
        </foreach>
    </trim>
    , tn.status = '1'
    where
    tn.id in
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

1.2 根据多个字段更新其他字段值（批量）

1.2.1写法一

<update id="batchUpdateWithoutId" parameterType="java.util.List">
UPDATE ${dbName}.tableName
SET column_one =
<foreach collection="list" item="item" open="case" close="end" separator="">
  when column_two = #{item.columnTwo} and column_three = #{item.columnThree} and column_four = #{item.columnFour}
  and column_five = #{item.columnFive}
  then #{item.columnOne}
</foreach>
,column_six =
<foreach collection="list" item="item" open="case" close="end" separator="">
  when column_two = #{item.columnTwo} and column_three = #{item.columnThree} and column_four = #{item.columnFour}
  and column_five = #{item.columnFive}
  then #{item.columnSix}
</foreach>
WHERE (column_two,column_three,column_four,column_five) IN
<foreach collection="list" item="item" open="(" close=")" separator=",">
 ( #{item.columnTwo},#{item.columnThree},#{item.columnFour},#{item.columnFive})
</foreach>

1.2.2写法二

 <update id="batchUpdateCrCodeAndFscDesc2">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
  UPDATE ${dbName}.tableName
  <set>
    <if test="item.columnOne != null and item.columnOne != ''">
      column_one = #{item.columnOne},
    </if>
    <if test="item.columnTwo != null and item.columnTwo != ''">
      column_two = #{item.columnTwo},
    </if>
  </set>
  WHERE column_three = #{columnThree} and column_four = #{columnFour} and column_five = #{columnFive} and column_six = #{columnSix}
</foreach>

1.2.3说明

1.关于写法二：Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的，也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行

2.更新记录数不大的情况下，写法二的性能高于写法一；测试修改1500条数据，写法二耗时500ms，写法一耗时2106ms；

3.实际开发个人使用的是第一种写法，原因暂时为项目组开发要求。