MyBatils批量插入/更新

最新推荐文章于 2025-04-15 22:12:21 发布

原创最新推荐文章于 2025-04-15 22:12:21 发布 · 257 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python

MyBatils 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何使用MyBatis的批量插入和更新功能，展示了`batchInsertOrUpdate`方法，以及如何利用`trim`标签动态拼接SQL，包括`ON DUPLICATE KEY UPDATE`和不同条件下的`CASE`语句。

 int batchInsertOrUpdate(@Param("list") List<AuntRestInfo> list);

<insert id="batchInsertOrUpdate" parameterType="map">
    <!--@mbg.generated generated on Tue Oct 20 10:53:28 CST 2020.-->
    insert into aunt_rest_info
    (rest_id, aunt_id, rest_day, rest1_start_time, rest1_end_time, rest2_start_time,
      rest2_end_time, creater, modifier, yn)
    values
      <foreach collection="list" item="item" separator=",">
      (#{item.restId,jdbcType=BIGINT}, #{item.auntId,jdbcType=BIGINT}, #{item.restDay,jdbcType=TINYINT},
        #{item.rest1StartTime,jdbcType=TIME}, #{item.rest1EndTime,jdbcType=TIME}, #{item.rest2StartTime,jdbcType=TIME},
        #{item.rest2EndTime,jdbcType=TIME}, #{item.creater,jdbcType=VARCHAR}, #{item.modifier,jdbcType=VARCHAR}, #{item.yn,jdbcType=TINYINT}
        )
      </foreach>
    ON DUPLICATE KEY UPDATE
    rest1_start_time = values(rest1_start_time),
    rest1_end_time = values(rest1_end_time),
    rest2_start_time = values(rest2_start_time),
    rest2_end_time = values(rest2_end_time),
    modifier = values(modifier),
    yn = values(yn)
  </insert>

MyBatils批量更新

<update id="batchUpdate" parameterType="java.util.List">
    update time_stock
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="time_stock_type =case" suffix="end,">
            <foreach collection="list" item="item">
                <if test="item.timeStockType != null">
                    when time_stock_id = #{item.timeStockId} then #{item.timeStockType}
                </if>
                <if test="item.timeStockType == null">
                    when time_stock_id = #{item.timeStockId} then time_stock.time_stock_type
                </if>
            </foreach>
        </trim>
        <trim prefix="time_stock_status =case" suffix="end,">
            <foreach collection="list" item="item">
                <if test="item.timeStockStatus != null">
                    when time_stock_id = #{item.timeStockId} then #{item.timeStockStatus}
                </if>
                <if test="item.timeStockStatus == null">
                    when time_stock_id = #{item.timeStockId} then time_stock.time_stock_status
                </if>
            </foreach>
        </trim>
    </trim>
    <where>
        <foreach collection="list" separator="or" item="item">
            time_stock_id = #{item.timeStockId}
            and time_stock_status = #{item.preTimeStockStatus}
            and time_stock_type = #{item.preTimeStockType}
        </foreach>
    </where>
</update>

 <update id="batchUpdate" parameterType="java.util.List">
     update ls_voucher_bind
    <trim prefix="set" suffixOverrides=",">
     <trim prefix="expired_status =case" suffix="end,">
       <foreach collection="list" item="item" index="index">
         <if test="item.expiredStatus !=null">
           when voucher_num=#{item.voucherNum} then #{item.expiredStatus}
         </if>
       </foreach>
     </trim>
      <trim prefix="verified_status=case" suffix="end">
        <foreach collection="list" item="item" index="index">
          <if test="item.verifiedStatus != null">
          when voucher_num=#{item.voucherNum} then #{item.verfiedStatus}
          </if>
        </foreach>
      </trim>
      <trim prefix="invalid_status =case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.invalidStatus != null" >
            when voucher_num=#{item.voucherNum} then #{item.invalidStatus}
          </if>
        </foreach>
      </trim> 
    </trim>
    where voucher_num in
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
    #{item.voucherNum,jdbcType=BIGINT}
    </foreach>
  </update>
    
   知识点：
      trim:具有动态拼接SQL的能力
      trim具有四个属性：
           prefix:表示在trim包裹的SQL前添加指定内容
           suffix:表示在trim包裹的SQL末尾添加内容
      prefixOverrides:表示去掉（覆盖）trim包裹的SQL的指定首部内容
      suffixOverrides:表示去掉（覆盖）trim包裹的SQL的指定尾部内容