Mybatis中实现动态SQL

超、凢脫俗 2024-03-24 21:52 67阅读 0赞

#### 目录 ####

*  1 背景
 *  2 if判断
 *  2 choose(when,otherwise)选择分支进入
 *  3 where
 *  4 set
 *  5 trim
 *  6 foreach循环遍历
 *  7 小结

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

## 1 背景 ##

![在这里插入图片描述][63df950af28942969b73abc777d3bf4a.png]

不建议使用，但是有些需求又不得不用回来温习下。动态SQL语句,也就意味着SQL语句不在是一成不变的而是具有多样性.

## 2 if判断 ##

if的用法还是跟平常差不多的(不过没有else if也没有else)

<update id="modify" parameterType="User">
            UPDATE `smbms_user`
            <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
                <if test="userCode!=null">`userCode` = #{userCode} , </if>
                <if test="userName!=null">`userName` = #{userName} , </if>
                <if test="userPassword!=null">`userPassword` = #{userPassword} , </if>
                <if test="gender!=null">`gender` = #{gender} , </if>
                <if test="birthday!=null">`birthday` = #{birthday} ,</if>
                <if test="phone!=null">`phone` = #{phone} , </if>
                <if test="address!=null">`address` = #{address} , </if>
                <if test="userRole!=null">`userRole` = #{userRole} , </if>
                <if test="createdBy!=null">`createdBy` = #{createdBy} , </if>
                <if test="creationDate!=null">`creationDate` = #{creationDate} , </if>
                <if test="modifyBy!=null">`modifyBy` = #{modifyBy}</if>
            </trim>
        </update>

如上面的代码,如果是空的字段则不执行更新操作

## 2 choose(when,otherwise)选择分支进入 ##

choose就相当于Java中的switch,when相当于case,otherwise相当于default

<select id="getBill" resultType="Bill">
            SELECT * FROM smbms_bill b JOIN smbms_provider p  ON b.providerId=p.id
            WHERE 1=1
            <choose>
                
                <when test="id!=null">
                    AND    b.providerId=#{
        id} 
                </when>
                
                <when test="proName!=null">
                    AND p.proName LIKE CONCAT('%',#{
        proName},'%')
                </when>
                
                <otherwise>
                    AND p.proContact LIKE CONCAT('%张%')
                </otherwise>
            </choose>
        </select>

## 3 where ##

此标签可以忽略条件中多余的and和or如下

SELECT * FROM smbms_bill b JOIN smbms_provider p  ON b.providerId=p.id
            <where>
                <if test="id!=null">
                     AND b.providerId=#{
        id}
                </if>
                <if test="proName!=null">
                     AND p.proName LIKE CONCAT('%',#{
        proName},'%')
                </if>
            </where>

假设id为1 proName为北京 两个if都满足条件时就会拼凑成这样

SELECT * FROM smbms_bill b JOIN smbms_provider p  ON b.providerId=p.id WHERE b.providerId=1 AND p.proName LIKE CONCAT('%北京%')

其实解决这种情况不用where也是可以的,只需在前面加上一个为真的条件即可

SELECT * FROM smbms_bill b JOIN smbms_provider p  ON b.providerId=p.id
            where 1=1
            <if test="id!=null">
                 AND b.providerId=#{
        id}
            </if>
            <if test="proName!=null">
                 AND p.proName LIKE CONCAT('%',#{
        proName},'%')
            </if>

效果也是一样的

## 4 set ##

此标签可以忽略多余的逗号

UPDATE `smbms_user` 
            <set>
                <if test="userPassword!=null">
                    `userPassword` = #{
        userPassword},
                </if>
            </set>
               WHERE `id` = #{
        id} ;

## 5 trim ##

此标签属性如下

1.  prefix:通过自动识别是否有返回值,在trim包含的内容上加上前缀
2.  suffix:在trim包含的内容上加上后缀
3.  prefixoverrides:对trim包含内容的首部进行指定内容的忽略
4.  suffixoverrides;对于trim包含内容的尾部进行指定内容的忽略

栗子1

<trim prefix="where" prefixOverrides="and|or">
                <if test="userRole!=null">
                    and userRole=#{
        userRole}
                </if> 
                <if test="userName!=null and userName!=''">
                    and u.userName like CONCAT('%',#{
        userName},'%')
                </if>
                order by creationDate DESC limit #(from),#(pageSize)
            </trim>

栗子2

UPDATE `smbms_user`
            
            <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
                <if test="userCode!=null">`userCode` = #{
        userCode} , </if>
                <if test="userName!=null">`userName` = #{
        userName} , </if>
            </trim>

## 6 foreach循环遍历 ##

此标签的属性如下

1.  item表示集合中每一个元素进行迭代时的别名
2.  index:指定一个名字，用于表示每次迭代的位置.好比for循环中自定义的 i
3.  open表示该语句以什么开始，在in条件语句中以"("开始
4.  separator表示在每次进行迭代之间以什么符号作为分隔 符,在in条件语句中以逗号进行分隔
5.  close表示以什么结束。在in条件语句中以")"结尾
6.  在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的,主要有以下3种情况：
    
    1.  如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
    2.  如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
    3.  如果传入的参数是多个的时候，我们一般把它们封装成一个Map了,此时属性值为map其中的一个key

栗子1

当入参为list时

<select id="getUSerByRoleId_foreach_list" resultMap="userMapByRole">
            select * from smbms_user where userRole in
            
            <foreach collection="list" item="roleList" open="(" separator="," close=")">
                #{
        roleList}
            </foreach>
        </select>

栗子2

当入参为数组时

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
            select * from smbms_user where userRole in
            
            <foreach collection="array" item="roleIds" open="(" separator="," close=")">
                #{
        roleIds}
            </foreach>
        </select>

栗子3

当为map时

<select id="getProvider"  resultMap="providerresult">
            SELECT * FROM smbms_bill b JOIN smbms_provider p  ON b.providerId=p.id
            where b.providerId in
            
            <foreach collection="#{bibli}" item="pros" open="(" separator="," close=")">
                
                #{
        pros.providerId}    
            </foreach>
        </select>

## 7 小结 ##

*  if+set:动态完成更新操作
 *  if+where:动态完成多条件查询
 *  if+trim:完成多条件查询(代替where)或者更新操作(代替set)
 *  choose(when,otherwise):动态完成多条件查询(多选一)
 *  foreach:主要用于in条件查询中,迭代集合,最关键的部分为collection属性,根据不同的入参类型,该属性值则不同
 *  1.  如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
    2.  如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
    3.  如果传入的参数是多个的时候，我们一般把它们封装成一个Map了,此时属性值为map其中的一个key

[63df950af28942969b73abc777d3bf4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/78a86f31c06e41908c87b4008b6ca2ae.png