Myabtis動態SQL，你真的會了嗎？

愛撒謊的男孩發表於2020-09-06
SQL

目錄

  • 前言
  • 什麼是動態SQL？
  • 常用的標籤
    • if
    • choose、when、otherwise
    • where
    • foreach
    • set
    • sql
    • include
    • 總結
  • 擴充一下
    • Mybatis中如何避免魔數？
    • 如何引用其他XML中的SQL片段？
  • 總結

前言

  • 通過前兩篇的文章我們瞭解了Mybatis基本的CRUD操作、一些基本標籤的屬性以及如何對映結果，感興趣的可以看我的前兩篇文章，分別是Mybatis入門之基礎操作和Mybatis結果對映，你射準了嗎？，如果有什麼疑問的地方可以在文章下方留言，作者統一回復。
  • 這篇文章就來聊一聊Mybatis的動態SQL，在實際的開發中Mybatis的這項功能是非常重要的，至於什麼是動態SQL？如何實現動態SQL？下面文章將會詳細介紹。

什麼是動態SQL？

  • 動態 SQL 是 MyBatis 的強大特性之一。顧名思義，就是會動的SQL，即是能夠靈活的根據某種條件拼接出完整的SQL語句。這種類似於MySQL中的case when then else then end....這種語法，能夠根據某種條件動態的拼接出需要的SQL。
  • 至於Mybatis如何實現動態SQL呢，Mybatis提供了非常多的標籤，能夠讓我們在XML檔案中靈活的運用這些標籤達到拼接SQL的目的。

常用的標籤

  • Mybatis為了能夠讓開發者靈活的寫SQL也是費了一番功夫，定義了很多的標籤和語法，下面將會一一介紹。

if

  • 雖然英文不太好，但是在這麼簡單的不會不知道是如果的意思吧，Java語法中也有，只有判斷條件為true才會執行其中的SQL語句。
  • 舉個例子：HIS系統中醫護人員需要根據特定條件篩選患者，比如住院號，床位，性別等。當然這些條件並不是必填的，具體的功能截圖如下:
  • 以上截圖中的條件篩選並不是必填的，因此我們不能在SQL中固定，要根據前端是否傳值來判斷是否需要加上這個條件。那麼此時查詢語句如何寫呢？如下：
<select id ='selectPats' resultType='com.xxx.domain.PatientInfo'>
  select * from patient_info 
  where status=1
  <!--前端傳來的住院號不為null，表示需要根據住院號篩選，此時Where語句就需要加上這個條件-->
  <if test="iptNum!=null">
      and ipt_num=#{iptNum}
  </if>
  
  <!--床位號篩選-->
  <if test="bedNum!=null">
      and bed_num=#{bedNum}
  </if>
</select>
  • <if>標籤中的屬性test用來指定判斷條件，那麼問題來了，上面的例子中的test中判斷條件都是一個條件，如果此時變成兩個或者多個條件呢？和SQL的語法類似，and連線即可，如下：
  <if test="bedNum!=null and bedNum!='' ">
      and bed_num=#{bedNum}
  </if>

choose、when、otherwise

  • 有時候，我們不想使用所有的條件，而只是想從多個條件中選擇一個使用。針對這種情況，MyBatis 提供了 choose 元素，它有點像 Java 中的 switch 語句。
  • 還是上面的例子改變一下：此時只能滿足一個篩選條件，如果前端傳來住院號就只按照住院號查詢，如果傳來床位號就只按照床位號篩選，如果什麼都沒傳，就篩選所有在院的。此時的查詢如下：
<select id="selectPats"
     resultType="com.xxx.domain.PatientInfo">
  select * from patient_info where 1=1
  <choose>
    <!--住院號不為null時，根據住院號查詢-->
    <when test="iptNum != null">
      AND ipt_num=#{iptNum}
    </when>
    <!--床位號不是NUll-->
    <when test="bedNum != null">
      AND bed_num = #{bedNum}
    </when>
    <otherwise>
      AND status=1
    </otherwise>
  </choose>
</select>
  • MyBatis 提供了 choose 元素，按順序判斷 when 中的條件出否成立，如果有一個成立，則 choose 結束。當 choose 中所有 when 的條件都不滿則時，則執行 otherwise 中的 sql。類似於 Java 的 switch 語句，choose 為 switch，when 為 case，otherwise 則為default。

