Mybatis多表查詢與動態(tài)SQL特性詳解

更新時間：2022年11月04日 15:53:27 作者：未見花聞

動態(tài)SQL可以省略很多拼接SQL的步驟,使用類似于JSTL方式,下面這篇文章主要給大家介紹了關(guān)于Mybatis多表查詢與動態(tài)SQL特性的相關(guān)資料,文字通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

1.較復(fù)雜的查詢操作

1.1 參數(shù)占位符 #{} 和 ${}

#{}:預(yù)處理符，如將id=#{2}替換為id=?，然后使用2替換?。

${}:替換符，如將id=${2}替換為id=2。

兩種占位符都可以正常使用的場合：傳入的參數(shù)類型是數(shù)值類型

使用${}：

select * from userinfo where id=${id}
select * from userinfo where id=2

使用#{}：

select * from userinfo where id=#{id}
select * from userinfo where id=?

對于這兩種參數(shù)占位符，個人建議能使用#{}就使用#{}，因?yàn)?code>${}存在SQL注入的問題，以及如果傳入的類型是字符串也會出類型。

只能使用#{}而不能使用${}的場合：傳入?yún)?shù)類型為String

使用${}：

select * from userinfo where username=${username}
//實(shí)際執(zhí)行的語句
Preparing: select * from userinfo where username=張三

但是在sql中通過字符串來查詢數(shù)據(jù)，是需要加上引號的，而使用${}生成的sql并沒有帶引號，因此不適用于字符串參數(shù)的sql。

使用#{}：

select * from userinfo where username=#{username}
//實(shí)際執(zhí)行語句
select * from userinfo where username=?

由于使用#{}是將目標(biāo)參數(shù)替換為占位符，然后利用JDBC中的占位符機(jī)制實(shí)現(xiàn)sql語句的填充，所以使用#{}構(gòu)造的sql是可以正常運(yùn)行的，并且沒有SQL注入的問題。

所以，#{}相比于${}，它支持所有類型的參數(shù)，包括數(shù)值類與字符串類，而${}支持?jǐn)?shù)值類型，不支持字符串類型的參數(shù)，但也可以在原來sql里面為${}外面加上一對引號。

select * from userinfo where username='${username}'
//實(shí)際執(zhí)行語句
Preparing: select * from userinfo where username='張三'

當(dāng)傳遞的參數(shù)為字符串類型的時候，雖然加上一對引號，使用${}也可以做到，但是${}存在SQL注入問題，所以仍然不推薦，有關(guān)SQL注入后面我們會介紹到。

大部分場合下，使用#{}都可以解決，但還是存在一小部分只能是${}來處理的。

如當(dāng)我們需要按照升序或者逆序得到數(shù)據(jù)庫查詢結(jié)果的時候，這種場合就只能使用${}，使用#{}會報錯，我們來演示一下。

首先，我們在Mapper接口中聲明一個方法：作用就是按照排序獲取結(jié)果集

public List<UserInfo> getOrderList(@Param(value = "order") String order);

我們再去xml文件中去寫sql語句：首先我們使用$進(jìn)行演示

    <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by createtime ${order};
    </select>

我們進(jìn)行一個單元測試，單元測試代碼很簡單，就是調(diào)用sql，然后得到結(jié)果集：

    @Test
    void getOrderList() {
        List<UserInfo> userMappers = userMapper.getOrderList("desc");
        System.out.println(userMappers);
    }

單元測試結(jié)果：

可以正常查詢，得到的結(jié)果與預(yù)期也是相同的。

我們再來試一試使用#{}來構(gòu)造sql語句：

    <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by createtime #{order};
    </select>

單元測試邏輯與代碼不變，我們再來看看單元測試執(zhí)行的一個結(jié)果：

我們發(fā)現(xiàn)程序報錯了，這是因?yàn)槭褂昧?code>desc的字符串替換了占位符，而我們所需要的不是一個desc字符串，而是直接一個desc的關(guān)鍵字，所以sql也拋出了語法錯誤，最終執(zhí)行的sql為：

