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