動態SQL語句

1.基本介紹

官方檔案 mybatis – MyBatis 3 | 動態 SQL

為什麼需要動態SQL？
1. 動態SQL是MyBatis的強大特性之一
2. 使用 JDBC 或其他類似的框架，根據不同條件拼接SQL語句非常麻煩，例如拼接時要確保不能忘記新增必要的空格，還要注意去掉列表最後一個列名的逗號等
3. SQL對映語句中的強大動態SQL語言可以很好地解決這個問題
基本介紹
1. 在一個實際的專案中，sql語句往往是比較複雜的
2. 為了滿足更加複雜的業務需求，MyBatis的設計者，提供了動態生成SQL的功能
動態SQL的必要性
1. 這裡有一些需求：查詢Monster時，如果程式設計師輸入的age不大於0，sql語句就不要帶age條件；更新記錄時，如果傳入的Monster物件沒有設定新的屬性值，資料庫表欄位就保持原來的值，設定了新的值，就更新
2. 解決方案：從上面的需求中可以看出，有時我們在生成sql語句時，在同一個方法中，還要根據不同的情況生成不同的sql語句，解決的辦法就是使用MyBatis提供的動態SQL機制
動態SQL常用標籤 -- 動態SQL提供瞭如下幾種常用的標籤，類似Java的控制語句：
1. if [判斷]
2. where [拼接where子句]
3. choose /when /otherwise [類似java的switch，注意是單分支]
4. foreach [類似in]
5. trim [替換關鍵字/客製化元素的功能]
6. set [在update的set中，可以保證進入set標籤的屬性被修改，沒有進入set的就保持原來的值]

2.動態SQL-案例演示

2.1if標籤

使用動態 SQL 最常見情景是，根據條件，用if標籤包含 where 子句的一部分。

例子：要求查詢age大於10的所有記錄，如果age不大於0，則輸出所有記錄。

<!--設定方法findMonsterByAge
    請查詢age大於10的所有記錄，如果程式設計師輸入的age不大於0，則輸出所有的記錄
    1.如果在test表示式中使用#{age}，是取不出值的
    2.要在介面宣告的入參使用@param註解為"age"-->
<select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
    SELECT * FROM `monster`
    <if test="age >= 0">
        WHERE `age`> #{age}
    </if>
</select>

當方法入參>=0時，底層的sql語句為：

當入參<0時，sql為：

2.2where標籤

where 標籤只會在子元素返回任何內容的情況下才插入「WHERE」子句。而且，若子句的開頭為「AND」或「OR」，where 元素也會將它們去除。

例子：查詢id>5，並且名字為「牛魔王」的所有記錄。如果名字為空，則不拼接帶名字的條件，如果id<0，則不拼接帶id的條件

<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
    SELECT * FROM `monster`
    <where>
        <if test="id>=0">
            id > #{id}
        </if>
        <if test="name !=null and name !=''">
            AND `name` = #{name}
        </if>
    </where>
</select>

如下，當id的值小於0時，會自動去除id子句和name子句前的AND

2.3choose/when/otherwise標籤

有時候，我們不想使用所有的條件，而只是想從多個條件中選擇一個使用。針對這種情況，MyBatis 提供了 choose 元素，它有點像 Java 中的 switch 語句。

<choose>
    <when></when>
    <when></when>
    <otherwise></otherwise>
</choose>

例子：

（1）如果給的name不為空，就按名字查詢妖怪

（2）如果指定的id>0，就按id來查詢妖怪

（3）如果前面兩個新增都不滿足，就預設查詢salary>100的

<select id="findMonsterByIdOrName" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <choose>
        <when test="name!=null and name != ''">
            WHERE `name` = #{name}
        </when>
        <when test="id >= 0 and id !=null">
            WHERE  `id` >= #{id}
        </when>
        <otherwise>
            WHERE  `salary` > 100
        </otherwise>
    </choose>
</select>

當入參name不符合條件時，選擇id的子句

當入參name和id都不符合條件時，預設選擇otherwise的子句

2.4foreach標籤

動態 SQL 的另一個常見使用場景是對集合進行遍歷（尤其是在構建 IN 條件語句的時候）。

