Mybatis之動態sql標籤的使用

1.Mybatis動態sql

MyBatis 的強大特性之一便是它的動態 SQL。如果你有使用 JDBC 或其它類似框架的經驗，你就能體會到根據不同條件拼接 SQL 語句的痛苦。例如拼接時要確保不能忘記新增必要的空格，還要注意去掉列表最後一個列名的逗號。利用動態 SQL 這一特性可以徹底擺脫這種痛苦。
 雖然在以前使用動態 SQL 並非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 對映語句中的強大的動態 SQL 語言得以改進這種情形。
 動態 SQL 元素和 JSTL 或基於類似 XML 的文字處理器相似。在 MyBatis 之前的版本中，有很多元素需要花時間瞭解。MyBatis 3 大大精簡了元素種類，現在只需學習原來一半的元素便可。MyBatis 採用功能強大的基於 OGNL 的表示式來淘汰其它大部分元素。

2.常見的動態sql標籤

2.1 if

在現實的工作場景中，我們通常需要按照不同的維度對資料進行查詢。比如我們
 透過員工管理系統要查詢一個name 為」Tom」的人，在大一點的公司可能有幾個name都為」Tom」的同事並且他們有可能分部在不同的部門，而在小點的公司可能只有一個人根本就不用按部門來過濾，這個時候我們可以透過傳參來控制我們的過濾條件如下：

  /**   * @Description employee的dao層程式碼   * @Author xiaoqx* @Version V1.0.0   * @Since 2017/11/26   */  public interface EmployeeMapper {     ListselectEmployeeList(Employee employee);  }select   *   from t_emp e   wheree.emp_name=#{name,jdbcType=VARCHAR}and e.emp_dep=#{dep.id,jdbcType=INTEGER}

配合一個「_databaseId」變數的 databaseIdProvider 可用於動態程式碼中，這樣就可以根據不同的資料庫廠商構建特定的語句。比如下面的例子：

  select seq_users.nextval from dualselect nextval for seq_users from sysibm.sysdummy1"insert into users values (#{id}, #{name})

2.2 where

我們可以想象一下如果我們只要按部門編號查詢某個部門的同事時，生成的sql 語句會是怎麼樣的? 很容易得出結論，最終生成的sql 就會如下：

執行後將會報sql語法錯誤。我們可以用另外一個動態標籤來解決這個問題：

  select   *   from t_emp eand e.emp_name=#{name,jdbcType=VARCHAR}and e.emp_dep=#{dep.id,jdbcType=INTEGER}

只要將sql放入where動態標籤內，至少有一個條件符合的時候，才會插入where語句並且會將條件語句前的 and 去掉。
 

2.3 trim

常用的屬性：
 prefix=」where」//給第一符合條件的語句 加上字首where
 prefixOverrides=」and」 //將最後一條語句的 字首and 覆蓋
 suffix=」and」 //給第一符合條件的語句 加上字尾 and
 suffixOverrides=」and」//將最後一條語句的字尾 and 覆蓋
 當我們把條件語句重新排版一下如下：

  select   *   from t_emp ee.emp_name=#{name,jdbcType=VARCHAR} andand e.emp_dep=#{dep.id,jdbcType=INTEGER} and

然後執行，結果如下：發現 動態where 標籤只會去除 條件語句的第一個and ，這時候動態where就解決不了這個問題了，就有了一個新的動態標籤trim
 

動態xml程式碼

   select   *   from t_emp e   //表示給第一個符合條件的語句前加 where，把最後一個語句的suffixOverrides="and" 指定的and 覆蓋掉e.emp_name=#{name,jdbcType=VARCHAR} ande.emp_dep=#{dep.id,jdbcType=INTEGER} and

2.4 set

類似的用於動態更新語句的解決方案叫做 set。set 元素可以用於動態包含需要更新的列，而捨去其它的。比如：

  update Authorusername=#{username},password=#{password},email=#{email},bio=#{bio}where id=#{id}

這裡，set 元素會動態前置 SET 關鍵字，同時也會刪掉無關的逗號，因為用了條件語句之後很可能就會在生成的 SQL 語句的後面留下這些逗號。（譯者註：因為用的是「if」元素，若最後一個「if」沒有匹配上而前面的匹配上，SQL 語句的最後就會有一個逗號遺留）

2.5 choose

有時我們不想應用到所有的條件語句，而只想從中擇其一項。針對這種情況，MyBatis 提供了 choose 元素，它有點像 Java 中的 switch 語句。
 還是上面的例子，但是這次變為提供了「title」就按「title」查詢，提供了「author」就按「author」查詢的情形，若兩者都沒有提供，就返回所有符合條件的 BLOG（實際情況可能是由管理員按一定策略選出 BLOG 列表，而不是返回大量無意義的隨機結果）。

  SELECT * FROM BLOG WHERE state = 『ACTIVE'AND title like #{title}AND author_name like #{author.name}AND featured = 1

2.6 foreach

常用的屬性：
 collection 要遍歷的集合；
 item 要遍歷的元素；
 index 元素在集合中的索引；
 open 遍歷以什麼開頭 比如 open=」and id in (「;
 seprator 遍歷出來的元素以什麼分隔；
 end 遍歷以什麼結束 end=」)」
 動態 SQL 的另外一個常用的操作需求是對一個集合進行遍歷，通常是在構建 IN 條件語句的時候。比如：

  SELECT *   FROM POST P   WHERE ID in#{item}

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

2.7 bind

bind 元素可以從 OGNL 表示式中建立一個變數並將其繫結到上下文。這個動態標籤可以完美解決#{}在某些時候不適用，而用美元{}又有sql注入的風險的情況（${}與#{}的區別）比如：

  SELECT * FROM BLOG   WHERE title LIKE #{pattern}

2.8 insert

批次插入mysql 與oracle的區別：

2.8.1 mysql 批次插入

插入語句

  insert into t_emp (id,username)   values#{u.id},#{u.username}

預編譯結果

  insert into t_emp (id,username)   values(？，？),(？，？),(？，？)

你可能會想把整個插入語句進行迴圈如下：
 用；來分隔每一條插入語句

   insert into t_emp (id,username)   values (#{u.id},#{u.username} )

預編譯結結果

  insert into t_emp (id,username) values (?,?);  insert into t_emp (id,username) values (?,?);  insert into t_emp (id,username) values (?,?);

mysql預設是不支援這種語法，需要在url 後面的連線屬性增加一個 allowMultiQueries=true; 該屬性預設是關閉的。

2.8.2 oracle批次插入

oracle並不支援mysql這種語法

  insert into t_emp (id,username) values(？，？),(？，？),(？，？)

他只能透過如下來完成插入

  begin   insert into t_emp (id,username) values(？，？);   insert into t_emp (id,username) values(？，？);   insert into t_emp (id,username) values(？，？);   end;

2.9 sql

這個元素可以被用來定義可重用的 SQL 程式碼段，可以包含在其他語句中。它可以被靜態地(在載入引數) 引數化. 不同的屬性值透過包含的例項變化. 比如：

  ${alias}.id,${alias}.username,${alias}.password

  select,from some_table t1   cross join some_table t2