select * from userinfo order by createtime ‘desc';

期望執(zhí)行的sql為：

select * from userinfo order by createtime desc;

所以在傳遞sql關(guān)鍵字的時候，不能使用#{}，只能使用${}。

1.2SQL注入

SQL注入就是使用${}，使用一些特殊的語句，來達(dá)到非法獲取數(shù)據(jù)的目的，如不通過正確的密碼獲取某賬戶的信息，下面我們來演示一下，就以登錄的例子來演示，SQL注入可以在不知道密碼的前提下登錄成功，并且獲取到用戶的相關(guān)信息。

首先我將數(shù)據(jù)庫只保留一個用戶信息，目的是為了方便演示SQL注入問題：

第一步，在Mapper接口中定義方法login，返回登錄成功的用戶對象。

public UserInfo login(@Param("username") String username, @Param(("password")) String password);

第二步，在xml文件中編寫SQL語句，我們需要演示SQL注入，所以我們使用${}來構(gòu)造sql語句。

    <select id="login" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username='${username}' and password='${password}';
    </select>

第三步，編寫測試類，我們在這個測試類中，傳入有注入問題的SQL語句' or 1='1，使得不需要密碼就能拿到相關(guān)的用戶信息。

    @Test
    void login() {
        String username = "admin";
        String password = "' or 1='1";

        UserInfo userInfo = userMapper.login(username, password);
        System.out.println(userInfo);
    }

單元測試運(yùn)行結(jié)果：

我們在不知道用戶密碼的情況下，登錄成功，并拿到了用戶的信息。

最終執(zhí)行的一段sql語句為：

select * from userinfo where username='admin' and password='' or 1='1';

相當(dāng)于它在原來?xiàng)l件判斷的語句下，后面有加上一個或的邏輯，并且或后面的表達(dá)式為true，這樣就使得原來的SQL語句中的條件判斷部分一定為真，所以就在密碼不知道的情況下拿到了用戶的基本信息。

所以我們能不使用#{}就不使用${}，因?yàn)榇嬖赟QL注入問題，如果必須使用${}則需要驗(yàn)證一下傳遞的參數(shù)是否合法，比如上面定義排序的sql，傳遞的參數(shù)只能是desc或者是asc，如果不是就不能執(zhí)行這條SQL，防止SQL注入的發(fā)生。

1.3like查詢

在Mybatis中使用like查詢比較特殊，因?yàn)橹苯邮褂?code>#{}會報錯，而使用${}，由于輸入的字符串情況很多，無法做到枚舉，驗(yàn)證比較困難，無法避免SQL注入問題。

首先，我們來演示使用#{}進(jìn)行like查詢，步驟我就不詳細(xì)寫了，就是查詢的步驟。

第一步，聲明方法。

public List<UserInfo> getListByName(@Param("username") String username);

第二步，xml寫sql。

    <select id="getListByName" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username like '%#{username}%'
    </select>

第三步，單元測試。

    @Test
    void getListByName() {
        String username = "a";
        List<UserInfo> list = userMapper.getListByName(username);
        for (UserInfo userInfo : list) {
            System.out.println(userInfo);
        }
    }

運(yùn)行結(jié)果：報錯了，因?yàn)?code>#{}會被替換成一個字符串，而在這個%#{username}%語句中#{username}不能帶上引號，帶上就違背SQL語法，造成錯誤。

這個時候，由于#{}多出一對引號，${}無法枚舉所有情況進(jìn)行驗(yàn)證會產(chǎn)生SQL注入，所以不能直接使用#{}，我們需要搭配MySQL內(nèi)置的字符串拼接語句concat。