foreach 元素的功能非常強大，它允許你指定一個集合，宣告可以在元素體內使用的集合項（item）和索引（index）變數。它也允許你指定開頭（open）與結尾（close）的字串以及集合項迭代之間的分隔符（separator），這個元素也不會錯誤地新增多餘的分隔符。

提示你可以將任何可迭代物件（如 List、Set 等）、Map 物件或者陣列物件作為集合引數傳遞給 foreach。當使用可迭代物件或者陣列時，index 是當前迭代的序號，item 的值是本次迭代獲取到的元素。當使用 Map 物件（或者 Map.Entry 物件的集合）時，index 是鍵，item 是值。

例子：查詢id為 10,12,14 的記錄

<!--1.先判斷集合是否為空，如果非空，則進行遍歷
    2.collection對應入參 map，item是遍歷的當前值value，open是sql的 ( ,
        separator是多個值之間的分隔符，close是 sql的 )
    3. #{id}對應 item="id"-->
<select id="findMonsterById_forEach" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <if test="ids !=null and ids != ''">
        <where>
            id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </if>
</select>

測試：

@Test
public void findMonsterById_forEach() {
    Map<String, Object> map = new HashMap<>();
    map.put("ids", Arrays.asList(10, 12, 14));//ids就是要遍歷的集合，這裡為List
    List<Monster> monsters =
            monsterMapper.findMonsterById_forEach(map);
    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }
    if (sqlSession != null) {
        sqlSession.close();
    }
}

2.5trim標籤（使用較少）

你可以通過自定義 trim 元素來客製化元素的功能。比如，和 where 元素等價的自定義 trim 元素為：

<trim prefix="WHERE" prefixOverrides="AND |OR">
  ...
</trim>

prefixOverrides 屬性會忽略通過管道符（|）分隔的文字序列（注意此例中的空格是必要的）。上述例子會移除所有 prefixOverrides 屬性中指定的內容，並且插入 prefix 屬性中指定的內容。我們也可以在 prefixOverride 屬性中新增其他文字序列，達到拓展where元素的功能。

此外，trim 元素還有 suffix、suffixOverrides 屬性，功能和 prefix、prefixOverride 類似。

例子：按照名字或年齡查詢記錄，如果sql語句開頭有 and | or 就替換成 where

<!--trim 的意義在於它可以替換你指定的任何字元-->
<select id="findMonsterByName_Trim" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <trim prefix="WHERE" prefixOverrides="and |or |HAHA">
        <if test="name != null and name !=''">
            HAHA `name` =#{name}
        </if>
        <if test="age != null and age !=''">
            OR `age` =#{age}
        </if>
    </trim>
</select>

2.6set標籤（重點）

set 元素可以用於動態包含需要更新的列，忽略其它不更新的列。比如：

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

這個例子中，set 元素會動態地在行首插入 SET 關鍵字，並會刪掉多餘的逗號（這些逗號是在使用條件語句給列賦值時引入的）。

你也可以使用trim標籤來達到set標籤的效果

例子：對指定id的記錄進行修改，如果沒有設定新的屬性，則保持原來的值

