作者：阿進的寫字檯
來源：http://www.cnblogs.com/homejim/p/9909657.html

0 一起來學習 mybatis

MyBatis 令人喜歡的一大特性就是動態 SQL。 在使用 JDBC 的過程中， 根據條件進行 SQL 的拼接是很麻煩且很容易出錯的。 MyBatis 動態 SQL 的出現， 解決了這個麻煩。

MyBatis通過 OGNL 來進行動態 SQL 的使用的。

目前， 動態 SQL 支持以下幾種標籤

1 數據準備

為了後面的演示， 創建了一個 Maven 項目 mybatis-dynamic, 創建了對應的數據庫和表

對應的項目結構

2 if 標籤

if 標籤是我們最常使用的。 在查詢、刪除、更新的時候很可能會使用到。 必須結合 test 屬性聯合使用。

2.1 在 WHERE 條件中使用 if 標籤

這是常見的一種現象， 我們在進行按條件查詢的時候， 可能會有多種情況。

2.1.1 查詢條件

根據輸入的學生信息進行條件檢索

1. 當只輸入用戶名時， 使用用戶名進行模糊檢索；
2. 當只輸入性別時， 使用性別進行完全匹配
3. 當用戶名和性別都存在時， 用這兩個條件進行查詢匹配查詢

2.1.2 動態 SQL

接口函數

對應的動態 SQL

在此 SQL 語句中， where 1=1 是多條件拼接時的小技巧， 後面的條件查詢就可以都用 and 了。