我們將sql改為concat進(jìn)行字符串拼接：

    <select id="getListByName" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username like concat('%', #{username}, '%')
    </select>

重新測試一下，發(fā)現(xiàn)可以正常執(zhí)行了：

1.4resultType與resultMap

resultType表示數(shù)據(jù)庫返回的數(shù)據(jù)映射在java程序中所對應(yīng)的類型，只要定義類中的字段名與數(shù)據(jù)庫中表的字段名字一致就沒有任何問題，但是如果字段名存在沖突，則沖突的字段無法獲取到數(shù)據(jù)庫查詢的結(jié)果。

比如用戶名屬性在數(shù)據(jù)庫中的名字是username，而在java程序類中的屬性名為name，此時通過mybatis將數(shù)據(jù)傳遞到程序中的對象時，獲取到的name屬性為null，就不能正確地獲取到對應(yīng)的屬性值，為了解決這個數(shù)據(jù)庫字段與類中中字段不匹配的問題，我們需要使用到resultMap。

resultMap的使用方式就是在xml文件中設(shè)置<resultMap>標(biāo)簽，至少需要設(shè)置兩個屬性，一個是id表示你這個resultMap標(biāo)簽的名字，還有一個是type屬性，它表示映射到程序中類的類型，需包含包名。

這個標(biāo)簽里面需要設(shè)置至少兩個子標(biāo)簽，一個是id標(biāo)簽，另外一個是result標(biāo)簽，前者表示主鍵，后者表示數(shù)據(jù)庫表中普通的列，這兩種標(biāo)簽也是至少需要設(shè)置兩個屬性，一個是column表示數(shù)據(jù)庫表中的字段名，另外一個是property表示程序類中的字段名，如果只是在單表進(jìn)行查詢，只設(shè)置不同字段名的映射就可以了，但是如果是多表查詢，必須將數(shù)據(jù)表中所有的字段與類中所有的字段生成映射關(guān)系。

就像下面這樣，圖中類與數(shù)據(jù)表字段是相同的，實(shí)際情況會存在不同的字段名：

1.4多表查詢

1.4.1一對一表映射

一對一關(guān)系就是對于一個屬性只與另外一個屬性有關(guān)系的映射，這就是一對一的關(guān)系，舉個例子，對于一篇博客，它只會對應(yīng)到一個用戶，則博客與用戶的關(guān)系是一對一的關(guān)系，下面我們嘗試在mybatis中實(shí)現(xiàn)一對一多表聯(lián)查。

那么，首先我們先將數(shù)據(jù)庫的博客表與查程序中的博客類對應(yīng)起來，就是按照數(shù)據(jù)庫中的博客表建立一個類：

@Data
public class Articleinfo {
    private Integer id;
    private String title;
    private String content;
    private String createtime;
    private String updatetime;
    private Integer uid;
    private Integer rcount;
    private Integer state;
    //不妨多一個屬性，用戶表
    private UserInfo userInfo;
}

目前文章表中只有一條數(shù)據(jù)，如下圖：

第二步，創(chuàng)建Mapper接口和對應(yīng)的xml文件。

第三步，在接口中聲明方法和在xml中寫sql標(biāo)簽與語句。

//根據(jù)文章名稱獲取文章對象
public Articleinfo getArticleById(@Param("id") Integer id);

    <select id="getArticleById" resultType="com.example.demo.model.Articleinfo">
        select * from articleinfo where id=#{id};
    </select>

第四步，編寫測試方法，我們直接調(diào)用查詢方法，然后使用日志輸出對象。

    @Test
    void getArticleById() {
        Articleinfo articleinfo = articleMapper.getArticleById(1);
        log.info("文章詳情：" + articleinfo);
    }

由于我們數(shù)據(jù)表與類的字段名是一致的，那些普通的屬性都一一對應(yīng)上了，都成功被賦值了，但是由于UserInfo類在數(shù)據(jù)表中沒有，所以并沒有得到UserInfo對象，如果我們想要拿到這個對象，我們得使用resultMap。

問題主要有兩個，第一，數(shù)據(jù)庫查詢到的用戶表沒有映射到UserInfo對象，第二，查詢的SQL語句是單表查詢語句，不是多表查詢語句。

所以想要實(shí)現(xiàn)一對一多表查詢，需要設(shè)置多表查詢SQL語句，我們使用左外連接進(jìn)行多表查詢：

    <select id="getArticleById" resultMap="BaseMap">
        select a.*, u.* from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{id};
    </select>

此外，我們除了設(shè)置UserInfo與Articleinfo類中每個屬性與數(shù)據(jù)表的映射之外，我們還要在Articleinfo類對應(yīng)的resultMap中使用association標(biāo)簽。最少需要設(shè)置兩個屬性，一個是property表示在主表Articleinfo中對應(yīng)副表UserInfo映射對象的變量名，另外一個是副表UserInfo對應(yīng)的resultMap。

Articleinfo類對應(yīng)的resultMap：

    <resultMap id="BaseMap" type="com.example.demo.model.Articleinfo">
        <id column="id" property="id"></id>
        <result column="title" property="title"></result>
        <result column="content" property="content"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="uid" property="uid"></result>
        <result column="rcount" property="rcount"></result>
        <result column="state" property="state"></result>

        <association property="userInfo" resultMap="com.example.demo.mapper.UserMapper.BaseMap"></association>
    </resultMap>

UserInfo類對應(yīng)的resultMap：

    <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
<!--        column 表示數(shù)據(jù)庫字段名 property 表示對應(yīng)對象的字段名，設(shè)置這兩個值可以建立映射-->
<!--        主鍵約束-->
        <id column="id" property="id"></id>
<!--普通變量映射-->
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="photo" property="photo"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="state" property="state"></result>
    </resultMap>

如果UserInfo類的resultMap沒有將所有的屬性都與數(shù)據(jù)庫的表映射，就會造成獲取到的userInfo對象中的數(shù)據(jù)不完整，假設(shè)只設(shè)置了id與name的映射，那獲取到的對象只有id與name有值。

將兩張表的resultMap映射好后，我們運(yùn)行同樣的單元測試案例，運(yùn)行結(jié)果如下：

但是，仍然存在一個問題，那就是我們所建的兩個表存在名字相同的字段，可能會出現(xiàn)數(shù)據(jù)覆蓋的情況，如兩個表的主鍵都叫id，但是id在兩個表的含義是不同的，在用戶表它表示用戶id，在文章表它表示文章的id，現(xiàn)在我們將獲取兩表的數(shù)據(jù)的id改為不相同，再來看一看單元測試運(yùn)行的結(jié)果：

按理來說，由于不存在id為1的用戶，所以獲取到UserInfo對象應(yīng)該為null才對，但是運(yùn)行的結(jié)果卻存在UserInfo對象，并且與文章表的重名字段都被賦值了文章表中的數(shù)據(jù)，為了解決這個問題，我們必須在文章表（主表）的resultMap中設(shè)置屬性columnPrefix，它的值隨便設(shè)置，作用是識別副表字段時加上一段前綴，如我們給用戶表的字段加上前綴u_，此時sql中就不能使用*來一次表示所有元素了，需要一個一個單獨(dú)設(shè)置，并將字段全部重命名，帶上u_前綴。

association字段設(shè)置：

<association property="userInfo" columnPrefix="u_" resultMap="com.example.demo.mapper.UserMapper.BaseMap" ></association>

SQL語句需要將用戶表的字段全部重命名：

    <select id="getArticleById" resultMap="BaseMap">
        select a.*, u.id as u_id,
               u.username as u_username,
               u.password as u_password,
               u.photo as u_photo,
               u.createtime as u_createtime,
               u.updatetime as u_updatetime,
               u.state as u_state
        from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{id};
    </select>

我們將userInfo對應(yīng)的用戶表的id再改回為1，讓查詢有關(guān)于UserInfo類的數(shù)據(jù)。

再次運(yùn)行單元測試案例：

我們是能夠獲取到相應(yīng)的數(shù)據(jù)的，所以如果兩個表字段重名了，進(jìn)行多表查詢時，需要設(shè)置columnPrefix屬性，這樣才能夠避免不同表同名字段數(shù)據(jù)覆蓋的問題。

所以，在創(chuàng)建數(shù)據(jù)庫的數(shù)據(jù)表時，盡量不要讓表與表中的字段重名。

1.4.2一對多表映射

一對多的關(guān)系，就是對于一個屬性，它對映著多個其他的屬性，比如用戶與博客之間的關(guān)系，一個用戶可以對應(yīng)多篇博客，則用戶與博客之間的關(guān)系就是一對多的關(guān)系。同樣的下面我們嘗試使用mybatis實(shí)現(xiàn)多對多的多表聯(lián)查。

下面我們以用戶表為主，文章表為輔，來演示如何進(jìn)行一對多關(guān)系的多表查詢。

既然是一對多的關(guān)系，那我們可以在UserInfo類中加上一個儲存ArticleInfo對象的List，來儲存用戶發(fā)布或所寫的文章。

@Data
public class UserInfo {
    private Integer id;
    private String username;
    private String password;
    private String photo;
    private String createtime;
    private String updatetime;
    private Integer state;
    private List<Articleinfo> aList;
}

實(shí)現(xiàn)多表查詢的大致過程如下：

在Mapper接口中聲明方法，我們聲明一個方法，就是通過用戶id獲取用戶信息以及對應(yīng)的文章列表。
在xml文件當(dāng)中寫resultMap映射關(guān)系，與一對一多表查詢不同的是，我們需要設(shè)置collection標(biāo)簽，而不是association標(biāo)簽。
在xml文件的resultMap標(biāo)簽中至少設(shè)置resultMap名字id，對應(yīng)映射的類type等屬性，里面需要設(shè)置數(shù)據(jù)表與類中所有字段的映射，以及設(shè)置collection標(biāo)簽，需要設(shè)置property屬性表示需映射的對象名，設(shè)置resultMap即副表的resultMap路徑，由于你無法保證表與表之間是否存在重名字段，需要設(shè)置columnPrefix為副表的字段添加上一個前綴，防止重名數(shù)據(jù)覆蓋。

        <collection
                property="aList"
                resultMap="com.example.demo.mapper.ArticleMapper.BaseMap"
                columnPrefix="a_">
        </collection>

在對應(yīng)的xml文件當(dāng)中寫SQL標(biāo)簽以及語句。

    <select id="getUserAndArticlesById" resultMap="BaseMap">
        select u.*, 
               a.id as a_id,
               a.title as a_title,
               a.content as a_content,
               a.createtime as a_createtime,
               a.updatetime as a_updatetime,
               a.uid as a_uid,
               a.rcount as a_rcount,
               a.state as a_state
               from userinfo as u left join articleinfo as a on u.id=a.uid where u.id=#{uid}
    </select>

編寫單元測試代碼，測試代碼是否編寫正確。

    @Test
    void getUserAndArticlesById() {
        Integer id = 1;
        UserInfo userInfo = userMapper.getUserAndArticlesById(id);
        log.info("用戶信息：" + userInfo);
    }

運(yùn)行結(jié)果：

2.動態(tài)SQL

首先來說一下什么是動態(tài)SQL，官方文檔對于動態(tài)SQL的定義是：

動態(tài) SQL 是 MyBatis 的強(qiáng)大特性之一。如果你使用過 JDBC 或其它類似的框架，你應(yīng)該能理解根據(jù)不同條件拼接 SQL 語句有多痛苦，例如拼接時要確保不能忘記添加必要的空格，還要注意去掉列表最后一個列名的逗號。利用動態(tài) SQL，可以徹底擺脫這種痛苦。使用動態(tài) SQL 并非一件易事，但借助可用于任何 SQL 映射語句中的強(qiáng)大的動態(tài) SQL 語言，MyBatis 顯著地提升了這一特性的易用性。

前面所說的mybatis增刪查改，那些傳入的參數(shù)都是一定會傳入的，但是在實(shí)際情況中，很多參數(shù)都是非必傳參數(shù)，使用動態(tài)SQL就可以解決傳入的參數(shù)是非必傳參數(shù)的情況。

動態(tài)SQL可以解決多余符號的問題，如,等。

2.1if標(biāo)簽

if標(biāo)簽的作用就是判斷一個參數(shù)是否有值，如果沒有值就將對應(yīng)的參數(shù)隱藏。

語法：

<if test="表達(dá)式">
	sql
</if>
//例如
<if test="參數(shù)!=null">
	sql部分語句
</if>

當(dāng)表達(dá)式為真，則插入if標(biāo)簽中的sql，否則不插入。

我們以在用戶表中插入一條數(shù)據(jù)為例，插入的數(shù)據(jù)中頭像photo不是必傳的參數(shù)：

方法聲明：

    //使用動態(tài)sql插入數(shù)據(jù)
    public int addUser(UserInfo userInfo);

動態(tài)SQL語句：

其中的photo是非必傳參數(shù)，我們使用if標(biāo)簽來判斷它是否有值，沒有值就不插入目標(biāo)的SQL語句。

    <insert id="addUser">
        insert into userinfo(username, password
        <if test="photo!=null">
            , photo
        </if>
        ) values(#{username}, #{password}
        <if test="photo!=null">
            , #{photo}
        </if>
        )
    </insert>

單元測試代碼：

    @Test
    void addUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("張三瘋");
        userInfo.setPassword("123456");

        int res = userMapper.addUser(userInfo);
        log.info("受影響的行數(shù)為:" + res);
    }

