mybatis實現動態sql

第一章、動態SQL

MyBatis 的強大特性之一便是它的動態 SQL。如果你有使用 JDBC 或其它類似框架的經驗，你就能體會到根據不同條件拼接 SQL 語句的痛苦。例如拼接時要確保不能忘記添加必要的空格，還要注意去掉列表最后一個列名的逗號。利用動態 SQL 這一特性可以徹底擺脫這種痛苦。

例如，下面需求就會使用到拼接sql語句：

【需求】：查詢男性用戶，如果輸入了用戶名，按用戶名模糊查詢,如果沒有輸入用戶名，就查詢所有男性用戶。

正常的sql語句：查詢男性并且用戶名中包含zhang

在這里插入圖片描述

select * from tb_user where sex = "男" and user_name like '%zhang%'

在這里插入圖片描述

select * from tb_user where  sex = "男"

? 實現需求時還要判斷用戶是否輸入用戶名來做不同的查詢要求，而這里似乎沒有辦法判斷是否輸入了用戶名，因此可以考慮使用動態sql來完成這個功能。

? 動態 SQL 元素和后面學習的 JSTL 或基于之前學習的類似 XML 的文本處理器相似。在 MyBatis 之前的版本中，有很多元素需要花時間了解。MyBatis 3 開始精簡了元素種類，現在只需學習原來一半的元素便可。MyBatis 采用功能強大的 OGNL 的表達式來淘汰其它大部分元素。

常見標簽如下：

if：判斷   if(1 gt 2){}
choose (when, otherwise)：分支判斷    switch:多選一
trim (where, set)：去除
foreach：循環遍歷標簽

動態SQL中的業務邏輯判斷需要使用到以下運算符： ognl表達式

1.   e1 or e2 滿足一個即可
2.   e1 and e2 都得滿足 
3.    e1 == e2,e1 eq e2 判斷是否相等
4.    e1 != e2,e1 neq e2 不相等
5.    e1 lt e2：小于   lt表示less than 
6.    e1 lte e2：小于等于，其他gt（大于）,gte（大于等于） gt 表示greater than
7.    e1 in e2 
8.    e1 not in e2
9.    e1 + e2,e1 * e2,e1/e2,e1 - e2,e1%e2
10.   !e,not e：非，求反
11.   e.method(args)調用對象方法
12.   e.property對象屬性值
13.   e1[ e2 ]按索引取值，List,數組和Map
14.   @class@method(args)調用類的靜態方法
15.   @class@field調用類的靜態字段值

1、if標簽

格式：

 <if test="判斷條件">滿足條件執行的代碼</if>
說明：1）if標簽：判斷語句，用于進行邏輯判斷的。如果判斷條件為true，則執行if標簽的文本內容2）test屬性：用來編寫表達式，支持ognl；

【需求】：查詢男性用戶，如果輸入了用戶名，按用戶名模糊查詢,如果沒有輸入用戶名，就查詢所有男性用戶。

正常的sql語句：查詢男性并且用戶名中包含zhang

在這里插入圖片描述

select * from tb_user where sex = "男" and user_name like '%zhang%'

在這里插入圖片描述

select * from tb_user where  sex = "男"

? 上述動態sql語句部分： and user_name like ‘%zhang%’

1.1、定義接口方法

? 在UserMapper接口中，定義如下方法：

   /*** 根據用戶名模糊查詢* @param userName* @return*/List<User> queryLikeUserName(@Param("userName") String userName);

1.2、編寫SQL

? 在UserMapper.xml文件中編寫與方法名同名的sql語句：

  <select id="queryLikeUserName" resultType="user">select * from user where sex='男'<if test="userName!=null and userName.trim()!=''">and username like '%${userName}%'</if></select>

【注】<if> 判斷中：

1、if標簽：用來判斷；

2、test屬性：使用OGNL表達式，完成具體的判斷業務邏輯；

3、這里使用的字符串拼接，所以這里不能是#取值，只能使用$取值，否則會報錯

1.3、測試

【userName有值】

在這里插入圖片描述

對應的SQL語句是：select * from user where sex=“男” and username like ‘%孫%’

【userName沒有值】

在這里插入圖片描述

對應的SQL語句是：select * from user where sex=“男”

【小結】

1、if標簽：用來在sql中處理判斷是否成立的情況；
2、屬性：test中書寫OGNL表達式，如果結果為true，if標簽的文本中的內容會被拼接到SQL中，反之不會被拼接到SQL中；
3、if標簽的應用場景：適用于 二選一

