MyBatis 動態 SQL

2021 年 10 月 8 日
筆記
JAVA

MyBatis 的動態 SQL 功能可以幫助我們根據不同條件拼接 SQL 語句，並自動處理 SQL 語法，動態 SQL 功能通過 OGNL(Object-Graph Navigation Language) 表達式和以下幾個標籤實現，下方詳細介紹。

首先列出本文涉及到的數據表 DDL、entity 對象和 Mybatis 基本配置。

DDL （源自 MySQL 官方演示用的 world_x ，簡單修改欄位名更符合我們熟知的開發規範）

CREATE TABLE country
(
    primary_code   VARCHAR(3)  DEFAULT '' NOT NULL PRIMARY KEY,
    country_name   VARCHAR(52) DEFAULT '' NOT NULL,
    capital        INT                    NULL,
    secondary_code VARCHAR(2)  DEFAULT '' NOT NULL
);

Country 對象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Country implements Serializable {

    /** 主要國家程式碼 */
    private String primaryCode;

    /** 國家名 */
    private String countryName;

    /** 首都ID */
    private Integer capital;

    /** 次要國家程式碼 */
    private String secondaryCode;

    private static final long serialVersionUID = 1L;

}

Mapper 介面大致內容：

package pers.cncsl.ft.mybatis.mapper;

import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import pers.cncsl.ft.mybatis.entity.Country;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 國家Mapper
 */
@Repository
public interface CountryMapper {
	//...
}

mapper.xml 文件大致內容：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "//mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="pers.cncsl.ft.mybatis.mapper.CountryMapper">

    <resultMap id="BaseResultMap" type="pers.cncsl.ft.mybatis.entity.Country">
        <id column="primary_code" jdbcType="VARCHAR" property="primaryCode"/>
        <result column="country_name" jdbcType="VARCHAR" property="countryName"/>
        <result column="capital" jdbcType="INTEGER" property="capital"/>
        <result column="secondary_code" jdbcType="VARCHAR" property="secondaryCode"/>
    </resultMap>

    <!--其他 MyBatis 標籤-->

</mapper>

if

if 是最基本的動態 SQL 標籤，其 test 屬性是一個用於判斷輸入參數的 OGNL 表達式，當條件判斷為真時會拼接其中的內容。與程式語言不同，mybatis 沒有 else 標籤，if 標籤僅用於最基本的條件判斷，如果有多個並列的條件需要連續使用相應數量的 if 標籤。例如根據主要國家程式碼或（和）國家名搜索：

/**
* 根據主鍵或國家名查詢一條記錄
*
* @param primaryKey 主鍵
* @param countryName 國家名
* @return 查詢結果
*/
Country selectByCodeOrName(@Param("primaryKey") String primaryKey, @Param("countryName") String countryName);

<select id="selectByCodeOrName" resultMap="BaseResultMap">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    WHERE 1 = 1
    <if test="primaryCode != null">
        and primary_code = #{primaryCode, jdbcType=VARCHAR}
    </if>
    <if test="countryName != null">
        and country_name = #{countryName, jdbcType=VARCHAR}
    </if>
</select>

如下三次調用：

Country resultByCode = mapper.selectByCodeOrName("CHN", null);
Country resultByName = mapper.selectByCodeOrName(null, "China");
Country resultByCodeAndName = mapper.selectByCodeOrName("CHN", "China");

從 Mybatis 的日誌中可以看到分別執行的 SQL 語句是：

SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and primary_code = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and country_name = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and primary_code = ? and country_name = ?

choose

choose 標籤的應用場景是多個條件選擇一個，類似程式語言的 switch 語法。它有 when 和 otherwise 兩個子標籤：

when 子標籤和 if 類似，test 屬性用於判斷 OGNL 表達式，一個 otherwise 標籤內可以有任意數量、只會命中第一個 test 屬性為真的。
otherwise 子標籤類似 switch 的 default，如果前方所有的 when 子標籤條件判斷都為假，才會拼接上 otherwise 子標籤里的內容。

例如上面的例子可以進一步改造為（介面內容不變）：