在單元測試代碼中，我沒有給photo賦值，if標(biāo)簽會判斷它為空，不會插入對應(yīng)photo的SQL，因此插入數(shù)據(jù)photo為默認(rèn)值。

結(jié)果：

數(shù)據(jù)庫查詢結(jié)果：

再來試一試給photo傳值的情況，它生成的SQL有三個參數(shù)：

    @Test
    void addUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("張無忌");
        userInfo.setPassword("12345611");
        userInfo.setPhoto("張無忌.png");
        int res = userMapper.addUser(userInfo);
        log.info("受影響的行數(shù)為:" + res);
    }

運(yùn)行結(jié)果：

最終生成的語句多了一個photo參數(shù)。

2.2trim標(biāo)簽

前面所說的if標(biāo)簽可以實(shí)現(xiàn)非必傳參數(shù)SQL的構(gòu)造，在極端情況下，有很多個非必傳參數(shù)，此時如果只使用if標(biāo)簽構(gòu)造出的SQL語句很有可能會多出一個,，因?yàn)橛泻芏喾潜貍鲄?shù)，如果只傳來一個參數(shù)，由于不確定后面是否還會有參數(shù)，因此會預(yù)留一個,，此時如果沒有其他參數(shù)，就會多出一個參數(shù)。

而trim標(biāo)簽可以做到這一點(diǎn)，它可以去除SQL語句前后多余的某個字符，它需要搭配if標(biāo)簽使用。

