詳解MyBatis特性之動態(tài)SQL

更新時(shí)間：2024年01月14日 14:14:38 作者：不能再留遺憾了

動態(tài) SQL 是 MyBatis 的強(qiáng)大特性之一,這篇文章我們將結(jié)合動態(tài)SQL完成更加復(fù)雜的 SQL 操作,文章通過代碼示例給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下

前言

動態(tài) SQL 是 MyBatis 的強(qiáng)大特性之一。如果你使用過 JDBC 或其它類似的框架，你應(yīng)該能理解根據(jù)不同條件拼接 SQL 語句有多痛苦，例如拼接時(shí)要確保不能忘記添加必要的空格，還要注意去掉列表最后一個(gè)列名的逗號。利用動態(tài) SQL，可以徹底擺脫這種痛苦。具體的定義大家可以參考官方文檔MyBatis動態(tài)SQL。這篇文章我們將結(jié)合動態(tài)SQL完成更加復(fù)雜的 SQL 操作。

增加操作

想必大家肯定遇到過注冊某個(gè)賬號的時(shí)候需要輸入自己的相關(guān)信息，其中這些信息包括：必填信息和非必填信息，對于這些必填信息，我們只需要在創(chuàng)建表的時(shí)候?qū)⑦@個(gè)字段設(shè)置為非 null 就可以了，而對于那些非必選的選項(xiàng)，我們又該如何定義呢？

這時(shí)就需要我們使用動態(tài)標(biāo)簽來判斷了，對于這些可以傳遞值和可以不傳遞值的字段，我們可以使用 <if> 標(biāo)簽來修飾：

@Insert("insert into userinfo(username,`password`,age," +
        "<if test='gender!=null'>gender,</if>" +
        "phone)" +
        "values(#{username},#{password},#{age}," +
        "<if test='gender!=null'>gender,</if>" +
        "#{phone})")
public Integer insertByCondition(UserInfo userInfo);

<if test="">123</if> 這個(gè)標(biāo)簽中 test 表示的是判斷，當(dāng) test 參數(shù)中的判斷為真時(shí)，那么這個(gè)標(biāo)簽的結(jié)果就為 <if> </if>標(biāo)簽之間的代碼塊，在這里就是123；如果 test 中的代碼塊的判斷為假的時(shí)候，那么這個(gè)<if> 標(biāo)簽的結(jié)果就是空。

@Test
void insertByCondition() {
    UserInfo userInfo = new UserInfo();
    userInfo.setUsername("彭于晏");
    userInfo.setPassword("123456");
    userInfo.setAge(18);
    userInfo.setPhone("132131231");
    int ret = userInfoMapper.insertByCondition(userInfo);
    log.info(ret + "被更新");
}

然后我們調(diào)用這個(gè)方法的時(shí)候，可以不為 gender 字段傳遞值，如果不傳遞值，那么這個(gè)字段的值就為創(chuàng)建表時(shí)定義的默認(rèn)值，也可以傳遞值。然后我們運(yùn)行一下看能達(dá)到效果嗎？

在這里插入圖片描述

這里為什么會報(bào)錯(cuò)呢？因?yàn)?<if> 標(biāo)簽是屬于 JavaScript 的，所以我們需要使用到 <script> 標(biāo)簽。

@Insert("<script>" +
        "insert into userinfo(username,`password`,age," +
        "<if test='gender!=null'>gender,</if>" +
        "phone)" +
        "values(#{username},#{password},#{age}," +
        "<if test='gender!=null'>gender,</if>" +
        "#{phone})" +
         "</script>")
public Integer insertByCondition(UserInfo userInfo);

在這里插入圖片描述

