Mybatis批量更新,批量删除

电玩女神 2022-10-09 12:09 610阅读 0赞

**目录**

Mybatis批量更新

1、单个字段批量更新

2、多个字段批量更新

trim标签的使用

Mybatis批量删除

1、传数组

2、传map

3、多参数批量删除示例

备注

Mybatis：通过on duplicate key update实现批量插入或更新

--------------------

# Mybatis批量更新 #

## 1、单个字段批量更新 ##

<update id="batchUpdateReserveStatus" parameterType="java.util.List">
 update dm_simulator_reserve_user
 <trim prefix="set" suffixOverrides=",">
 <trim prefix="reserve_status =case" suffix="end,">
 <foreach collection="list" item="item" index="index">
 <if test="item.reserveStatus !=null and item.reserveStatus != -1">
 when id=#{item.id} then #{item.reserveStatus}
 </if>
 </foreach>
 </trim>
 </trim>
 where id in
 <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
 #{item.id,jdbcType=BIGINT}
 </foreach>
 </update>

代码转化成`sql`如下:

update dm_simulator_reserve_user 
    set reserve_status =
    case when id=? then ? when id=? then ? end 
    where id in ( ? , ? )

## 2、多个字段批量更新 ##

<update id="updateBatch" parameterType="java.util.List">
 update t_user
 <trim prefix="set" suffixOverrides=",">
 <trim prefix="STATUS =case" suffix="end,">
 <foreach collection="list" item="i" index="index">
 <if test="i.status!=null">
 when USER_ID=#{i.userId} then #{i.status}
 </if>
 </foreach>
 </trim>
 <trim prefix=" OPERATE_TIME =case" suffix="end,">
 <foreach collection="list" item="i" index="index">
 <if test="i.operateTime!=null">
 when USER_ID=#{i.userId} then #{i.operateTime}
 </if>
 </foreach>
 </trim>
 
 <trim prefix="OPERATOR =case" suffix="end," >
 <foreach collection="list" item="i" index="index">
 <if test="i.operator!=null">
 when USER_ID=#{i.userId} then #{i.operator}
 </if>
 </foreach>
 </trim>
 </trim>
 where
 <foreach collection="list" separator="or" item="i" index="index" >
 USER_ID=#{i.userId}
 </foreach>
 </update>

## trim标签的使用 ##

> MyBatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <th>属性</th> 
 <th>描述</th> 
 </tr> 
 <tr> 
 <td>prefix</td> 
 <td> 给sql语句拼接的前缀 表示在trim包裹的SQL前添加指定内容 </td> 
 </tr> 
 <tr> 
 <td>suffix</td> 
 <td> 给sql语句拼接的后缀 表示在trim包裹的SQL末尾添加指定内容 </td> 
 </tr> 
 <tr> 
 <td>prefixOverrides</td> 
 <td> 表示去掉（覆盖）trim包裹的SQL的指定首部内容 去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND" </td> 
 </tr> 
 <tr> 
 <td>suffixOverrides</td> 
 <td> 表示去掉（覆盖）trim包裹的SQL的指定尾部内容 去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定 </td> 
 </tr> 
 </tbody> 
</table>

# Mybatis批量删除 #

## 1、传数组 ##

int deleteByBatch(String[] array);
 <delete id="deleteByBatch" parameterType="java.lang.String">
 delete from t_enterprise_output_value
 where OUTPUT_ID IN
 <foreach collection="array" item="outputId" open="(" separator="," close=")">
 #{outputId}
 </foreach>
 </delete>

## 2、传map ##

<delete id="deleteByRole" parameterType="java.util.Map">
 DELETE
 FROM
 t_user_role
 <where>
 <if test="userIdList != null">
 USER_ID IN (#{userIdList,jdbcType=VARCHAR})
 </if>
 <if test="roleId != null">
 AND ROLE_ID=#{roleId,jdbcType=VARCHAR}
 </if>
 <if test="sysCode != null">
 AND SYSCODE=#{sysCode}
 </if>
 </where>
 </delete>

## 3、多参数批量删除示例 ##

> 如果删除不是以主键为条件，而是多个条件同时成立才可以删除

<delete id="deleteByUserIdSysRoleBatch">
 delete from t_user_role
 where SYSCODE = #{sysCode,jdbcType=VARCHAR} AND ROLE_ID = #{roleId,jdbcType=VARCHAR} AND USER_ID IN
 <foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
 #{item}
 </foreach>
 </delete>
 
 
 <delete id="deleteUserJob" parameterType="java.util.List">
 delete from sys_user_job where
 <foreach collection="list" item="item" separator=" or " index="index">
 (user_id = #{item.userId} and job_id= #{item.jobId})
 </foreach>
 </delete>

# 备注 #

## [Mybatis：通过on duplicate key update实现批量插入或更新][Mybatis_on duplicate key update] ##

> [https://blog.csdn.net/fly910905/article/details/104004165][Mybatis_on duplicate key update]

参考链接: [https://www.cnblogs.com/javalanger/p/10899088.html][https_www.cnblogs.com_javalanger_p_10899088.html]

[Mybatis_on duplicate key update]: https://blog.csdn.net/fly910905/article/details/104004165
[https_www.cnblogs.com_javalanger_p_10899088.html]: https://www.cnblogs.com/javalanger/p/10899088.html