Mybatis多表查詢與動態SQL特性詳解

2022-11-05 14:00:32

1.較複雜的查詢操作

1.1 引數預留位置 #{} 和 ${}

#{}:預處理符，如將id=#{2}替換為id=?，然後使用2替換?。

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

兩種預留位置都可以正常使用的場合：傳入的引數型別是數值型別

使用${}：

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

使用#{}：

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

對於這兩種引數預留位置，個人建議能使用#{}就使用#{}，因為${}存在SQL隱碼攻擊的問題，以及如果傳入的型別是字串也會出型別。

只能使用#{}而不能使用${}的場合：傳入引數型別為String

使用${}：

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

但是在sql中通過字串來查詢資料，是需要加上引號的，而使用${}生成的sql並沒有帶引號，因此不適用於字串引數的sql。

使用#{}：

select * from userinfo where username=#{username}
//實際執行語句
select * from userinfo where username=?

由於使用#{}是將目標引數替換為預留位置，然後利用JDBC中的預留位置機制實現sql語句的填充，所以使用#{}構造的sql是可以正常執行的，並且沒有SQL隱碼攻擊的問題。

所以，#{}相比於${}，它支援所有型別的引數，包括數值類與字串類，而${}支援數值型別，不支援字串型別的引數，但也可以在原來sql裡面為${}外面加上一對引號。

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

當傳遞的引數為字串型別的時候，雖然加上一對引號，使用${}也可以做到，但是${}存在SQL隱碼攻擊問題，所以仍然不推薦，有關SQL隱碼攻擊後面我們會介紹到。

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

如當我們需要按照升序或者逆序得到資料庫查詢結果的時候，這種場合就只能使用${}，使用#{}會報錯，我們來演示一下。

首先，我們在Mapper介面中宣告一個方法：作用就是按照排序獲取結果集

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

我們再去xml檔案中去寫sql語句：首先我們使用$進行演示

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

我們進行一個單元測試，單元測試程式碼很簡單，就是呼叫sql，然後得到結果集：

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

單元測試結果：

可以正常查詢，得到的結果與預期也是相同的。

我們再來試一試使用#{}來構造sql語句：

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

單元測試邏輯與程式碼不變，我們再來看看單元測試執行的一個結果：

我們發現程式報錯了，這是因為使用了desc的字串替換了預留位置，而我們所需要的不是一個desc字串，而是直接一個desc的關鍵字，所以sql也丟擲了語法錯誤，最終執行的sql為：

select * from userinfo order by createtime ‘desc';

期望執行的sql為：

select * from userinfo order by createtime desc;

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

1.2SQL隱碼攻擊

SQL隱碼攻擊就是使用${}，使用一些特殊的語句，來達到非法獲取資料的目的，如不通過正確的密碼獲取某賬戶的資訊，下面我們來演示一下，就以登入的例子來演示，SQL隱碼攻擊可以在不知道密碼的前提下登入成功，並且獲取到使用者的相關資訊。

首先我將資料庫只保留一個使用者資訊，目的是為了方便演示SQL隱碼攻擊問題：

第一步，在Mapper介面中定義方法login，返回登入成功的使用者物件。

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

第二步，在xml檔案中編寫SQL語句，我們需要演示SQL隱碼攻擊，所以我們使用${}來構造sql語句。

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

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

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

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

單元測試執行結果：

我們在不知道使用者密碼的情況下，登入成功，並拿到了使用者的資訊。

最終執行的一段sql語句為：

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

相當於它在原來條件判斷的語句下，後面有加上一個或的邏輯，並且或後面的表示式為true，這樣就使得原來的SQL語句中的條件判斷部分一定為真，所以就在密碼不知道的情況下拿到了使用者的基本資訊。

所以我們能不使用#{}就不使用${}，因為存在SQL隱碼攻擊問題，如果必須使用${}則需要驗證一下傳遞的引數是否合法，比如上面定義排序的sql，傳遞的引數只能是desc或者是asc，如果不是就不能執行這條SQL，防止SQL隱碼攻擊的發生。

1.3like查詢