有人會問了，使用 <if> 標(biāo)簽和不使用作用不是一樣的嗎？對于當(dāng)前插入數(shù)據(jù)操作作用是一樣的，但是如果我們進(jìn)行的是修改操作的話，因?yàn)槲覀儾恢烙脩粜枰薷氖裁葱畔?，所以我們在寫修改操作的話，就需要將所有的字段的修改操作都寫上，但是如果我們不使?<if> 標(biāo)簽的話，并且用戶在修改的時(shí)候，某個(gè)信息沒有修改話，后端SQL預(yù)處理之后是這樣的：update userinfo set username=?, password=?, gender=?, phone=? where username=?，前端傳遞來的參數(shù)是這樣的：null, null, null, 123456, 小美，也就是用戶只是修改了電話號碼這個(gè)字段，但是因?yàn)闆]有使用 <if> 標(biāo)簽，所以其他的字段就會被修改為 null，這就會出現(xiàn)問題了。而使用 <if> 標(biāo)簽就會這樣處理：update userinfo phone=? where username=?，參數(shù)傳遞：123456, 小美。

上面是使用注解的方式來實(shí)現(xiàn) MyBatis 的，實(shí)現(xiàn) MyBatis 不僅可以通過注解來實(shí)現(xiàn)，也可以通過 XML 的格式實(shí)現(xiàn)。我們看看 XML 如何實(shí)現(xiàn)動態(tài) SQL。

首先我們需要告訴 MyBatis 我們的 xml 文件在哪里：

