基于mybatis 動態(tài)SQL查詢總結(jié)
背景
××項(xiàng)目需要提供系統(tǒng)部分函數(shù)第三方調(diào)用接口，基于安全性和避免暴露數(shù)據(jù)庫表信息的基礎(chǔ)上進(jìn)行函數(shù)接口的設(shè)計(jì)，根據(jù)第三方調(diào)用身份的權(quán)限提供某張表的自定義集合。
本項(xiàng)目基于mybatis的持久層框架，支持定制化的SQL，這樣可以避免拼接sql語句的痛苦。
例如拼接時(shí)要確保不能添加空格，還要注意去掉列表的最后一個(gè)列名的都逗號。
基于OGNL的表達(dá)式的mybatis框架可以徹底解決這種痛苦。
動態(tài)返回mysql某張表指定列的名字，類型和注釋
<select id="queryColumns" resultType="map" parameterType="java.util.HashMap">
select column_name columnName, data_type dataType, column_comment columnComment
from information_schema.columns
where table_name = #{tablename}
and
column_name in
<foreach collection="columnsArray" item="column_name" index="index" open="(" close=")" separator=",">
#{column_name}
</foreach>
</select>
動態(tài)查詢某個(gè)表的指定列數(shù)據(jù)
<select id="query1" resultType="map" parameterType="java.util.HashMap">
select
<foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
${item}
</foreach>
from #{tableName} tn
</select>
利用 if和foreach元素動態(tài)的拼接where 條件部分
<select id="query2" resultType="map" parameterType="java.util.HashMap">
select
<foreach collection="columnsArray" item="item" index="index" open="" separator="," close="" >
${item}
</foreach>
from #{tableName} db
where
<if test ="name !=null">
db.name=#{name} and
</if>
db.LastModifyTime between #{datestart,jdbcType=TIMESTAMP} and #{dateend,jdbcType=TIMESTAMP}
</select>
傳參數(shù) ${} 和#{}區(qū)別
Mybatis 的Mapper.xml語句中parameterType向SQL語句傳參有兩種方式：#{}和${}
我們經(jīng)常使用的是#{},一般解說是因?yàn)檫@種方式可以防止SQL注入，簡單的說#{}這種方式SQL語句是經(jīng)過預(yù)編譯的，它是把#{}中間的參數(shù)轉(zhuǎn)義成字符串，舉個(gè)例子：
select * from student where studentName = #{name}
預(yù)編譯后,會動態(tài)解析成一個(gè)參數(shù)標(biāo)記符?：
select * from student where studentName = ?
而使用${}在動態(tài)解析時(shí)候，會傳入?yún)?shù)字符串
select * from student where studentName = 'lyrics'
-看完上面的一些例子，可以看到主要用到了if 、foreach等元素，mybatis之前的版本，有很多的元素需要了解，而mybatis大大精簡了元素種類，現(xiàn)在只需要學(xué)習(xí)以下幾個(gè)元素：
-if
-choose(when,otherwise)
-trim(where,set)
-foreach
-- 引用[http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html]
if
動態(tài)SQL通常要做的是根據(jù)條件包含where子句的一部分。比如：
<select id="findActiveBlogWithTitleLike" resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
</select>
這條語句提供了一種可選的查找文本功能。如果沒有傳入“title”，那么所有處于“ACTIVE”狀態(tài)的BLOG都會返回；反之若傳入了“title”，那么就會對“title”一列進(jìn)行模糊查找并返回 BLOG 結(jié)果（細(xì)心的讀者可能會發(fā)現(xiàn)，“title”參數(shù)值是可以包含一些掩碼或通配符的）。
如果希望通過“title”和“author”兩個(gè)參數(shù)進(jìn)行可選搜索該怎么辦呢？首先，改變語句的名稱讓它更具實(shí)際意義；然后只要加入另一個(gè)條件即可。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
choose,when,otherwise
有時(shí)我們不想應(yīng)用到所有的條件語句，而只想從中擇其一項(xiàng)。針對這種情況，MyBatis 提供了 choose 元素，它有點(diǎn)像 Java 中的 switch 語句。
還是上面的例子，但是這次變?yōu)樘峁┝恕皌itle”就按“title”查找，提供了“author”就按“author”查找的情形，若兩者都沒有提供，就返回所有符合條件的 BLOG（實(shí)際情況可能是由管理員按一定策略選出 BLOG 列表，而不是返回大量無意義的隨機(jī)結(jié)果）。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE'
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
trim, where, set
前面幾個(gè)例子已經(jīng)合宜地解決了一個(gè)臭名昭著的動態(tài) SQL 問題。現(xiàn)在回到“if”示例，這次我們將“ACTIVE = 1”也設(shè)置成動態(tài)的條件，看看會發(fā)生什么。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
如果這些條件沒有一個(gè)能匹配上會發(fā)生什么？最終這條 SQL 會變成這樣：
SELECT * FROM BLOG WHERE
這會導(dǎo)致查詢失敗。如果僅僅第二個(gè)條件匹配又會怎樣？這條 SQL 最終會是這樣:
SELECT * FROM BLOG WHERE AND title like ‘someTitle'
這個(gè)查詢也會失敗。這個(gè)問題不能簡單地用條件句式來解決，如果你也曾經(jīng)被迫這樣寫過，那么你很可能從此以后都不會再寫出這種語句了。
MyBatis 有一個(gè)簡單的處理，這在 90% 的情況下都會有用。而在不能使用的地方，你可以自定義處理方式來令其正常工作。一處簡單的修改就能達(dá)到目的：
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
where 元素只會在至少有一個(gè)子元素的條件返回 SQL 子句的情況下才去插入“WHERE”子句。而且，若語句的開頭為“AND”或“OR”，where 元素也會將它們?nèi)コ?/p>
如果 where 元素沒有按正常套路出牌，我們可以通過自定義 trim 元素來定制 where 元素的功能。比如，和 where 元素等價(jià)的自定義 trim 元素為：
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
prefixOverrides 屬性會忽略通過管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 屬性中的內(nèi)容，并且插入 prefix 屬性中指定的內(nèi)容。
類似的用于動態(tài)更新語句的解決方案叫做 set。set 元素可以用于動態(tài)包含需要更新的列，而舍去其它的。比如：
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
這里，set 元素會動態(tài)前置 SET 關(guān)鍵字，同時(shí)也會刪掉無關(guān)的逗號，因?yàn)橛昧藯l件語句之后很可能就會在生成的 SQL 語句的后面留下這些逗號。（譯者注：因?yàn)橛玫氖恰癷f”元素，若最后一個(gè)“if”沒有匹配上而前面的匹配上，SQL 語句的最后就會有一個(gè)逗號遺留）
若你對 set 元素等價(jià)的自定義 trim 元素的代碼感興趣，那這就是它的真面目：
<trim prefix="SET" suffixOverrides=","> ... </trim>
注意這里我們刪去的是后綴值，同時(shí)添加了前綴值。
foreach
動態(tài) SQL 的另外一個(gè)常用的操作需求是對一個(gè)集合進(jìn)行遍歷，通常是在構(gòu)建 IN 條件語句的時(shí)候。比如：
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
foreach 元素的功能非常強(qiáng)大，它允許你指定一個(gè)集合，聲明可以在元素體內(nèi)使用的集合項(xiàng)（item）和索引（index）變量。它也允許你指定開頭與結(jié)尾的字符串以及在迭代結(jié)果之間放置分隔符。這個(gè)元素是很智能的，因此它不會偶然地附加多余的分隔符。
-釋義：
-collection ：collection屬性的值有三個(gè)分別是list、array、map三種，分別對應(yīng)的參數(shù)類型為：List、array、map，上面?zhèn)鞯膮?shù)為數(shù)組，所以值為array
-item ： 表示在迭代過程中每一個(gè)元素的別名
-index ：表示在迭代過程中每次迭代到的位置（下標(biāo)）
-open ：前綴
-close ：后綴
-separator ：分隔符，表示迭代時(shí)每個(gè)元素之間以什么分隔
我們通?？梢詫⒅玫脚縿h除、添加等操作中。
注意
你可以將任何可迭代對象（如 List、Set 等）、Map 對象或者數(shù)組對象傳遞給 foreach 作為集合參數(shù)。
當(dāng)使用可迭代對象或者數(shù)組時(shí)，index 是當(dāng)前迭代的次數(shù)，item 的值是本次迭代獲取的元素。
當(dāng)使用 Map 對象（或者 Map.Entry 對象的集合）時(shí)，index 是鍵，item 是值。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
在Spring和SpringMVC中,注解的使用大大簡化了配置,提高了代碼的可讀性和可維護(hù)性,這篇文章主要給大家介紹了關(guān)于SpringMVC中常用注解和用法的相關(guān)資料,需要的朋友可以參考下2024-08-08
這篇文章主要介紹了springboot獲取profile的操作，文中的示例代碼講解詳細(xì)，具有很好的參考價(jià)值，希望對大家有所幫助2022-04-04
我們本章開始學(xué)習(xí)Jmeter，后續(xù)還會有RF以及LoadRunner 的介紹，為什么要學(xué)習(xí)Jmeter，它主要是用來做性能測試的，其中它也需要間接或直接的需要用到抓包工具2021-09-09
這篇文章主要介紹了CorsFilter 過濾器解決跨域的處理操作，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-06-06
這篇文章主要介紹了Java中Json解析的方法,結(jié)合實(shí)例形式分析了java針對json格式數(shù)據(jù)的解析實(shí)現(xiàn)步驟與相關(guān)操作技巧,需要的朋友可以參考下2017-05-05
深入學(xué)習(xí)JAVA GC日志的相關(guān)知識
JVM 在Java應(yīng)用程序優(yōu)化中是不可缺少的一大重項(xiàng)，如何合理配置Java參數(shù)，如何驗(yàn)證配置參數(shù)的有效性，從GC日志中可以獲得很重要的提示。下面小編就帶大家來一起學(xué)習(xí)一下吧2019-06-06
Java使用延時(shí)隊(duì)列搞定超時(shí)訂單處理的場景
這篇文章主要介紹了Java使用延時(shí)隊(duì)列搞定超時(shí)訂單處理,本文通過示例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-08-08
這篇文章主要給大家介紹了關(guān)于Spring boot配置文件加解密的相關(guān)資料，文中通過示例代碼以及圖文介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
這篇文章主要幫助大家輕松掌握J(rèn)ava模板模式,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-09-09
這篇文章主要介紹了Java多線程中的原子類屬性說明,對多線程訪問同一個(gè)變量,我們需要加鎖,而鎖是比較消耗性能的,JDk1.5之后,新增的原子操作類提供了一種用法簡單、性能高效、線程安全地更新一個(gè)變量的方式,需要的朋友可以參考下2023-10-10