<trim>標(biāo)簽中有如下屬性：

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

語法：

<trim prefix="前綴符", suffix="后綴符", prefixOverrides="去除多余的前綴字符", suffixOverrides="去除多余的后綴字符">
	<if test="表達(dá)式">
		...
	</if>
	...
	...
</trim>

假設(shè)username password photo都是非必傳參數(shù)，但是至少傳遞一個，我們來寫插入語句的動態(tài)SQL。

    <insert id="addUser2">
        insert into userinfo
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="photo!=null">
                photo
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
            <if test="photo!=null">
                #{photo}
            </if>
        </trim>
    </insert>

單元測試代碼：

    @Test
    void addUser2() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("wangwu");
        userInfo.setPassword("12345622");
        int res = userMapper.addUser(userInfo);
        log.info("受影響的行數(shù)為:" + res);
    }

運(yùn)行結(jié)果與生成的SQL語句：

我們發(fā)現(xiàn)逗號它自動去除了。

2.3where標(biāo)簽

where標(biāo)簽主要是實(shí)現(xiàn)where關(guān)鍵字的替換，如果SQL中沒有使用到where（沒有查詢條件），就會隱藏，存在查詢條件，就會生成含有where的查詢SQL語句，并且可以去除前面的and。

我們就不以復(fù)雜的案例作為演示了，直接寫一個最簡單的查詢，為了簡單，我們刪除數(shù)據(jù)庫的其他數(shù)據(jù)，只留admin一條數(shù)據(jù)。

