什麼是動態sql

• MyBatis 的強大特性之一便是它的動態 SQL，它極大的簡化了我們拼接SQL的操作。
• 動態 SQL 元素和使用 JSTL 或其他類似基於 XML 的文本處理器相似。
• MyBatis 採用功能強大的基於 OGNL 的表達式來消除其他元素：

1. if
2. choose (when, otherwise)
3. trim (where, set)
4. foreach

if條件判斷

• 查詢學生，查詢條件中攜帶了哪個欄位，就把欄位拼接上

<!-- 查詢條件中攜帶了哪個欄位，就把欄位拼接上 -->
<!-- public List<Student>ListStudentByCondition(Student student) ; -->
<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
select *
from student
where
<!-- test:OGNL表達式
從參數中取值
遇到特殊字元應用轉義字元:見下圖
-->
<if test="id!=null">
id = #{id}
</if>
<if test="name!=null and name.trim()!=""">
and name = #{name}
</if>
<if test="age!=null && age!=""">
and age = #{age}
</if>
<if test="sex=="M" or sex=="F"" >
and trim(sex) = #{sex}
</if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 轉義字元表：

註：char類型查詢時判斷條件不正確的問題

特別注意：我使用的是ORACLE，性別欄位為char長度為2，Mybatis在查出數據後會自動的將char的長度補為2得到的就是』F 『(有一個空格)，因此導致sex=#{sex}的判斷結果不正確，解決辦法就是在查出數據之後使用trim()方法去掉多餘的空格。

問題：如上所述，可以使用if方法進行判斷，符合條件的進行拼接，但也會出現問題，如果第一個欄位為空的話就會導致字元串拼接失敗造成如下結果：

### SQL: select * from student where and trim(sex) = ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00936: 缺失表達式
1
2

那麼怎麼解決這個問題呢？

• 解決方式一：（給where加上永真條件1=1，之後全用and連接）

<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
select *
from student
where 1=1
<!-- test:OGNL表達式
從參數中取值
遇到特殊字元應用轉義字元:見下圖
-->
<if test="id!=null">
and id = #{id}
</if>
<if test="name!=null and name.trim()!=""">
and name = #{name}
</if>
<if test="age!=null && age!=""">
and age = #{age}
</if>
<if test="sex=="M" or sex=="F"" >
and trim(sex) = #{sex}
</if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

• 解決方式二：（使用where標籤）

<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
select *
from student
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="name!=null and name.trim()!=""">
and name = #{name}
</if>
<if test="age!=null && age!=""">
and age = #{age}
</if>
<if test="sex=="M" or sex=="F"" >
and trim(sex) = #{sex}
</if>
</where>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Trim字元串拼接

以上問題還可以使用trim字元串拼接來解決問題。

• 解決方式三：使用trim標籤拼接

<select id="ListStudentByConditionTrim" resultType="com.cn.cmc.bean.Student" >
<!-- trim標籤拼接字元串
prefix：給拼串後的字元串加上一個前綴
prefixOverrides：去掉拼串後的字元串的一個前綴
suffix：給拼串後的字元串加上一個後綴
suffixOverrides：去掉拼串後字元串的一個前綴
set標籤：在更新中使用，可以去除多餘的逗號
-->
select *
from student
<trim prefix="where" prefixOverrides="and" suffix="" suffixOverrides="">
<if test="id!=null">
and id = #{id}
</if>
<if test="name!=null and name.trim()!=""">
and name = #{name}
</if>
<if test="age!=null && age!=""">
and age = #{age}
</if>
<if test="sex=="M" or sex=="F"" >
and trim(sex) = #{sex}
</if>
</trim>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• bind標籤的使用，按名字模糊查詢學生
• bind 元素可以從 OGNL 表達式中創建一個變數並將其綁定到元素中方便使用

<!-- 按名字模糊查詢學生，假設在介面中有方法public Student getStudentByName(@Param("name")String name) ;
bind標籤：將OGNL表達式綁定到指定變數中
name:綁定的變數名
value：OGNL表達式
-->
<select id="getStudentByName" resultType="com.cn.cmc.bean.Student">
<bind name="_name" value=""%"+name+"%""/>
select *
from student
where name like #{_name}
</select>
1
2
3
4
5
6
7
8
9
10
11

choose（選擇分支）

• 查詢學生，要求傳入什麼欄位按什麼欄位來查

<!-- choose, when, otherwise查詢學生，有哪個欄位按哪個欄位查 -->
<!-- choose分支選擇器：類似於switch-case-default的用法
when：判斷進入那個分支類似於case
otherwise:當所有情況都不匹配時，執行的操作
-->
<select id="ListStudentByConditionChoose" resultType="com.cn.cmc.bean.Student">
select *
from student
<where>
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="name!=null and name!=""">
name = #{name}
</when>
<when test="sex=="F" or sex=="M"">
trim(sex) = #{sex}
</when>
<when test="age!=0">
age = #{age}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

註：choose一次只能選擇一條分支

特別注意：在設計的時候，Student的age欄位我使用int這個類型，這是不合理的，因為java中默認int的屬性為0，造成判斷的時候age永不為空，應用Integer類型

foreach集合遍歷

動態 SQL 的另外一個常用的必要操作是需要對一個集合進行遍歷，通常是在構建 IN 條件語句的時候。

• 按id條件構建In來查出學生

<!-- foreach集合的遍歷，通常是構建in條件語句的時候使用
collection:指定遍歷的集合名：
Mybatis會將傳入的list封裝到map中，map中的鍵就是list
也可以用註解@param("")決定所封裝的別名
item：將當前遍歷的對象取一個名方便調用，調用方法#{對象名}
index：若遍歷的對象是list時，index就表示list的索引,item表示當前的值
若遍歷的對象是一個map的時候，index表示map的key,item表示map的value
open：遍歷的開始符
close：遍歷的結束符
separator：表示遍曆元素之間的分隔符
-->
<select id="ListStudentByConditionForeach" resultType="com.cn.cmc.bean.Student">
select *
from student
<foreach collection="list" item="item_id" open="where id in(" close=")" separator=",">
#{item_id}
</foreach>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

• 批量保存學生
• 相關SQL語句為：

begin
insert into student(id,name,sex,age)
values(?,?,?,?);
insert into student(id,name,sex,age)
values(?,?,?,?);
end ;
1
2
3
4
5
6
7
8

• 在Mybatis中這樣配置

<!-- 批量插入學生 -->
<insert id="addAllStudent" parameterType="com.cn.cmc.bean.Student">
<foreach collection="students" item="std_item" open="begin" close="end;" separator="">
insert into student(id,name,sex,age)
values (student_seq.nextval,#{std_item.name},#{std_item.sex},#{std_item.age});
</foreach>
</insert>

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！