三.MyBatis动态sql语句

心已赠人 2022-05-26 11:22 363阅读 0赞

假设有如下需求：

**根据用户选择的内容进行查询，没有选择的内容不作为查询条件**，当类似这样查询条件不固定时，可以用动态SQL来解决，先看最简单的IF标签

![70][]

当if标签中test为真时，会把if标签包裹的内容添加到sql语句中，为假时不添加

这样的if语句是有问题的，比如当两个查询条件都不存在时，sql语句中where后就没有了内容，这样的sql语句是错误的，由此引入 **where** **标签**

![70 1][]

被where标签包裹的内容：

1.第一个sql语句如果带有and，会被自动删除掉，所以加和不加都一样

2.如果where标签包裹的内容没有添加任何sql语句，where不会出现

如下图，student信息不满足 if标签的test，所以不会添加人和内容到sql语句中

![70 2][]

所以拼接出的sql语句没有出现where

![70 3][]

choose标签

如同java中的switch语句一样，choose标签有两个子标签，一个是when（类似case），一个是otherwise（类似default），如果when标签的语句为真就执行，而且执行后直接返回，不会继续往下执行，如果所有的when标签都为假，则执行otherwise标签

![70 4][]

foreach标签：

如果需要查询，id为1，3，5，7，9的用户如果直接写死sql查询语句，是这么查的

Selsect name , id ,score,age from tbl\_student where id in (1，3，5，7，9)

但是如果要求根据传给你的数组进行查询时，你需要用foreach标签进行拼接字符串

![70 5][]

collection：指定当前要遍历的对象是什么容器（数组或者集合）

open：拼接遍历的内容时，以什么开头

close：拼接遍历的内容时，以什么结尾

spearator：拼接时遍历的内容时，以什么分割

item：要遍历的collection中的每一个项目单元

如果要遍历的不是数组而是集合只需要修改collection为list

![70 6][]

如果传入的是list的泛型是自定义的类型，可以这么接收

![70 7][]

sql片段：

比如我们以上的案例，每一次都要写select name,age,score,id from tbl\_student 这样的一段重复的代码，如果使用sql片段就可以不写这样的重复的代码，但是缺点是可读性差，不灵活。

![70 8][]

### trim标记是一个格式化的标记，可以完成set或者是where标记的功能，如下代码： ###

1、

select * from user 
    
    　　<trim prefix="WHERE" prefixoverride="AND |OR">
    
    　　　　<if test="name != null and name.length()>0"> AND name=#{name}</if>
    
    　　　　<if test="gender != null and gender.length()>0"> AND gender=#{gender}</if>
    
    　　</trim>

假如说name和gender的值都不为null的话打印的SQL为：select \* from user where    name = 'xx' and gender = 'xx'

在红色标记的地方是不存在第一个and的，上面两个属性的意思如下：

prefix：前缀

prefixoverride：去掉第一个and或者是or

2、

update user
    
    　　<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
    
    　　　　<if test="name != null and name.length()>0"> name=#{name} , </if>
    
    　　　　<if test="gender != null and gender.length()>0"> gender=#{gender} ,  </if>
    
    　　</trim>

假如说name和gender的值都不为null的话打印的SQL为：update user set name='xx' , gender='xx'     where id='x'

在红色标记的地方不存在逗号，而且自动加了一个set前缀和where后缀，上面三个属性的意义如下，其中prefix意义如上：

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

suffix：后缀

### set标签 ###

同`where`标签功能类似，`where`用去去除第一个条件中出现的`and`前缀，那么`set`标签就是去除最后一个更新字段语句中出现的`,[逗号]`后缀。

<update id="updateUseSetTag" parameterType="com.heiketu.testpackage.pojo.Product">
        UPDATE
          Products
            <set>
                <if test="vendId != null and vendId != ''">
                    vend_id = #{vendId},
                </if>
                <if test="prodName != null and prodName != ''">
                    prod_name = #{prodName},
                </if>
                <if test="prodDesc != null and prodDesc != ''">
                    prod_desc = #{prodDesc},
                </if>
            </set>
          <where>
              prod_id = #{prodId}
          </where>
    </update>

**`where 和 set 完全都可以被trim替换使用。如下代码:`**

<update id="updateUseSetTag" parameterType="com.heiketu.testpackage.pojo.Product">
        UPDATE
          Products
            <trim prefix="set" prefixOverrides="," suffixOverrides=",">
                <if test="vendId != null and vendId != ''">
                    vend_id = #{vendId},
                </if>
                <if test="prodName != null and prodName != ''">
                    prod_name = #{prodName},
                </if>
                <if test="prodDesc != null and prodDesc != ''">
                    prod_desc = #{prodDesc},
                </if>
            </trim>
          <where>
              prod_id = #{prodId}
          </where>
    </update>

bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。

不使用bind

<select id="selectSysUsersAdvancedWithWhere" resultType="com.artisan.mybatis.xml.domain.SysUser">
            SELECT
                a.id,
                a.user_name userName,
                a.user_password userPassword,
                a.user_email userEmail,
                a.user_info userInfo,
                a.head_img headImg,
                a.create_time createTime
            FROM
                sys_user a
            <where>
                <if test="userName != null and userName != '' ">
                    and user_name like concat('%',#{userName},'%')
                </if>
                <if test="userEmail != null and userEmail != '' ">
                    and user_email = #{userEmail}
                </if>
            </where>
        </select>

使用concat函数连接字符串，在MySQL中，这个函数支持多个参数，但是在Oracle中只支持两个参数。 由于不同数据库之间的语法差异，如果更换了数据库，有些SQL语句可能就需要重写。 针对这种情况，可以使用bind标签来避免由于更换数据库带来的一些麻烦。 我们将上面的语句改为bind方式，如下

<select id="selectSysUserByAdvancedCondition" resultType="com.artisan.mybatis.xml.domain.SysUser">
            SELECT
                a.id,
                a.user_name userName,
                a.user_password userPassword,
                a.user_email userEmail,
                a.user_info userInfo,
                a.head_img headImg,
                a.create_time createTime
            FROM
                sys_user a
            <where>
                <if test="userName != null and userName != '' ">
                    
                    <bind name="userNameLike" value=" '%' + userName + '%' "/>
                        and user_name like #{userNameLike}
                </if>
                <if test="userEmail != null and userEmail != '' ">
                    and user_email = #{userEmail}
                </if>
            </where>
        </select>

[70]: /images/20220526/29cbf1844f9b4dd4ac2da83df64be5d6.png
[70 1]: /images/20220526/d7d6009b13834fa984c7b4b6dbab678c.png
[70 2]: /images/20220526/32615f0be46147ffa217244bfa70f8d9.png
[70 3]: /images/20220526/4c799e8c674c46b2bf2248c7adffa02a.png
[70 4]: /images/20220526/9e65409870a0455681acac308c2a4c4e.png
[70 5]: /images/20220526/cedd4b2c0e2148d9b5328a3e8be1dc05.png
[70 6]: /images/20220526/2fb5810978b94d488c3c9c53cfab58c6.png
[70 7]: /images/20220526/994c7ed9398348d9942148975bda4e18.png
[70 8]: /images/20220526/59560e0818e745a198eed300eabc4597.png