前言：

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 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>