在Mybatis中使用like查詢比較特殊，因為直接使用#{}會報錯，而使用${}，由於輸入的字串情況很多，無法做到列舉，驗證比較困難，無法避免SQL隱碼攻擊問題。

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

第一步，宣告方法。

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);
        }
    }

執行結果：報錯了，因為#{}會被替換成一個字串，而在這個%#{username}%語句中#{username}不能帶上引號，帶上就違背SQL語法，造成錯誤。

這個時候，由於#{}多出一對引號，${}無法列舉所有情況進行驗證會產生SQL隱碼攻擊，所以不能直接使用#{}，我們需要搭配MySQL內建的字串拼接語句concat。

我們將sql改為concat進行字串拼接：

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

重新測試一下，發現可以正常執行了：

1.4resultType與resultMap

resultType表示資料庫返回的資料對映在java程式中所對應的型別，只要定義類中的欄位名與資料庫中表的欄位名字一致就沒有任何問題，但是如果欄位名存在衝突，則衝突的欄位無法獲取到資料庫查詢的結果。

比如使用者名稱屬性在資料庫中的名字是username，而在java程式類中的屬性名為name，此時通過mybatis將資料傳遞到程式中的物件時，獲取到的name屬性為null，就不能正確地獲取到對應的屬性值，為了解決這個資料庫欄位與類中中欄位不匹配的問題，我們需要使用到resultMap。

resultMap的使用方式就是在xml檔案中設定<resultMap>標籤，至少需要設定兩個屬性，一個是id表示你這個resultMap標籤的名字，還有一個是type屬性，它表示對映到程式中類的型別，需包含包名。

這個標籤裡面需要設定至少兩個子標籤，一個是id標籤，另外一個是result標籤，前者表示主鍵，後者表示資料庫表中普通的列，這兩種標籤也是至少需要設定兩個屬性，一個是column表示資料庫表中的欄位名，另外一個是property表示程式類中的欄位名，如果只是在單表進行查詢，只設定不同欄位名的對映就可以了，但是如果是多表查詢，必須將資料表中所有的欄位與類中所有的欄位生成對映關係。

就像下面這樣，圖中類與資料表欄位是相同的，實際情況會存在不同的欄位名：

1.4多表查詢

1.4.1一對一表對映

一對一關係就是對於一個屬性只與另外一個屬性有關係的對映，這就是一對一的關係，舉個例子，對於一篇部落格，它只會對應到一個使用者，則部落格與使用者的關係是一對一的關係，下面我們嘗試在mybatis中實現一對一多表聯查。

那麼，首先我們先將資料庫的部落格表與查程式中的部落格類對應起來，就是按照資料庫中的部落格表建立一個類：

@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;
}

目前文章表中只有一條資料，如下圖：

第二步，建立Mapper介面和對應的xml檔案。

第三步，在介面中宣告方法和在xml中寫sql標籤與語句。

//根據文章名稱獲取文章物件
public Articleinfo getArticleById(@Param("id") Integer id);

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

第四步，編寫測試方法，我們直接呼叫查詢方法，然後使用紀錄檔輸出物件。

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

由於我們資料表與類的欄位名是一致的，那些普通的屬性都一一對應上了，都成功被賦值了，但是由於UserInfo類在資料表中沒有，所以並沒有得到UserInfo物件，如果我們想要拿到這個物件，我們得使用resultMap。

問題主要有兩個，第一，資料庫查詢到的使用者表沒有對映到UserInfo物件，第二，查詢的SQL語句是單表查詢語句，不是多表查詢語句。

所以想要實現一對一多表查詢，需要設定多表查詢SQL語句，我們使用左外連線進行多表查詢：

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

此外，我們除了設定UserInfo與Articleinfo類中每個屬性與資料表的對映之外，我們還要在Articleinfo類對應的resultMap中使用association標籤。最少需要設定兩個屬性，一個是property表示在主表Articleinfo中對應副表UserInfo對映物件的變數名，另外一個是副表UserInfo對應的resultMap。

Articleinfo類對應的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類對應的resultMap：

    <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
