MyBatis編寫(xiě)動(dòng)態(tài)SQL實(shí)例
動(dòng)態(tài) SQL 是 MyBatis 的強(qiáng)大特性之一。如果你使用過(guò) JDBC 或其它類(lèi)似的框架，你應(yīng)該能理解根據(jù)不同條件拼接 SQL 語(yǔ)句有多痛苦，例如拼接時(shí)要確保不能忘記添加必要的空格，還要注意去掉列表最后一個(gè)列名的逗號(hào)。利用動(dòng)態(tài) SQL，可以徹底擺脫這種痛苦。
使用動(dòng)態(tài) SQL 并非一件易事，但借助可用于任何 SQL 映射語(yǔ)句中的強(qiáng)大的動(dòng)態(tài) SQL 語(yǔ)言，MyBatis 顯著地提升了這一特性的易用性。
如果你之前用過(guò) JSTL 或任何基于類(lèi) XML 語(yǔ)言的文本處理器，你對(duì)動(dòng)態(tài) SQL 元素可能會(huì)感覺(jué)似曾相識(shí)。
在 MyBatis 之前的版本中，需要花時(shí)間了解大量的元素。
借助功能強(qiáng)大的基于 OGNL 的表達(dá)式，MyBatis 3 替換了之前的大部分元素，大大精簡(jiǎn)了元素種類(lèi)，現(xiàn)在要學(xué)習(xí)的標(biāo)簽元素種類(lèi)比原來(lái)的一半還要少：
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
- bind
- sql片段
元素 | 作用 | 備注 |
if | 判斷語(yǔ)句 | 單條件分支判斷 |
choose、when、otherwise | 相當(dāng)于java的case when | 多條件分支判斷 |
Trim、where、set | 輔助元素 | 用于處理sql拼裝問(wèn)題 |
foreach | 循環(huán)語(yǔ)句 | 在in語(yǔ)句等列舉條件常用，常用于實(shí)現(xiàn)批量操作 |
一、if
EmpDao.xml
<select id="getEmpByCondition" resultType="cn.tulingxueyuan.bean.Emp">
select * from emp where
<if test="empno!=null">
empno > #{empno} and
</if>
<if test="ename!=null">
ename like #{ename} and
</if>
<if test="sal!=null">
sal > #{sal}
</if>
</select>EmpDao.java
public List<Emp> getEmpByCondition(Emp emp);
Test.java
@Test
public void test10() {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp emp = new Emp();
emp.setEmpno(6500);
emp.setEname("%E%");
emp.setSal(500.0);
List<Emp> empByCondition = mapper.getEmpByCondition(emp);
for (Emp emp1 : empByCondition) {
System.out.println(emp1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
看起來(lái)測(cè)試是比較正常的，但是大家需要注意的是如果我們傳入的參數(shù)值有缺失會(huì)怎么呢？這個(gè)時(shí)候拼接的sql語(yǔ)句就會(huì)變得有問(wèn)題，例如不傳參數(shù)或者丟失最后一個(gè)參數(shù)，那么語(yǔ)句中就會(huì)多一個(gè)where或者and的關(guān)鍵字。
可以通過(guò)在where條件的最前面加上"1 = 1"來(lái)解決這個(gè)問(wèn)題。加入條件“1=1”后，既保證了where后面的條件成立，又避免了where后面第一個(gè)詞是and或者or之類(lèi)的關(guān)鍵詞。但是這樣寫(xiě)雖然可以實(shí)現(xiàn)功能，但存在SQL注入的風(fēng)險(xiǎn)，而且可能會(huì)造成索引失效全表掃描。
因此MyBatis提供了where標(biāo)簽可以代替where 1=1。
二、where
where 元素只會(huì)在子元素返回任何內(nèi)容的情況下才插入 “WHERE” 子句。而且，若子句的開(kāi)頭為 “AND” 或 “OR”，where 元素也會(huì)將它們?nèi)コ?/p>
<select id="getEmpByCondition" resultType="cn.tulingxueyuan.bean.Emp">
select * from emp
<where>
<if test="empno!=null">
empno > #{empno}
</if>
<if test="ename!=null">
and ename like #{ename}
</if>
<if test="sal!=null">
and sal > #{sal}
</if>
</where>
</select>【作用】
自動(dòng)去除首個(gè)條件中的“AND”或“OR”
注：
1、按照標(biāo)準(zhǔn)寫(xiě)法，第一個(gè)if標(biāo)簽內(nèi)的AND應(yīng)該不寫(xiě)，但是，就算開(kāi)發(fā)中書(shū)寫(xiě)也不會(huì)報(bào)錯(cuò)。因?yàn)閣here標(biāo)簽會(huì)自動(dòng)的移除了第一個(gè)AND鏈接。但是，第二個(gè)之后的if標(biāo)簽內(nèi)，必須有AND鏈接。
2、如果沒(méi)有一個(gè)條件符合，則返回所有條目。
三、trim
該標(biāo)簽的功能與where類(lèi)似，并且額外的提供了前綴后綴功能。具體用法如下：
<!--
trim截取字符串：
prefix：前綴，為sql整體添加一個(gè)前綴
prefixOverrides:去除整體字符串前面多余的字符
suffixOverrides:去除后面多余的字符串
-->
<select id="getEmpByCondition" resultType="cn.tulingxueyuan.bean.Emp">
select * from emp
<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
<if test="empno!=null">
empno > #{empno} and
</if>
<if test="ename!=null">
ename like #{ename} and
</if>
<if test="sal!=null">
sal > #{sal} and
</if>
</trim>
</select>【作用】
1、可以用trim替代where標(biāo)簽。
2、屬性 prefix=“where”，表示：加前綴 where。
3、屬性 suffix=“)”，表示：加后綴 )。
4、屬性 prefixOverrides=“and|or”，前綴覆蓋：自動(dòng)覆蓋第一個(gè)and或者or。
5、屬性 suffixOverrides=“”，后綴覆蓋：去掉整個(gè)字符串后面多余的字符。
trim擴(kuò)展
trim標(biāo)簽的使用場(chǎng)景比where更多，如下：
<if test="params.businessTypeList != null and params.businessTypeList.size() > 0 ">
<trim prefix="and ( " prefixOverrides="AND|OR" suffix=" )">
<foreach collection="params.businessTypeList" item="idItem" index="index">
OR FIND_IN_SET( #{idItem}, space_types )
</foreach>
</trim>
</if>在循環(huán)的時(shí)候拼接多個(gè)sql條件，用trim標(biāo)簽的prefixOverrides屬性覆蓋第一個(gè)and或者or，前綴加上"and (“，后綴加上” )"，動(dòng)態(tài)生成一個(gè)條件滿足多種情況的sql。
四、foreach
動(dòng)態(tài) SQL 的另一個(gè)常見(jiàn)使用場(chǎng)景是對(duì)集合進(jìn)行遍歷（尤其是在構(gòu)建 IN 條件語(yǔ)句的時(shí)候）。
<!--foreach是對(duì)集合進(jìn)行遍歷
collection="deptnos" 指定要遍歷的集合
close="" 表示以什么結(jié)束
index="" 給定一個(gè)索引值
item="" 遍歷的每一個(gè)元素的值
open="" 表示以什么開(kāi)始
separator="" 表示多個(gè)元素的分隔符
-->
<select id="getEmpByDeptnos" resultType="Emp">
select * from emp where deptno in
<foreach collection="deptnos" close=")" index="idx" item="deptno" open="(" separator=",">
#{deptno}
</foreach>
</select>高頻面試題：通過(guò)Mybatis怎么樣進(jìn)行批量的操作
- 通過(guò)foreach動(dòng)態(tài)拼裝SQL語(yǔ)句（這個(gè)速度跟快更快，因?yàn)橹缓蛿?shù)據(jù)庫(kù)交互一次）
- 使用BATCH類(lèi)型的excutor
五、choose、when、otherwise
有時(shí)候，我們不想使用所有的條件，而只是想從多個(gè)條件中選擇一個(gè)使用。針對(duì)這種情況，MyBatis 提供了 choose 元素，它有點(diǎn)像 Java 中的 switch 語(yǔ)句。
<select id="getEmpByConditionChoose" resultType="cn.tulingxueyuan.bean.Emp">
select * from emp
<where>
<choose>
<when test="empno!=null">
empno > #{empno}
</when>
<when test="ename!=null">
ename like #{ename}
</when>
<when test="sal!=null">
sal > #{sal}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>六、set
用于動(dòng)態(tài)更新語(yǔ)句的類(lèi)似解決方案叫做 set。set 元素可以用于動(dòng)態(tài)包含需要更新的列，忽略其它不更新的列。
<update id="updateEmpByEmpno">
update emp
<set>
<if test="empno!=null">
empno=#{empno},
</if>
<if test="ename!=null">
ename = #{ename},
</if>
<if test="sal!=null">
sal = #{sal}
</if>
</set>
<where>
empno = #{empno}
</where>
</update>七、bind
bind 元素允許你在 OGNL 表達(dá)式以外創(chuàng)建一個(gè)變量，并將其綁定到當(dāng)前的上下文。
比如：
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>八、sql
sql元素：用來(lái)定義可重用的 SQL 代碼段，可以包含在其他語(yǔ)句中；
這個(gè)元素可以用來(lái)定義可重用的 SQL 代碼片段，以便在其它語(yǔ)句中使用。 參數(shù)可以靜態(tài)地（在加載的時(shí)候）確定下來(lái)，并且可以在不同的 include 元素中定義不同的參數(shù)值。比如：
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>這個(gè) SQL 片段可以在其它語(yǔ)句中使用，例如：
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from some_table t1
cross join some_table t2
</select>九、MyBatis常用OGNL表達(dá)式
OGNL表達(dá)式表達(dá)式就是寫(xiě)在if標(biāo)簽里面的那些判斷條件。
- e1 or e2
- e1 and e2
- e1 == e2 判斷值是否相同
- e1 eq e2 判斷字符串是否相同
- e1 != e2 判斷值是否不相等
- e1 neq e2 判斷字符串是否不相等
- e1 lt e2 小于
- e1 lte e2 小于等于
- gt 大于
- gte 大于等于
- e1 in e2 判斷e1是否在e2集合中
- e1 not in e2
- e1 + e2, e1 * e2, e1/e2, e1 - e2, e1%e2
- !e 非運(yùn)算，求反
- not e 同上
- e.method(args) 調(diào)用對(duì)象方法
- e.property 對(duì)象屬性值
- e1[e2]按索引取值，List,數(shù)組和Map
- @class@method(args)調(diào)用類(lèi)的靜態(tài)方法
- @class@field調(diào)用類(lèi)的靜態(tài)字段值
十、總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
對(duì)MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程
這篇文章主要介紹了對(duì)MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程,文中提到的Query-Digest-UI這個(gè)基于B/S的圖形化查看工具非常好用,需要的朋友可以參考下2015-12-12
MySQL中year()和month()函數(shù)解析與輸出示例詳解
這篇文章主要介紹了MySQL中year()和month()函數(shù)解析與輸出,通過(guò)本文，我們?cè)敿?xì)了解了MySQL中year()和month()函數(shù)的底層邏輯，它們能夠從日期或日期時(shí)間類(lèi)型的數(shù)據(jù)中提取年份和月份，需要的朋友可以參考下2023-07-07
這篇文章主要為大家詳細(xì)介紹了MySQL中出現(xiàn)索引失效的原因以及相關(guān)優(yōu)化方案,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2025-07-07
這篇文章主要為大家詳細(xì)介紹了MySQL數(shù)據(jù)庫(kù)中表常用的一些操作方法，文中的示例代碼講解詳細(xì)， 對(duì)我們學(xué)習(xí)MySQL有一定幫助，需要的可以參考一下2022-09-09
windows下mysql 8.0.12安裝步驟及基本使用教程
這篇文章主要為大家詳細(xì)介紹了windows下mysql 8.0.12安裝步驟及基本使用教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-08-08
Mysql 存在多條數(shù)據(jù)時(shí)如何按時(shí)間取最新的那一組數(shù)據(jù)(思路詳解)
這篇文章主要介紹了Mysql 存在多條數(shù)據(jù)時(shí)如何按時(shí)間取最新的那一組數(shù)據(jù),本文給大家分享兩種思路結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2024-04-04
最近生產(chǎn)爆出一條慢sql，原因是用了or和!=，導(dǎo)致索引失效。于是，總結(jié)了索引失效的十大雜癥，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-09-09
這篇文章主要介紹了MySQL服務(wù)器線程數(shù)的查看方法,結(jié)合實(shí)例形式分析了mysql線程數(shù)查看的相關(guān)命令、配置、參數(shù)及相關(guān)使用技巧,需要的朋友可以參考下2018-03-03
MySQL UPDATE 語(yǔ)句的非標(biāo)準(zhǔn)實(shí)現(xiàn)代碼
這篇文章主要介紹了MySQL UPDATE 語(yǔ)句的非標(biāo)準(zhǔn)實(shí)現(xiàn)代碼,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-04-04
這篇文章主要為大家分享了win7下MySql 5.7安裝配置方法圖文教程，感興趣的小伙伴們可以參考一下2016-05-05