同時， 我們添加了 if 標籤來處理動態 SQL

 <if test="name != null and name !=''">
 and name like concat('%', #{name}, '%')
 </if>
 <if test="sex != null">
 and sex=#{sex}
 </if>

此 if 標籤的 test 屬性值是一個符合 OGNL 的表達式， 表達式可以是 true 或 false。 如果表達式返回的是數值， 則0為 false, 非 0 為 true;

2.1.3 測試

只有名字時的查詢， 發送的語句和結果

查詢的條件只發送了

where 1=1 and name like concat('%', ?, '%') 

只有性別時的查詢， 發送的語句和結果

查詢的條件只發送了

 where 1=1 and sex=? 

姓名和性別同時存在的查詢， 發送的語句和結果

查詢條件

where 1=1 and name like concat('%', ?, '%') and sex=? 

2.2 在 UPDATE 更新列中使用 if 標籤

有時候我們不希望更新所有的字段， 只更新有變化的字段。

2.2.1 更新條件

只更新有變化的字段， 空值不更新。

2.2.1 動態 SQL

接口方法

 /**
 * 更新非空屬性
 */
 int updateByPrimaryKeySelective(Student record);

對應的 SQL

2.2.3 測試

結果如下

2.3 在 INSERT 動態插入中使用 if 標籤

我們插入數據庫中的一條記錄， 不是每一個字段都有值的， 而是動態變化的。 在這時候使用 if 標籤， 可幫我們解決這個問題。

2.3.1 插入條件

只有非空屬性才插入。

2.3.2 動態SQL

接口方法

 /**
 * 非空字段才進行插入
 */
 int insertSelective(Student record);

對應的SQL

這個 SQL 大家應該很熟悉， 畢竟是自動生成的。

2.3.3 測試

對應的結果

SQL 中， 只有非空的字段才進行了插入。

3 choose 標籤

choose when otherwise 標籤可以幫我們實現 if else 的邏輯。

一個 choose 標籤至少有一個 when, 最多一個otherwise

下面是一個查詢的例子。

3.1 查詢條件

假設 name 具有唯一性， 查詢一個學生

• 當 studen_id 有值時， 使用 studen_id 進行查詢；
• 當 studen_id 沒有值時， 使用 name 進行查詢；
• 否則返回空

3.2 動態SQL

接口方法

 /**
 * - 當 studen_id 有值時， 使用 studen_id 進行查詢；
 * - 當 studen_id 沒有值時， 使用 name 進行查詢；
 * - 否則返回空
 */
 Student selectByIdOrName(Student record);

對應的SQL

3.3 測試

有 ID 則根據 ID 獲取， 結果

沒有 ID 則根據 name 獲取

沒有 ID 和 name, 返回 null

4 trim(set、where)

這三個其實解決的是類似的問題。 如我們在寫前面的[在 WHERE 條件中使用 if 標籤] SQL 的時候， where 1=1 這個條件我們是不希望存在的。

4.1 where

4.1.1 查詢條件

根據輸入的學生信息進行條件檢索。

1. 當只輸入用戶名時， 使用用戶名進行模糊檢索；
2. 當只輸入性別時， 使用性別進行完全匹配
3. 當用戶名和性別都存在時， 用這兩個條件進行查詢匹配查詢

不使用 where 1=1。

4.1.2 動態 SQL

很顯然， 我們要解決這幾個問題

• 當條件都不滿足時： 此時 SQL 中應該要不能有 where ， 否則導致出錯
• 當 if 有條件滿足時： SQL 中需要有 where， 且第一個成立的 if 標籤下的 and | or 等要去掉

這時候， 我們可以使用 where 標籤。

接口方法

 /**
 * 根據輸入的學生信息進行條件檢索
 * 1. 當只輸入用戶名時， 使用用戶名進行模糊檢索；
 * 2. 當只輸入郵箱時， 使用性別進行完全匹配
 * 3. 當用戶名和性別都存在時， 用這兩個條件進行查詢匹配的用
 */
 List<Student> selectByStudentSelectiveWhereTag(Student student);

對應的 SQL

4.1.3 測試

只有名字時的查詢, 有 where

姓名和性別同時存在的查詢， 有 where

姓名和性別都不存在時查詢, 此時， where 不會再出現了。

4.2 set

set 標籤也類似， 在 [2.2 在 UPDATE 更新列中使用 if 標籤] 中， 如果我們的方法 updateByPrimaryKeySelective 沒有使用

4.3 trim

set 和 where 其實都是 trim 標籤的一種類型， 該兩種功能都可以使用 trim 標籤進行實現。

4.3.1 trim 來表示 where

如以上的 where 標籤， 我們也可以寫成

<trim prefix="where" prefixOverrides="AND |OR">
</trim>

表示當 trim 中含有內容時， 添加 where， 且第一個為 and 或 or 時， 會將其去掉。 而如果沒有內容， 則不添加 where。

4.3.2 trim 來表示 set

相應的， set 標籤可以如下表示

<trim prefix="SET" suffixOverrides=",">
</trim>

表示當 trim 中含有內容時， 添加 set， 且最後的內容為 , 時， 會將其去掉。 而沒有內容， 不添加 set

4.3.3 trim 的幾個屬性

• prefix: 當 trim 元素包含有內容時， 增加 prefix 所指定的前綴
• prefixOverrides: 當 trim 元素包含有內容時， 去除 prefixOverrides 指定的 前綴
• suffix: 當 trim 元素包含有內容時， 增加 suffix 所指定的後綴
• suffixOverrides： 當 trim 元素包含有內容時， 去除 suffixOverrides 指定的後綴

5 foreach 標籤

foreach 標籤可以對數組， Map 或實現 Iterable 接口。

foreach 中有以下幾個屬性

• collection: 必填， 集合/數組/Map的名稱.
• item: 變量名。 即從迭代的對象中取出的每一個值
• index: 索引的屬性名。 當迭代的對象為 Map 時， 該值為 Map 中的 Key.
• open: 循環開頭的字符串
• close: 循環結束的字符串
• separator: 每次循環的分隔符

其他的比較好理解， collection 中的值應該怎麼設定呢？

跟接口方法中的參數相關。

1. 只有一個數組參數或集合參數

默認情況： 集合collection=list， 數組是collection=array

推薦： 使用 @Param 來指定參數的名稱， 如我們在參數前@Param("ids")， 則就填寫 collection=ids

2. 多參數

多參數請使用 @Param 來指定， 否則SQL中會很不方便

3. 參數是Map

指定為 Map 中的對應的 Key 即可。 其實上面的 @Param 最後也是轉化為 Map 的。

4. 參數是對象

使用屬性.屬性即可。

5.1 在 where 中使用 foreach

在 where條件中使用， 如按id集合查詢， 按id集合刪除等。

5.1.1 查詢條件

我們希望查詢用戶 id 集合中的所有用戶信息。

5.1.2 動態 SQL

函數接口

 /**
 * 獲取 id 集合中的用戶信息
 * @param ids
 * @return
 */
 List<Student> selectByStudentIdList(List<Integer> ids);

對應 SQL

5.1.3 測試

結果

5.2 foreach 實現批量插入

可以通過foreach來實現批量插入。

5.2.1 動態SQL

接口方法

 /**
 * 批量插入學生
 */
 int insertList(List<Student> students);

對應的SQL

5.2.2 測試

結果

6 bind 標籤

bind 標籤是通過 OGNL 表達式去定義一個上下文的變量， 這樣方便我們使用。

如在 selectByStudentSelective 方法中， 有如下

<if test="name != null and name !=''">
 and name like concat('%', #{name}, '%')
 </if>

在 MySQL 中， 該函數支持多參數， 但在 Oracle 中只支持兩個參數。 那麼我們可以使用 bind 來讓該 SQL 達到支持兩個數據庫的作用

<if test="name != null and name !=''">
 <bind name="nameLike" value="'%'+name+'%'"/>
 and name like #{nameLike}
</if>

更改後的查詢結果如下

7 代碼

使用示例：https://github.com/homejim/mybatis-examples

擴展閱讀

mybatis中動態sql常用的標籤

Mybatis解析動態sql原理分析

深入理解Mybatis技術與原理 第5章 動態SQL

教你如何定位及優化SQL語句的性能問題

SpringBoot Mybatis 讀寫分離配置

性能評測：MyBatis 與 Hibernate 的性能差異