<select id="selectByCodeOrName" resultMap="BaseResultMap">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    WHERE 1 = 1
    <choose>
        <when test="primaryCode != null">
            and primary_code = #{primaryCode, jdbcType=VARCHAR}
        </when>
        <otherwise>
            and country_name = #{countryName, jdbcType=VARCHAR}
        </otherwise>
    </choose>
</select>

和之前同樣的參數再調用三次，執行的 SQL 語句分別是：

SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and primary_code = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and country_name = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE 1 = 1 and primary_code = ?

trim

trim 標籤用於避免使用 if 或 choose 時為了 SQL 語法的正確性而不得不插入類似 1=1 and 這種語句，保證生成的 SQL 更乾淨、優雅，where 和 set 標籤是 trim 的一種具體用法。

where

如果 where 標籤內的其他標籤有返回值，就在標籤位置插入一個 where，並剔除後續字元串開頭的 AND 和 OR。

在之前的兩個例子中，為了保證生成正確的 SQL，我們加入了 WHERE 1=1 這樣的程式碼段，可以通過 where 標籤來優化（介面內容不變）：

<select id="selectByCodeOrName" resultMap="BaseResultMap">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    <where>
        <choose>
            <when test="primaryCode != null">
                and primary_code = #{primaryCode, jdbcType=VARCHAR}
            </when>
            <otherwise>
                and country_name = #{countryName, jdbcType=VARCHAR}
            </otherwise>
        </choose>
    </where>
</select>

改造後仍然用之前的參數調用，執行的 SQL 語句分別是：

SELECT primary_code, country_name, capital, secondary_code FROM country WHERE primary_code = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE country_name = ?
SELECT primary_code, country_name, capital, secondary_code FROM country WHERE primary_code = ?

可以看出，MyBatis 已經幫我們在生成的 SQL 中插入了 WHERE。

set

如果 set 標籤內的其他標籤有返回值，就在標籤位置插入一個 set，並剔除後續字元串結尾處的逗號。

很常見的需求是僅更新數據表中的指定欄位，這些欄位需要通過 if 標籤處理，而 set 標籤用於插入一個 SET 關鍵字：

/**
* 根據主鍵和入參的其他數據更新一條記錄，入參為 null 的欄位不變更
*
* @param entity 入參數據
* @return 更新結果
*/
int updateByPrimaryKeySelective(Country entity);

<update id="updateByPrimaryKeySelective" parameterType="pers.cncsl.ft.mybatis.entity.Country">
    UPDATE country
    <set>
        <if test="countryName != null">
            country_name = #{countryName, jdbcType=VARCHAR},
        </if>
        <if test="capital != null">
            capital = #{capital, jdbcType=INTEGER},
        </if>
        <if test="secondaryCode != null">
            secondary_code = #{secondaryCode, jdbcType=VARCHAR},
        </if>
    </set>
    WHERE primary_code = #{primaryCode, jdbcType=VARCHAR}
</update>

如下調用：

Country update = Country.builder().primaryCode(PRIMARY_CODE).countryName("中國").build();
int rows = mapper.updateByPrimaryKeySelective(update);

執行的 SQL 為：

UPDATE country SET country_name = ? WHERE primary_code = ?

Mybatis 幫我們插入了 SET 關鍵字、並刪除了後續多餘的逗號。

trim 屬性詳解

trim 標籤有以下四個屬性：

prefix：當 trim 內有內容時，會給內容增加 prefix 指定的前綴
prefixOverrides：當 trim 內有內容時，會將內容中匹配的前綴字元串取掉
suffix：當 trim 內有內容時，會給內容增加 suffix 指定的後綴
suffixOverrides：當 trim 內有內容時，會將內容中匹配的後綴字元串取掉

所以 where 標籤和 set 標籤實際相當於：

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

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

foreach

顧名思義，foreach 標籤用於實現集合遍歷，它有以下屬性：

collection：要迭代遍歷的屬性名。
item：變數名，每次迭代時從集合中取出的值的名稱。
index：索引值，對 List 集合值為當前索引值，對 Map 集合值為當前 key。
open：整個循環內容開頭處添加的字元串。
close：整個循環內容結尾處添加的字元串。
separator：每次循環的分隔符。