下面我們來寫最簡單的查詢語句，就是根據(jù)id獲取一個用戶信息。

寫動態(tài)SQL時，我故意在最前面寫一個and來證明where標(biāo)簽是可以自動刪除前面多余的and。

    <select id="getUserById" resultType="com.example.demo.model.UserInfo">
        select * from userinfo
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
        </where>
    </select>

單元測試代碼：

    @Test
    void gerUserById() {
        UserInfo userInfo = userMapper.getUserById(1);
        System.out.println(userInfo);
        //Assertions.assertNotNull(userInfo);
    }

結(jié)果：

發(fā)現(xiàn)自動生成了where語句并刪除了多余的and。

如果我們查詢一個null值，就不會生成where語句。

以上<where>標(biāo)簽也可以使用 <trim prefix="where" prefixOverrides="and"> 替換。

2.4set標(biāo)簽

其實(shí)set標(biāo)簽與where標(biāo)簽很相似，只不過where用來替換查詢SQL，set用于修改SQL中，用來自動生成set部分的SQL語句。

set標(biāo)簽還可以自動去除最后面的一個,。

比如我們寫一個能夠修改賬戶名username，密碼password，頭像photo的動態(tài)SQL，根據(jù)id進(jìn)行修改。

方法聲明：

    //使用動態(tài)SQL實(shí)現(xiàn)修改用戶信息，包括賬戶名，密碼，頭像
    public int updateUser(UserInfo userInfo);

