前言
Mybatis 对于大家来说真是再熟悉不过了,ORM映射框架,目前在国内项目中备受青睐,
适合国内复杂的业务场景以及敏捷开发的需要,不过其参数的使用有事让人迷糊,今天就好好的讲一下,可以做个参考节省时间。
批量更新
#dao 层
void batchUpdateTargetData(@Param(value = "encryptListData") List<UserInfo> encryptListData,@Param(value = "yearNum") Integer yearNum);
## 第一种更新 (优点:
1.书写方便
2.不用考虑条数限制
缺点:
1. 性能差
2. 数据库连接配置多条查询
)
<update id="batchUpdateTargetData">
<foreach collection="encryptListData" item="encryptData" index="index" open="" close="" separator=";">
update tableName_${yearNum}
prefix="set" suffixOverrides=",">
<if test="encryptData.receiverName!=null and encryptData.receiverName!=''">
receiver_name =#{encryptData.receiverName},
</if>
<if test="encryptData.receiverAddress!=null and encryptData.receiverAddress!=''">
receiver_address =#{encryptData.receiverAddress},
</if>
<if test="encryptData.receiverMobile!=null and encryptData.receiverMobile!=''" >
receiver_mobile =#{encryptData.receiverMobile},
</if>
<if test="encryptData.receiverPhone!=null and encryptData.receiverPhone!=''">
receiver_phone=#{encryptData.receiverPhone}
</if>
</trim>
where id = #{encryptData.id}
</foreach>
</update>
### 第二种更新(优点:
1.性能好
2.数据库连接不用配置多条查询
缺点:
1. 书写复杂
2. 注意每个数据库in 数据的限制
3. 建议分批
)
<update id="batchUpdateTargetData">
update tableName_${yearNum}
<trim prefix="set" suffixOverrides=",">
<trim prefix="receiver_name=case" suffix="end,">
<foreach collection="encryptListData" item="encryptData" index="index">
<if test="encryptData.receiverName!=null and encryptData.receiverName!=''">
when id = #{encryptData.id} then #{encryptData.receiverName}
</if>
</foreach>
</trim>
<trim prefix="receiver_address =case" suffix="end,">
<foreach collection="encryptListData" item="encryptData" index="index">
<if test="encryptData.receiverAddress!=null and encryptData.receiverAddress!=''">
when id = #{encryptData.id} then #{encryptData.receiverAddress}
</if>
</foreach>
</trim>
<trim prefix="receiver_mobile=case" suffix="end,">
<foreach collection="encryptListData" item="encryptData" index="index">
<if test="encryptData.receiverMobile!=null and encryptData.receiverMobile!=''" >
when id = #{encryptData.id} then #{encryptData.receiverMobile}
</if>
</foreach>
</trim>
</trim>
where id in
<trim prefix="(" suffix=")">
<foreach collection="encryptListData" separator="," item="encryptData" index="index" >
#{encryptData.id}
</foreach>
</trim>
</update>
### 批量删除的优化
dao 层
void deleteDetailsByIds(@Param(value = "tenantId") Long tenantId, @Param(value = "ids") List<Long> ids);
mapper 层
<delete id="deleteDetailsByIds">
delete from table_name
where tenant_id = #{tenantId}
<choose>
<when test=" null!=ids and ids.size()==1">
and id =#{ids[0]}
</when>
<otherwise>
and id in
<foreach collection="ids" separator="," open="(" close=")" item="id">
#{id}
</foreach>
</otherwise>
</choose>
</delete>
void updateDetailsByIds(@Param(value = "tenantId") Long tenantId, @Param(value = "ids") List<Long> ids);
mapper 层:
注意点:
1. ids.size() ==1 ===>用 == 进行比较
2. 注意 in 要写在 foreach 外面
3. 赋值用 单引号 '' 表示字符串
4. ${field_value} 必需是前面传过来的
<update id="batchUpdateProdStatusWithIdList">
update table_name
set `status` = 'down'
where prod_id
<if test="prodIdList !=null and prodIdList.size>0">
<choose>
<when test="prodIdList.size==1">
=#{prodIdList[0]}
</when>
<otherwise>
in
<if test="prodIdList !=null and prodIdList.size>1">
<foreach collection="prodIdList" item="prodId" open="(" separator="," close=")">
#{prodId}
</foreach>
</if>
</otherwise>
</choose>
</if>
</update>
对应标签解释
@Param(value = "encryptListData") List<UserInfo> encryptListData
@Param 代表着绑定字段 dao层只有一个参数可以不写,多个必须写,不然找不到对应的参数
collection ————> 当前传参传入的集合类型参数
item ————> 当前集合对应的别名,也就是下面操作的对象
open ————> 起始的标志
close ————> 关闭的标志
separator ————> 数据分割的标志
index ————> 索引可写可不写 基本用不到 可以直接写index ,
tirm 语法格式 类似Java StringBuilder append() 拼接的作用
<trim prefix=" " suffix="" suffixOverrides="" prefixOverrides="">
</trim>
prefix ————> 前缀
suffix ————> 后缀
suffixOverrides ————> 去掉重复的后缀 比如 or
prefixOverrides ————> 去掉重复的前缀 比如 and 和 where 一起使用不用考虑 and 的位置