where

  • 舉個例子：對於choose標籤的例子中的查詢，如果去掉where後的1=1此時的SQL語句會變成什麼樣子，有三種可能的SQL，如下：
select * from patient_info where AND ipt_num=#{iptNum};

select * from patient_info where AND bed_num = #{bedNum};

select * from patient_info where AND status=1;
  • 發生了什麼，以上三條SQL語句對嗎？很顯然是不對的，顯然where後面多了個AND。如何解決呢？此時就要用到where這個標籤了。
  • where 元素只會在子元素返回任何內容的情況下才插入 WHERE 子句。而且，若子句的開頭為 AND 或 OR，where 元素也會將它們去除。
  • 此時的查詢改造如下：
<select id="selectPats"
     resultType="com.xxx.domain.PatientInfo">
  select * from patient_info
    <where>
        <choose>
          <!--住院號不為null時，根據住院號查詢-->
          <when test="iptNum != null">
            AND ipt_num=#{iptNum}
          </when>
          <!--床位號不是NUll-->
          <when test="bedNum != null">
            AND bed_num = #{bedNum}
          </when>
          <otherwise>
            AND status=1
          </otherwise>
        </choose>
   </where>
</select>

foreach

  • foreach是用來對集合的遍歷，這個和Java中的功能很類似。通常處理SQL中的in語句。
  • foreach 元素的功能非常強大，它允許你指定一個集合，宣告可以在元素體內使用的集合項（item）和索引（index）變數。它也允許你指定開頭與結尾的字串以及集合項迭代之間的分隔符。這個元素也不會錯誤地新增多餘的分隔符
  • 你可以將任何可迭代物件（如 List、Set 等）、Map 物件或者陣列物件作為集合引數傳遞給 foreach。當使用可迭代物件或者陣列時，index 是當前迭代的序號，item 的值是本次迭代獲取到的元素。當使用 Map 物件（或者 Map.Entry 物件的集合）時，index 是鍵，item 是值。
  • 例子如下：
<select id="selectPats" resultType="com.xxx.domain.PatientInfo">
  SELECT *
  FROM patient_info 
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>
  • 改標籤中的各個屬性的含義如下：
屬性 含義
item 表示在迭代過程中每一個元素的別名
index 表示在迭代過程中每次迭代到的位置（下標）
open 字首
close 字尾
separator 分隔符，表示迭代時每個元素之間以什麼分隔

set

  • 講這個標籤之前，先看下面這個例子：
<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    SET NAME = #{name},
    MAJOR = #{major},
    HOBBY = #{hobby}
    WHERE ID = #{id};
</update>

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT SET
    <if test="name!=null and name!='' ">
        NAME = #{name},
    </if>
    <if test="hobby!=null and hobby!='' ">
        MAJOR = #{major},
    </if>
    <if test="hobby!=null and hobby!='' ">
        HOBBY = #{hobby}
    </if>
    WHERE ID = #{id};
</update>

  • 上面的例子中沒有使用 if 標籤時，如果有一個引數為 null，都會導致錯誤。當在 update 語句中使用 if 標籤時，如果最後的 if 沒有執行，則或導致逗號多餘錯誤。使用 set 標籤可以將動態的配置 set 關鍵字，和剔除追加到條件末尾的任何不相關的逗號。

  • 使用 set+if 標籤修改後，如果某項為 null 則不進行更新，而是保持資料庫原值。此時的查詢如下：

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    <set>
        <if test="name!=null and name!='' ">
            NAME = #{name},
        </if>
        <if test="hobby!=null and hobby!='' ">
            MAJOR = #{major},
        </if>
        <if test="hobby!=null and hobby!='' ">
            HOBBY = #{hobby}
        </if>
    </set>
    WHERE ID = #{id};
</update>

sql

  • 在實際開發中會遇到許多相同的SQL，比如根據某個條件篩選，這個篩選很多地方都能用到，我們可以將其抽取出來成為一個公用的部分，這樣修改也方便，一旦出現了錯誤，只需要改這一處便能處處生效了，此時就用到了<sql>這個標籤了。
  • 當多種型別的查詢語句的查詢欄位或者查詢條件相同時，可以將其定義為常量，方便呼叫。為求 <select> 結構清晰也可將 sql 語句分解。如下：
<!-- 查詢欄位 -->
<sql id="Base_Column_List">
    ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY
</sql>