例如根據主鍵批量查詢：

/**
* 根據入參的主鍵List集合批量查詢，使用 List 較為簡單。
*
* @param keys 主鍵List集合
* @return 查詢結果
*/
List<Country> selectByPrimaryCodeList(List<String> keys);

<select id="selectByPrimaryCodeList" parameterType="java.util.Collection"
        resultType="pers.cncsl.ft.mybatis.entity.Country">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    <where>
        <if test="list != null and !list.isEmpty()">
            primary_code IN
            <foreach collection="list" open="(" close=")" separator="," item="code">
                #{code}
            </foreach>
        </if>
    </where>
</select>

注意上面的 where、if 和 foreach 三種標籤的組合使用，能避免入參錯誤導致 MyBatis 拼接出錯誤的 SQL 語句，不過入參為 null 或空集合時會查出全表。編碼時對於這種需求應該小心對待，盡量不傳入 null 或空集合。

collection 屬性的值是 Mybatis 中的參數名，通常用 @Param 註解指定，不指定時 Mybatis 會對不同類型的參數添加一個默認的名稱，數組和集合有關的默認參數名規則如下：

數組類型 array
Collection 集合（java.util.Collection 的實現類）都為 collection
List 也可用 list

仔細下方內容，重點在於 Mapper 介面中函數入參為數組、List 集合和 Set 集合時 xml 文件中 foreach 標籤的屬性和內容：

/**
* 根據入參的主鍵數組批量查詢，使用數組是最基礎的情況，使用場景不多。
*
* @param keys 主鍵數組
* @return 查詢結果
*/
List<Country> selectByPrimaryArray(String[] keys);

/**
* 根據入參的主鍵List集合批量查詢，使用 List 較為簡單。
*
* @param keys 主鍵List集合
* @return 查詢結果
*/
List<Country> selectByPrimaryCodeList(List<String> keys);

/**
* 根據入參的主鍵Set集合批量查詢，使用 Set 可以保證集合元素唯一。
*
* @param keys 主鍵Set集合
* @return 查詢結果
*/
List<Country> selectByPrimaryCodeSet(Set<String> keys);

<select id="selectByPrimaryArray" parameterType="java.util.Collection"
        resultType="pers.cncsl.ft.mybatis.entity.Country">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    <where>
        <if test="array != null and array.length gt 0">
            primary_code IN
            <foreach collection="array" open="(" close=")" separator="," item="code">
                #{code}
            </foreach>
        </if>
    </where>
</select>

<select id="selectByPrimaryCodeList" parameterType="java.util.Collection"
        resultType="pers.cncsl.ft.mybatis.entity.Country">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    <where>
        <if test="list != null and !list.isEmpty()">
            primary_code IN
            <foreach collection="list" open="(" close=")" separator="," item="code">
                #{code}
            </foreach>
        </if>
    </where>
</select>

<select id="selectByPrimaryCodeSet" parameterType="java.util.Collection"
        resultType="pers.cncsl.ft.mybatis.entity.Country">
    SELECT primary_code, country_name, capital, secondary_code
    FROM country
    <where>
        <if test="collection != null and !collection.isEmpty()">
            primary_code IN
            <foreach collection="collection" open="(" close=")" separator="," item="code">
                #{code}
            </foreach>
        </if>
    </where>
</select>

foreach 還可實現批量插入和動態更新功能，不過這兩個功能在開發中用到的場景不多，就不詳細說了。

Tags: JAVA

MyBatis 動態 SQL

if

choose

trim

where

set

trim 屬性詳解

foreach

VirMach 便宜 VPS

QNews

MyBatis 動態 SQL

if

choose

trim

where

set

trim 屬性詳解

foreach

分享此文：

Related Posts

Gin + Vue全棧開發實戰(二)

2022最新IntellJ IDEA的zheng開發部署文檔

貴州一高校軍訓出動直升機 原因公布：這畫面學生高呼太帥

【影像處理】基於OpenCV實現影像直方圖的原理

VirMach 便宜 VPS

QNews

熱門搜尋

貴州一高校軍訓出動直升機原因公布：這畫面學生高呼太帥