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(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。
| 属性 | 描述 |
| prefix | 给sql语句拼接的前缀 表示在trim包裹的SQL前添加指定内容 |
| suffix | 给sql语句拼接的后缀 表示在trim包裹的SQL末尾添加指定内容 |
| prefixOverrides | 表示去掉（覆盖）trim包裹的SQL的指定首部内容 去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND" |
| suffixOverrides | 表示去掉（覆盖）trim包裹的SQL的指定尾部内容 去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定 |
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实现批量插入或更新
参考链接: https://www.cnblogs.com/javalanger/p/10899088.html