<!-- 查詢條件 -->
<sql id="Example_Where_Clause">
    where 1=1
    <trim suffixOverrides=",">
        <if test="id != null and id !=''">
            and id = #{id}
        </if>
        <if test="major != null and major != ''">
            and MAJOR = #{major}
        </if>
        <if test="birthday != null ">
            and BIRTHDAY = #{birthday}
        </if>
        <if test="age != null ">
            and AGE = #{age}
        </if>
        <if test="name != null and name != ''">
            and NAME = #{name}
        </if>
        <if test="hobby != null and hobby != ''">
            and HOBBY = #{hobby}
        </if>
    </trim>
</sql>

include

  • 這個標籤和<sql>是天仙配，是共生的，include用於引用sql標籤定義的常量。比如引用上面sql標籤定義的常量，如下：
<select id="selectAll" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    FROM student
    <include refid="Example_Where_Clause" />
</select>
  • refid這個屬性就是指定<sql>標籤中的id值（唯一標識）。

總結

  • 至此，Mybatis動態SQL中常用的標籤就已經介紹完了，這部分的內容在實際工作中是必須會用到的，除非你們公司不用Mybatis。

擴充一下

  • 前面介紹了動態SQL的一些標籤以及屬性，相信看完之後應該能夠靈活的應用了，但是在實際開發中還是有一些奇技淫巧的，陳某今天簡單的講幾個。

Mybatis中如何避免魔數

  • 開過阿里巴巴開發手冊的大概都知道程式碼中是不允許出現魔數的，何為魔數？簡單的說就是一個數字，一個只有你知道，別人不知道這個代表什麼意思的數字。通常我們在Java程式碼中都會定義一個常量類專門定義這些數字。
  • 比如獲取醫生和護士的許可權，但是醫生和護士的許可權都不相同，在這條SQL中肯定需要根據登入的型別type來區分，比如type=1是醫生，type=2是護士，估計一般人會這樣寫：
<if test="type!=null and type==1">
    -- ....獲取醫生的許可權
</if>

<if test="type!=null and type==2">
    -- ....獲取護士的許可權
</if>
  • 這樣寫也沒什麼錯，但是一旦這個type代表的含義變了，那你是不是涉及到的SQL都要改一遍。
  • 開發中通常定義一個常量類，如下：
package com.xxx.core.Constants;
public class CommonConstants{
  //醫生
  public final static int DOC_TYPE=1;
  
  //護士
  public final static int NUR_TYPE=2;
  
}
  • 那麼此時的SQL應該如何寫呢？如下：
<if test="type!=null and type==@com.xxx.core.Constants.CommonConstants@DOC_TYPE">
    -- ....獲取醫生的許可權
</if>

<if test="type!=null and type==@com.xxx.core.Constants.CommonConstants@NUR_TYPE">
    -- ....獲取護士的許可權
</if>
  • 就是這麼簡單，就是@+全類名+@+常量。
  • 除了呼叫常量類中的常量，還可以類中的方法，很少用到，不再介紹了，感興趣的可以問下度娘。

如何引用其他XML中的SQL片段

  • 實際開發中你可能遇到一個問題，比如這個resultMap或者這個<sql>片段已經在另外一個xxxMapper.xml中已經定義過了，此時當前的xml還需要用到，難不成我複製一份？小白什麼也不問上來就複製了，好吧，後期修改來了，每個地方都需要修改了。難受不？
  • 其實Mybatis中也是支援引用其他Mapper檔案中的SQL片段的。其實很簡單，比如你在com.xxx.dao.xxMapper這個Mapper的XML中定義了一個SQL片段如下：
<sql id="Base_Column_List">
    ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY
</sql>
  • 此時我在com.xxx.dao.PatinetMapper中的XML檔案中需要引用，如下：
  <include refid="com.xxx.dao.xxMapper.Base_Column_List"></include>
  • 如此簡單，類似於Java中的全類名。
  • <select>標籤中的resultMap同樣可以這麼引用，和上面引用的方式一樣，不再贅述了。

總結

  • 好了，Myabtis的動態SQL的內容已經介紹完了，你會了嗎？每日來看看，下面會有更多精彩的內容！！！
  • 如果 覺得寫的不錯的，關注作者微信公眾號【碼猿技術專欄】，掃一掃右邊的二維碼即可。每天都有最新的原創內容分享。

相關文章