mybatis:
  mapper-locations: classpath:mapper/**Mapper.xml

然后在 XML 文件中寫入下面代碼，SQL 語句寫在 <mapper> 標(biāo)簽中。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis20240101.mapper.UserInfoMapper">

</mapper>

這里 namespace 的值是我們是使用了 MyBatis 框架操作數(shù)據(jù)庫的類的全限定類名稱。

<insert id="insertByCondition">
    insert into userinfo(username,`password`,age,
    <if test="gender!=null">
            gender,
    </if>
    phone)
    values(#{username},#{password},#{age},
    <if test="gender!=null">
        #{gender},
    </if>
     #{phone})
</insert>

因?yàn)?XML 文件本身就支持 JavaScript，所以我們這里不需要添加 <script> 標(biāo)簽，不僅如此，使用 XML 文件的方式寫 MyBatis 還會有提示，所以書寫動態(tài) SQL 建議使用 XML 文件格式。

在這里插入圖片描述

但是使用 <if> 標(biāo)簽也會存在問題，假設(shè)我們將 phone 字段也設(shè)置為動態(tài)的，并且在傳遞值的時(shí)候不傳遞 phone 的話就會出現(xiàn)問題。

<insert id="insertByCondition">
    insert into userinfo(username,`password`,age,
    <if test="gender!=null">
            gender,
    </if>
    <if test="phone!=null">
            phone
    </if>)
    values(#{username},#{password},#{age},
    <if test="gender!=null">
        #{gender},
    </if>
     <if test="phone!=null">
        #{phone}
     </if>)
</insert>

@Test
void insertByCondition() {
    UserInfo userInfo = new UserInfo();
    userInfo.setUsername("彭于晏");
    userInfo.setPassword("123456");
    userInfo.setAge(18);
    //userInfo.setPhone("123456");
    int ret = userInfoMapper.insertByCondition(userInfo);
    log.info(ret + "被更新");
}

在這里插入圖片描述

可以看到，因?yàn)?phone 是我們定義增加操作時(shí)候的最后一個(gè)字段，在這里將 phone 設(shè)置為動態(tài)的，并且在傳遞值的時(shí)候沒有傳遞 phone，那么在拼接 SQL 的時(shí)候 insert into userinfo() values() 中兩個(gè)括號中一定會是以逗號結(jié)尾，那么這樣的 SQL 就是不和規(guī)則的 SQL，那么如何解決呢？這里就需要用到 <trim> 標(biāo)簽了。

<trim>標(biāo)簽

trim 標(biāo)簽在 MyBatis 中主要用于處理 SQL 語句，以去除多余的關(guān)鍵字、逗號，或者添加特定的前綴和后綴。這在進(jìn)行選擇性插入、更新、刪除或條件查詢等操作時(shí)非常有用。

trim 標(biāo)簽有以下屬性：

prefix：表?整個(gè)語句塊，以prefix的值作為前綴
suffix：表?整個(gè)語句塊，以suffix的值作為后綴
prefixOverrides：表?整個(gè)語句塊要去除掉的前綴
suffixOverrides：表?整個(gè)語句塊要去除掉的后綴

因?yàn)槲覀冞@里是語句塊末尾出現(xiàn)了多余的逗號，所以我們配置 suffixOverrides 屬性來刪除多余的逗號。

<insert id="insertByCondition">
    insert into userinfo
    <trim prefix="(" suffix=")" suffixOverrides=",">
        username,`password`,age,
        <if test="gender!=null">
                gender,
        </if>
        <if test="phone!=null">
                phone
        </if>
    </trim>
    values
       <trim prefix="(" suffix=")" suffixOverrides=",">
           #{username},#{password},#{age},
           <if test="gender!=null">
               #{gender},
           </if>
           <if test="phone!=null">
               #{phone}
           </if>
       </trim>
</insert>

在這里插入圖片描述

查詢操作

大家在肯定在網(wǎng)上買過手機(jī)吧，當(dāng)我們買手機(jī)的時(shí)候，往往會加上一些限制條件。

在這里插入圖片描述

而這種加上限制條件的查詢就可以看成是 select 后面加了 where 語句，但是由于不知道用戶需要加上多少查詢時(shí)候的限制條件，所以這里就可以使用到動態(tài) SQL。

UserInfo selectByCondition(UserInfo userInfo);

<select id="selectByCondition" resultType="com.example.mybatis20240101.model.UserInfo">
    select * from userinfo
    where
        <if test="username!=null">
            username=#{username} 
        </if>
        <if test="password!=null">
             and password=#{password} 
        </if>
        <if test="age!=null">
            and age=#{age} 
        </if>
        <if test="phone!=null">
            and phone=#{phone} 
        </if>
</select>

@Test
void selectByCondition() {
    UserInfo userInfo = new UserInfo();
    userInfo.setUsername("彭于晏");
    userInfo.setPhone("34567");
    log.info(userInfoMapper.selectByCondition(userInfo).toString());
}

在這里插入圖片描述

當(dāng)然這里也會出現(xiàn)問題，就是當(dāng)?shù)谝粋€(gè) where 子句沒有傳遞值的話，那么 where 子句中就會多一個(gè) and 在開頭。

@Test
void selectByCondition() {
    UserInfo userInfo = new UserInfo();
    //userInfo.setUsername("彭于晏");
    userInfo.setPhone("34567");
    log.info(userInfoMapper.selectByCondition(userInfo).toString());
}

在這里插入圖片描述

為了解決問題，可以使用 <trim> 標(biāo)簽刪除前面多余的 and：

<select id="selectByCondition" resultType="com.example.mybatis20240101.model.UserInfo">
    select * from userinfo
    where
        <trim prefixOverrides="and">
            <if test="username!=null">
                username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
            <if test="age!=null">
                and age=#{age}
            </if>
            <if test="phone!=null">
                and phone=#{phone}
            </if>
        </trim>
</select>

在這里插入圖片描述

<where>標(biāo)簽

這里是解決了 where 子句中開頭出現(xiàn)多余的 and，如果我們一個(gè)限制條件都不加入呢？

@Test
void selectByCondition() {
    UserInfo userInfo = new UserInfo();
    //userInfo.setUsername("彭于晏");
    //userInfo.setPhone("34567");
    log.info(userInfoMapper.selectByCondition(userInfo).toString());
}

在這里插入圖片描述

這樣就會出現(xiàn)問題，那么這樣如何解決呢？MyBatis 為我們提供了 <where> 標(biāo)簽用來解決查詢語句的 where 子句出現(xiàn)的各種問題，包括開頭出現(xiàn)的多余的 and 和 where 子句無內(nèi)容的情況。

<select id="selectByCondition" resultType="com.example.mybatis20240101.model.UserInfo">
    select * from userinfo
    <where>
        <if test="username!=null">
            username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
        <if test="age!=null">
            and age=#{age}
        </if>
        <if test="phone!=null">
            and phone=#{phone}
        </if>
	</where>
</select>

在這里插入圖片描述

可以看到，當(dāng)我們 where 子句為空的時(shí)候，使用 <where> 標(biāo)簽會自動刪除 where 子句，它也可以幫助我們刪除多余的 and，下面的報(bào)錯(cuò)咱們不管，這時(shí)因?yàn)槲覀儧]加 where 子句，所以就相當(dāng)于查詢整個(gè)表，但是我們方法的返回值是 UserInfo，改為列表就可以了。

當(dāng) where 子句為空的時(shí)候，我們還有一種解決方式，就是自己加上一個(gè) 1=1 的條件，這樣當(dāng)我們加的條件為空的時(shí)候就不會出現(xiàn)錯(cuò)誤。

<select id="selectByCondition" resultType="com.example.mybatis20240101.model.UserInfo">
    select * from userinfo
    where 1=1
        <trim prefixOverrides="and">
            <if test="username!=null">
                username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
            <if test="age!=null">
                and age=#{age}
            </if>
            <if test="phone!=null">
                and phone=#{phone}
            </if>
        </trim>
</select>

修改操作

這個(gè)修改操作就是前面我們舉的一個(gè)例子，我們并不知道用戶會修改哪些信息，所以我們這里就需要使用到動態(tài) SQL 來解決這個(gè)問題。

void updateByCondition(UserInfo userInfo);

<update id="updateByCondition">
    update userinfo set
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="age!=null">
                age=#{age},
            </if>
            <if test="gender!=null">
                gender=#{gender},
            </if>
            <if test="phone!=null">
                phone=#{phone}
            </if>
        where
            <if test="username!=null">
                username=#{username}
            </if>
</update>

@Test
void updateByCondition() {
    UserInfo userInfo = new UserInfo();
    userInfo.setUsername("小美");
    userInfo.setPassword("666666");
    userInfo.setPhone("23456");
    userInfoMapper.updateByCondition(userInfo);
}

在這里插入圖片描述

<set>標(biāo)簽

為了解決 set 中出現(xiàn)的 set 子句中多余的逗號的問題，可以使用 <set> 標(biāo)簽。

在這里插入圖片描述

<update id="updateByCondition">
    update userinfo
    <set>
        <if test="password!=null">
            password=#{password},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
        <if test="gender!=null">
            gender=#{gender},
        </if>
        <if test="phone!=null">
            phone=#{phone}
        </if>
    </set>    
    <where>
        <if test="username!=null">
            username=#{username}
        </if>
    </where>
</update>

如果 set 子句為空的時(shí)候，是會報(bào)錯(cuò)的，通過 <set> 標(biāo)簽無法解決，我們應(yīng)該避免用戶 set 輸入空的子句。

刪除操作 <foreach>標(biāo)簽

當(dāng)我們想要在刪除的時(shí)候刪除不定數(shù)量的條件時(shí)，delete from userinfo where name in("小美","小帥")，因?yàn)闂l件的數(shù)量是不確定的，所以我們在定義的時(shí)候就不知道 where 子句后面有多少個(gè)，所以這里我們就需要用到 <foreach> 標(biāo)簽。

<foreach> 標(biāo)簽可以對集合進(jìn)行遍歷，標(biāo)簽具有以下屬性：

collection：綁定?法參數(shù)中的集合，如 List，Set，Map或數(shù)組對象
item：遍歷時(shí)的每?個(gè)對象
open：語句塊開頭的字符串
close：語句塊結(jié)束的字符串
separator：每次遍歷之間間隔的字符串

void deleteByCondition(List<Integer> list);

<delete id="deleteByCondition">
     delete from userinfo
    where id in
    <foreach collection="list" item="id" open="(" close=")">
        #{id}
    </foreach>
</delete>

在這里插入圖片描述

<include>標(biāo)簽

在xml映射?件中配置的SQL，有時(shí)可能會存在很多重復(fù)的?段，此時(shí)就會存在很多冗余的代碼

在這里插入圖片描述

我們可以對重復(fù)的代碼?段進(jìn)?抽取，將其通過 <sql> 標(biāo)簽封裝到?個(gè)SQL?段，然后再通過
<include> 標(biāo)簽進(jìn)?引?。

ArrayList<UserInfo> selectAll();

<sql id="allColumn">
    id, username, age, gender, phone, delete_flag, create_time, update_time
</sql>

<select id="selectAll" resultType="com.example.mybatis20240101.model.UserInfo">
    select
    <include refid="allColumn"></include>
    from userinfo
</select>

在這里插入圖片描述