2、choose，when，otherwise

choose標簽：分支選擇（多選一，遇到成立的條件即停止）when子標簽：編寫條件，不管有多少個when條件，一旦其中一個條件成立，后面的when條件都不執行。test屬性：編寫ognl表達式otherwise子標簽：當所有條件都不滿足時，才會執行該條件。

需求：

編寫一個查詢方法，設置兩個參數，一個是用戶名，一個是住址。根據用戶名或者住址查詢所有男性用戶:如果輸入了用戶名則按照用戶名模糊查找，否則就按照住址查找，兩個條件只能成立一個，如果都不輸入就查找用戶名為“孫悟空”的用戶。

【需求分析】

1、查詢所有男性用戶,如果輸入了用戶名則按照用戶名模糊查找;

SELECT * FROM  user WHERE  sex = "男" AND  username LIKE  '%孫%';

2、查詢所有男性用戶,如果輸入了住址則按照住址查詢；

SELECT * FROM  user WHERE  sex = "男" AND  address = "花果山水簾洞";

3、查詢所有男性用戶，如果都不輸入就查找用戶名為“孫悟空”的用戶。

SELECT * FROM  user WHERE  sex = "男" AND username = '孫悟空';

2.1、定義接口方法

在UserMapper接口中，定義接口方法：

 /*查詢用戶名或者地址*/List<User> queryByUserNameOrAddress(@Param("userName") String userName, @Param("address") String address);

2.2、編寫SQL

在UserMapper.xml中編寫對應的SQL語句

<!--根據用戶名或者住址查詢所有男性用戶:如果輸入了用戶名則按照用戶名模糊查找，否則就按照住址查找，兩個條件只能成立一個，如果都不輸入就查找用戶名為“孫悟空”的用戶。--><select id="queryByUserNameOrAddress" resultType="user">select * from user where sex='男'<choose><when test="userName!=null and userName.trim()!=''">and username like '%${userName}%'</when><when test="address!=null and address.trim()!=''">and address = #{address}</when><otherwise>and username='孫悟空'</otherwise></choose></select>

2.3、測試

編寫測試類，對這個方法進行測試：

@Test
public void queryByUserNameOrAddress(){List<User> userList = userMapper.queryByUserNameOrAddress("", null);System.out.println("userList = " + userList);
}

【小結】

1、choose，when，otherwise標簽組合的作用類似于java中的switch語句，使用于多選一；

3、where

where標簽：拼接多條件查詢時 1、能夠添加where關鍵字； 2、能夠去除多余的and或者or關鍵字

案例：按照如下條件查詢所有用戶，

如果輸入了用戶名按照用戶名進行查詢，
如果輸入住址，按住址進行查詢，
如果兩者都輸入，兩個條件都要成立。

【需求分析】

1、如果輸入了用戶名按照用戶名進行查詢，

SELECT * FROM  user WHERE user_name = '孫悟空';

2、如果輸入住址，按住址進行查詢，

SELECT * FROM  user WHERE address='花果山水簾洞';

3、如果兩者都輸入，兩個條件都要成立。

SELECT * FROM  user WHERE user_name = '孫悟空' AND address='花果山水簾洞';

3.1、定義接口方法

在UserMapper接口中定義如下方法：

List<User> queryByUserNameAndAge(@Param("userName") String userName, @Param("address") String address);

3.2、編寫SQL

在UserMapper.xml中編寫SQL

 <!--如果輸入了用戶名按照用戶名進行查詢，如果輸入住址，按住址進行查詢，如果兩者都輸入，兩個條件都要成立。說明：如果按照如下寫sql語句會有問題，假設用戶名username是空，那么用戶名的sql語句不參與條件，此時sql語句就會變為：SELECT * FROM  user where AND address = #{address}where后面直接書寫了and顯然不滿足sql語句語法，這里會報錯我們可以使用where標簽解決上述問題：where標簽：拼接多條件查詢時 1、能夠添加where關鍵字； 2、能夠去除多余的and或者or關鍵字--><!-- <select id="queryByUserNameAndAge" resultType="user">SELECT * FROM  user where<if test="userName != null and userName.trim()!=''">username = #{userName}</if><if test="address!=null and address.trim()!=''">AND address = #{address}</if></select>--><!-- SELECT * FROM user WHERE address = ?where子標簽將and去掉了--><select id="queryByUserNameAndAge" resultType="user">SELECT * FROM  user<where><if test="userName != null and userName.trim()!=''">username = #{userName}</if><if test="address!=null and address.trim()!=''">AND address = #{address}</if></where></select>

