Mybatis 批量插入和修改

水深无声 2022-05-25 06:21 285阅读 0赞

<update id="updateForBatch" parameterType="java.util.List">
      update info_message_receive set
    
      status=
      <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
        when #{item.id} then #{item.status}
      </foreach>
    
      where id in
      <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.id}
      </foreach>
    </update>

<update id="updateForBatch" parameterType="java.util.List">  
              update user_credit_black_list set  
              type=  
              <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">  
                when #{item.id,jdbcType=BIGINT} then #{item.type,jdbcType=VARCHAR}  
              </foreach>  
              ,user_id=  
              <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">  
                when #{item.id,jdbcType=BIGINT} then #{item.userId,jdbcType=BIGINT}  
              </foreach>  
              ,update_time=  
              <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">  
                when #{item.id,jdbcType=BIGINT} then #{item.updateTime,jdbcType=TIMESTAMP}  
              </foreach>  
              ,delete_flg=  
              <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">  
                when #{item.id,jdbcType=BIGINT} then #{item.deleteFlg,jdbcType=BIT}  
              </foreach>  
              ,update_code=  
              <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">  
                when #{item.id,jdbcType=BIGINT} then #{item.updateCode,jdbcType=BIGINT}  
              </foreach>  
              where ID in  
              <foreach collection="list" index="index" item="item" separator="," open="(" close=")">  
                #{item.id,jdbcType=BIGINT}  
              </foreach>  
          </update>

<update id="updateForBatch" parameterType="java.util.List">  
            <foreach close=";" collection="list" index="index" item="record" open="" separator=";">  
                update A set value=#{record.value}  
                where detail_id=#{record.detailId} 
            </foreach>
    </update>

此时，需要在db链接url后面带一个参数  &allowMultiQueries=true

即：  jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true

void updateForBatch(@Param("list") List<InfoMessageReceive> list);

批量修改

<insert id="insertBatch">
      insert into info_message_receive (id, user_id, info_message_id,
      status)
      values
      <foreach item="item" collection="list" separator=",">
        (#{item.id,jdbcType=VARCHAR}, #{item.userId,jdbcType=VARCHAR}, #{item.infoMessageId,jdbcType=VARCHAR},
        #{item.status,jdbcType=INTEGER})
      </foreach>
    </insert>

void insertBatch(@Param("list") List<InfoMessageReceive> list);

批量插入