<!--        column 表示資料庫欄位名 property 表示對應物件的欄位名，設定這兩個值可以建立對映-->
<!--        主鍵約束-->
        <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沒有將所有的屬性都與資料庫的表對映，就會造成獲取到的userInfo物件中的資料不完整，假設只設定了id與name的對映，那獲取到的物件只有id與name有值。

將兩張表的resultMap對映好後，我們執行同樣的單元測試案例，執行結果如下：

但是，仍然存在一個問題，那就是我們所建的兩個表存在名字相同的欄位，可能會出現資料覆蓋的情況，如兩個表的主鍵都叫id，但是id在兩個表的含義是不同的，在使用者表它表示使用者id，在文章表它表示文章的id，現在我們將獲取兩表的資料的id改為不相同，再來看一看單元測試執行的結果：

按理來說，由於不存在id為1的使用者，所以獲取到UserInfo物件應該為null才對，但是執行的結果卻存在UserInfo物件，並且與文章表的重名欄位都被賦值了文章表中的資料，為了解決這個問題，我們必須在文章表（主表）的resultMap中設定屬性columnPrefix，它的值隨便設定，作用是識別副表欄位時加上一段字首，如我們給使用者表的欄位加上字首u_，此時sql中就不能使用*來一次表示所有元素了，需要一個一個單獨設定，並將欄位全部重新命名，帶上u_字首。

association欄位設定：

<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對應的使用者表的id再改回為1，讓查詢有關於UserInfo類的資料。

再次執行單元測試案例：

我們是能夠獲取到相應的資料的，所以如果兩個表欄位重名了，進行多表查詢時，需要設定columnPrefix屬性，這樣才能夠避免不同表同名欄位資料覆蓋的問題。

所以，在建立資料庫的資料表時，儘量不要讓表與表中的欄位重名。

1.4.2一對多表對映

一對多的關係，就是對於一個屬性，它對映著多個其他的屬性，比如使用者與部落格之間的關係，一個使用者可以對應多篇部落格，則使用者與部落格之間的關係就是一對多的關係。同樣的下面我們嘗試使用mybatis實現多對多的多表聯查。

下面我們以使用者表為主，文章表為輔，來演示如何進行一對多關係的多表查詢。

既然是一對多的關係，那我們可以在UserInfo類中加上一個儲存ArticleInfo物件的List，來儲存使用者釋出或所寫的文章。

@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;
}

實現多表查詢的大致過程如下：

在Mapper介面中宣告方法，我們宣告一個方法，就是通過使用者id獲取使用者資訊以及對應的文章列表。
在xml檔案當中寫resultMap對映關係，與一對一多表查詢不同的是，我們需要設定collection標籤，而不是association標籤。
在xml檔案的resultMap標籤中至少設定resultMap名字id，對應對映的類type等屬性，裡面需要設定資料表與類中所有欄位的對映，以及設定collection標籤，需要設定property屬性表示需對映的物件名，設定resultMap即副表的resultMap路徑，由於你無法保證表與表之間是否存在重名欄位，需要設定columnPrefix為副表的欄位新增上一個字首，防止重名資料覆蓋。

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

在對應的xml檔案當中寫SQL標籤以及語句。

    <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);
    }

執行結果：

2.動態SQL

首先來說一下什麼是動態SQL，官方檔案對於動態SQL的定義是：

動態 SQL 是 MyBatis 的強大特性之一。如果你使用過 JDBC 或其它類似的框架，你應該能理解根據不同條件拼接 SQL 語句有多痛苦，例如拼接時要確保不能忘記新增必要的空格，還要注意去掉列表最後一個列名的逗號。利用動態 SQL，可以徹底擺脫這種痛苦。使用動態 SQL 並非一件易事，但藉助可用於任何 SQL 對映語句中的強大的動態 SQL 語言，MyBatis 顯著地提升了這一特性的易用性。

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

動態SQL可以解決多餘符號的問題，如,等。

2.1if標籤

if標籤的作用就是判斷一個引數是否有值，如果沒有值就將對應的引數隱藏。

語法：

<if test="表示式">
	sql
</if>
//例如
<if test="引數!=null">
	sql部分語句
</if>

