前言:
mybatis设用场景比较多,可以代替java代码,但还是不建议写过多的逻辑。最近想写一个关于常用函数使用,针对以前没用过的,可能还有很多没写到,可以提上来
一、if else使用
<if test="params.awardId != null and params.awardId != '' ">
<choose>
<when test="params.awardId == 4">
and award_id in(4,16)
</when>
<when test="params.awardId == 7">
and award_id in(7,12)
</when>
<otherwise>
and award_id = #{params.awardId}
</otherwise>
</choose>
</if>
二、String字符串类型的数组foreach循环使用
<if test="advertChannelUser != null and advertChannelUser != '-1'">
and advert_channel_user in
<foreach item="status" collection="advertChannelUser.split(',')" open="(" separator="," close=")">
#{status}
</foreach>
</if>
三、String集合类型foreach循环使用
参数:List
ai.uid0 in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
四、List数组foreach循环使用
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
五、or或者and一起作为条件
select b.bin_name, l.* from bas_location l inner join bas_bin b on l.bin_code = b.bin_code
<where>
<if test="record.binCode != null and record.binCode != ''
or record.locName != null and record.locName != ''">
(
<trim prefixOverrides="AND |OR">
<if test="record.binCode != null and record.binCode != '' ">
AND l.bin_code = '${record.binCode}'
</if>
<if test="record.locName != null and record.locName != '' ">
OR l.loc_name = '${record.locName}'
</if>
</trim>
)
</if>
<include refid="Lable_Page_Where_Clause"/>
</where>
order by l.create_time desc
六、foreach批量插入
<!--批量插入用户-->
<insert id="insertBusinessUserList" parameterType="java.util.List">
insert into `business_user` (`id` , `user_type` , `user_login` )
values
<foreach collection="list" index="index" item="item" separator=",">
<trim prefix="(" suffix=")" suffixOverrides=",">
<choose>
<when test="item.id != null and item.id !=''">
#{item.id,jdbcType=CHAR},
</when>
<otherwise>
'',
</otherwise>
</choose>
<choose>
<when test="item.userType != null and item.userType !=''">
#{item.userType,jdbcType=VARCHAR},
</when>
<otherwise>
'',
</otherwise>
</choose>
</trim>
</foreach>
</insert>
七、批量冲突忽略新增
<foreach collection="list" item="index">
insert into net_serious_problem (id) values (${index.id})
on duplicate key update
gmt_create = #{index.gmtCreate, jdbcType=TIMESTAMP}
, gmt_modified = #{index.gmtModified, jdbcType=TIMESTAMP}
, creator = #{index.creator}
, modifier = #{index.modifier};
</foreach>