說明：

1.說明：如果按照如下寫sql語句會有問題，假設用戶名username是空，那么用戶名的sql語句不參與條件，此時
sql語句就會變為：SELECT * FROM user where AND address = #{address}
where后面直接書寫了and顯然不滿足sql語句語法，這里會報錯
我們可以使用where標簽解決上述問題：
where標簽：拼接多條件查詢時 1、能夠添加where關鍵字； 2、能夠去除多余的and或者or關鍵字

2.SELECT * FROM user WHERE address = ?
where子標簽將and去掉了

3.3、測試

@Testpublic void queryByUserNameAndAge() {List<User> userList = userMapper.queryByUserNameAndAge("", "花果山水簾洞");System.out.println("userList = " + userList);}

在這里插入圖片描述

只傳入住址，此時where子標簽去掉了and.

【小結】

1、<where>標簽作用：用于拼接多選一或者同時成立的SQL情況；
2、<where>還會根據情況，動態的去掉SQL語句中的AND或者or；

4、set

set標簽：在update語句中,可以自動添加一個set關鍵字，并且會將動態sql最后多余的逗號去除。

案例：修改用戶信息，如果參數user中的某個屬性為null，則不修改。

如果在正常編寫更新語句時，如下：

在這里插入圖片描述

update user SET username = ?, birthday=?, sex=?, where id = ?

那么一旦在傳遞的參數中沒有address，此時生成的sql語句就會因為多了一個逗號而報錯。

4.1、定義接口方法

在UserMapper接口中定義如下方法：

void updateSelectiveUser(User user);

4.2、編寫SQL

在UserMapper.xml文件中編寫如下SQL:

    <!--選擇性地對user數據進行修改--><update id="updateSelectiveUser">update user<set><if test="username != null and username.trim()!=''">username = #{username},</if><if test="birthday != null">birthday=#{birthday},</if><if test="sex != null and sex.trim()!=''">sex=#{sex},</if><if test="address != null and address.trim()!=''">address=#{address}</if></set>where id = #{id}</update>

4.3、測試

 @Testpublic void updateSelectiveUser() {User user = new User();user.setUsername("鎖哥1");user.setBirthday(new Date());user.setSex("男");user.setAddress("");user.setId(7);userMapper.updateSelectiveUser(user);}

【結果】

 update user SET username = ?, birthday=?, sex=? where id = ?

【小結】

1、<set>標簽替代了sql語句中的set關鍵字；
2、<set>標簽還能把sql中多余的,去掉；

5、foreach

foreach標簽：遍歷集合或者數組
<foreach collection="集合名或者數組名" item="元素" separator="標簽分隔符" open="以什么開始" close="以什么結束">#{元素}
</foreach>collection屬性：接收的集合或者數組,集合名或者數組名item屬性：集合或者數組參數中的每一個元素 separator屬性：標簽分隔符 open屬性：以什么開始 close屬性：以什么結束

需求：按照id值是1，2，3來查詢用戶數據；

5.1、定義接口方法

在UserMapper接口中定義如下方法：

List<User> queryByIds(@Param("arrIds") Integer[] arrIds);

這里一定加@Param(“arrIds”)，否則報錯

5.2、編寫SQL

	<!--根據多個id值查詢--><select id="queryByIds" resultType="user">SELECT * FROM  user WHERE id IN<foreach collection="arrIds" item="ID" separator="," open="(" close=")">#{ID}</foreach></select>

5.3、測試

    @Testpublic void queryByIds() {Integer[] arrIds = {1,2,3};List<User> userList = userMapper.queryByIds(arrIds);System.out.println("userList = " + userList);}

在這里插入圖片描述

【小結】

<foreach>標簽的作用：用于對查詢參數進行遍歷取值；

6、小結

If標簽：條件判斷test屬性：編寫ognl表達式where標簽：用于sql動態條件拼接，添加where關鍵字，可以將動態sql多余的第一個and或者or去除。set標簽: 用于更新語句的拼接，添加set關鍵字，并可以將動態sql中多余的逗號去除foreach標簽：用于遍歷參數中的數組或者集合collection屬性：參數中的數組或者集合item屬性：表示數組或者集合中的某個元素，取出數據使用#{item的屬性值}separator屬性：分隔符open:以什么開始close:以什么結束