當表示式為真，則插入if標籤中的sql，否則不插入。

我們以在使用者表中插入一條資料為例，插入的資料中頭像photo不是必傳的引數：

方法宣告：

    //使用動態sql插入資料
    public int addUser(UserInfo userInfo);

動態SQL語句：

其中的photo是非必傳引數，我們使用if標籤來判斷它是否有值，沒有值就不插入目標的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("受影響的行數為:" + res);
    }

在單元測試程式碼中，我沒有給photo賦值，if標籤會判斷它為空，不會插入對應photo的SQL，因此插入資料photo為預設值。

結果：

資料庫查詢結果：

再來試一試給photo傳值的情況，它生成的SQL有三個引數：

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

執行結果：

最終生成的語句多了一個photo引數。

2.2trim標籤

前面所說的if標籤可以實現非必傳引數SQL的構造，在極端情況下，有很多個非必傳引數，此時如果只使用if標籤構造出的SQL語句很有可能會多出一個,，因為有很多非必傳引數，如果只傳來一個引數，由於不確定後面是否還會有引數，因此會預留一個,，此時如果沒有其他引數，就會多出一個引數。

而trim標籤可以做到這一點，它可以去除SQL語句前後多餘的某個字元，它需要搭配if標籤使用。

<trim>標籤中有如下屬性：

prefix：表示整個語句塊，以prefix的值作為字首
suffix：表示整個語句塊，以suffix的值作為字尾
prefixOverrides：表示整個語句塊要去除掉的字首
suffixOverrides：表示整個語句塊要去除掉的字尾

語法：

<trim prefix="字首符", suffix="字尾符", prefixOverrides="去除多餘的字首字元", suffixOverrides="去除多餘的字尾字元">
	<if test="表示式">
		...
	</if>
	...
	...
</trim>

假設username password photo都是非必傳引數，但是至少傳遞一個，我們來寫插入語句的動態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("受影響的行數為:" + res);
    }

執行結果與生成的SQL語句：

我們發現逗號它自動去除了。

2.3where標籤

where標籤主要是實現where關鍵字的替換，如果SQL中沒有使用到where（沒有查詢條件），就會隱藏，存在查詢條件，就會生成含有where的查詢SQL語句，並且可以去除前面的and。

我們就不以複雜的案例作為演示了，直接寫一個最簡單的查詢，為了簡單，我們刪除資料庫的其他資料，只留admin一條資料。

下面我們來寫最簡單的查詢語句，就是根據id獲取一個使用者資訊。

寫動態SQL時，我故意在最前面寫一個and來證明where標籤是可以自動刪除前面多餘的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);
    }

結果：

發現自動生成了where語句並刪除了多餘的and。

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

以上<where>標籤也可以使用 <trim prefix="where" prefixOverrides="and"> 替換。

2.4set標籤

其實set標籤與where標籤很相似，只不過where用來替換查詢SQL，set用於修改SQL中，用來自動生成set部分的SQL語句。

set標籤還可以自動去除最後面的一個,。

比如我們寫一個能夠修改賬戶名username，密碼password，頭像photo的動態SQL，根據id進行修改。

方法宣告：

    //使用動態SQL實現修改使用者資訊，包括賬戶名，密碼，頭像
    public int updateUser(UserInfo userInfo);

動態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("受影響的行數：" + res);
    }

執行結果：

修改成功並且可以根據傳入引數個數自動生成相應的修改SQL，以及可以自動去除最後的,。

以上<set>標籤也可以使用 <trim prefix="set" suffixOverrides=","> 替換。

2.5foreach標籤

對集合進行遍歷可以使用foreach標籤，常用的場景有批次刪除功能。

collection：繫結方法引數中的集合，如 List，Set，Map或陣列物件
item：遍歷時的每一個物件
open：語句塊開頭的字串
close：語句塊結束的字串
separator：每次遍歷之間間隔的字串

為了方便演示批次刪除，我們隨便插入幾條資料到資料庫：

方法宣告：

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

動態SQL語句：

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

單元測試程式碼：

刪除資料庫中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("受影響的行數" + res);
    }

執行結果：