動態(tài)SQL：

    <update id="updateUser">
        update userinfo
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="photo!=null">
                photo=#{photo}
            </if>
        </set>
        where id=#{id}
    </update>

單元測試代碼：

    @Test
    void updateUser() {
        UserInfo userInfo = new UserInfo();
        //修改密碼為123456
        userInfo.setPassword("123456");
        userInfo.setId(1);
        int res = userMapper.updateUser(userInfo);
        log.info("受影響的行數(shù)：" + res);
    }

運(yùn)行結(jié)果：

修改成功并且可以根據(jù)傳入?yún)?shù)個數(shù)自動生成相應(yīng)的修改SQL，以及可以自動去除最后的,。

以上<set>標(biāo)簽也可以使用 <trim prefix="set" suffixOverrides=","> 替換。

2.5foreach標(biāo)簽

對集合進(jìn)行遍歷可以使用foreach標(biāo)簽，常用的場景有批量刪除功能。

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

為了方便演示批量刪除，我們隨便插入幾條數(shù)據(jù)到數(shù)據(jù)庫：

方法聲明：

    //使用動態(tài)sql批量刪除元素
    public int deleteIds(List<Integer> ids);

動態(tài)SQL語句：

    <delete id="deleteIds">`在這里插入代碼片`
        delete from userinfo where id in
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </delete>

單元測試代碼：

刪除數(shù)據(jù)庫中id為10 11 12的用戶。

    @Test
    void deleteIds() {
        List<Integer> ids = new ArrayList<>();
        ids.add(10);
        ids.add(11);
        ids.add(12);
        
        int res = userMapper.deleteIds(ids);
        log.info("受影響的行數(shù)" + res);
    }

運(yùn)行結(jié)果：