<update id="updateMonster_set" parameterType="map">
    UPDATE `monster`
    <set>
        <if test="age != null and age != ''">age`=#{age},</if>
        <if test="name != null and name != ''">`name`=#{name},</if>
        <if test="email != null and email != ''">`email`=#{email},</if>
        <if test="birthday != null and birthday != ''">`birthday`=#{birthday},</if>
        <if test="gender != null and gender != ''">`gender`=#{gender},</if>
        <if test="salary != null and salary != ''">`salary`=#{salary},</if>
    </set>
    WHERE `id`=#{id}
</update>

當入參只有id和name時，SQL語句如下，只有name欄位被修改了，其他的欄位仍保持原來的值。

3.練習

有一個hero表，欄位為id，nickname，skill，rank，salary，birthday

需求如下：

新增hero記錄
查詢rank大於30的所有記錄，如果輸入的rank不大於0，則輸出所有的hero-if
查詢rank為 3,6,10（rank可變）的hero記錄-foreach
修改hero資訊，如果沒有設定新的屬性值，則保持原來的值-set
根據id查詢hero，如果沒有傳入id，就返回所有的hero-where|choose

（1）建立表hero

-- 建立hero表
CREATE TABLE `hero`(
`id` INT NOT NULL AUTO_INCREMENT,
`nickname` VARCHAR(255) NOT NULL DEFAULT '',
`skill` VARCHAR(255) NOT NULL DEFAULT '',
`rank` INT NOT NULL,
`salary` DOUBLE NOT NULL,
`birthday` DATE NOT NULL,
PRIMARY KEY (`id`)
)CHARSET=utf8

（2）實體類Hero

package com.li.entity;

import lombok.*;

import java.util.Date;

/**
 * @author 李
 * @version 1.0
 */
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class Hero {
    private Integer id;
    private String nickname;
    private String skill;
    private Integer rank;
    private double salary;
    private Date birthday;
}

（3）介面宣告

package com.li.mapper;

import com.li.entity.Hero;

import java.util.List;

/**
 * @author 李
 * @version 1.0
 */
public interface HeroMapper {
    //1. 新增hero記錄,返回增長的id
    public void addHero(Hero hero);

    //2. 查詢rank大於30的所有記錄，如果輸入的rank不大於0，則返回所有的hero
    public List<Hero> findHeroByRank(Integer rank);

    //3. 查詢rank為 3,6,10（rank可變）的所有hero記錄
    public List<Hero> findHeroesByRanks(List<Integer> list);

    //4. 修改hero資訊，如果沒有設定新的屬性值，則保持原來的值
    public void updateHero(Hero hero);

    //5. 根據id查詢hero，如果沒有傳入id，就返回所有的hero
    public List<Hero> findHeroById(Integer id);
}

（4）對映檔案

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.li.mapper.HeroMapper">
    <!--1. 新增 hero記錄,返回增長的id-->
    <!--public void addHero(Hero hero);-->
    <insert id="addHero" parameterType="Hero" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `hero` (nickname,skill,rank,salary,birthday)
        VALUE(#{nickname},#{skill},#{rank},#{salary},#{birthday})
    </insert>

    <!--2. 查詢rank大於30的所有記錄，如果輸入的rank不大於0，則返回所有的hero-->
    <!--public List<Hero> findHeroByRank(Integer rank);-->
    <select id="findHeroByRank" parameterType="Integer" resultType="Hero">
        SELECT * FROM `hero`
        <if test="rank >=0 ">
            WHERE rank > #{rank}
        </if>
    </select>

    <!--3. 查詢rank為 3,6,10（rank可變）的所有hero記錄-->
    <!--public List<Hero> findHeroesByRanks(List<Integer> list);-->
    <select id="findHeroesByRanks" parameterType="list" resultType="Hero">
        SELECT * FROM `hero`
        <if test="list !=null">
            <where>
                rank IN
                <foreach collection="list" item="rank" open="(" separator="," close=")">
                    #{rank}
                </foreach>
            </where>
        </if>
    </select>

    <!--4. 修改hero資訊，如果沒有設定新的屬性值，則保持原來的值-->
    <!--public void updateHero(Hero hero);-->
    <update id="updateHero" parameterType="Hero">
        UPDATE `hero`
        <set>
            <if test="nickname !=null and nickname !=''">`nickname`=#{nickname},</if>
            <if test="skill !=null and skill !=''">`skill`=#{skill},</if>
            <if test="rank !=null and rank !=''">`rank`=#{rank},</if>
            <if test="salary !=null and salary !=''">`salary`=#{salary},</if>
            <if test="birthday !=null and birthday !=''">`birthday`=#{birthday},</if>
        </set>
        WHERE id = #{id}
    </update>

    <!--5. 根據id查詢hero，如果沒有傳入id，就返回所有的hero-->
    <!--public List<Hero> findHeroById(Integer id);-->
    <select id="findHeroById" parameterType="Integer" resultType="Hero">
        SELECT * FROM `hero`
        <!--也可以直接使用if-->
        <choose>
            <when test="id !=null and id !=''">
                WHERE id = #{id}
            </when>
        </choose>
    </select>
</mapper>

（5）測試略

day06-動態SQL語句