基於mybatis 動態SQL查詢總結
背景
××項目需要提供系統部分函數第三方調用接口，基於安全性和避免暴露數據庫表信息的基礎上進行函數接口的設計，根據第三方調用身份的權限提供某張表的自定義集合。
本項目基於mybatis的持久層框架，支持定制化的SQL，這樣可以避免拼接sql語句的痛苦。
例如拼接時要確保不能添加空格，還要註意去掉列表的最後一個列名的都逗號。
基於OGNL的表達式的mybatis框架可以徹底解決這種痛苦。
動態返回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>
動態查詢某個表的指定列數據
<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元素動態的拼接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>
傳參數 ${} 和#{}區別
Mybatis 的Mapper.xml語句中parameterType向SQL語句傳參有兩種方式：#{}和${}
我們經常使用的是#{},一般解說是因為這種方式可以防止SQL註入，簡單的說#{}這種方式SQL語句是經過預編譯的，它是把#{}中間的參數轉義成字符串，舉個例子：
select * from student where studentName = #{name}
預編譯後,會動態解析成一個參數標記符?：
select * from student where studentName = ?
而使用${}在動態解析時候，會傳入參數字符串
select * from student where studentName = 'lyrics'
-看完上面的一些例子，可以看到主要用到瞭if 、foreach等元素，mybatis之前的版本，有很多的元素需要瞭解，而mybatis大大精簡瞭元素種類，現在隻需要學習以下幾個元素：
-if
-choose(when,otherwise)
-trim(where,set)
-foreach
— 引用[http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html]
if
動態SQL通常要做的是根據條件包含where子句的一部分。比如：
<select id="findActiveBlogWithTitleLike" resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
</select>
這條語句提供瞭一種可選的查找文本功能。如果沒有傳入“title”，那麼所有處於“ACTIVE”狀態的BLOG都會返回；反之若傳入瞭“title”，那麼就會對“title”一列進行模糊查找並返回 BLOG 結果（細心的讀者可能會發現，“title”參數值是可以包含一些掩碼或通配符的）。
如果希望通過“title”和“author”兩個參數進行可選搜索該怎麼辦呢？首先，改變語句的名稱讓它更具實際意義；然後隻要加入另一個條件即可。
<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
有時我們不想應用到所有的條件語句，而隻想從中擇其一項。針對這種情況，MyBatis 提供瞭 choose 元素，它有點像 Java 中的 switch 語句。
還是上面的例子，但是這次變為提供瞭“title”就按“title”查找，提供瞭“author”就按“author”查找的情形，若兩者都沒有提供，就返回所有符合條件的 BLOG（實際情況可能是由管理員按一定策略選出 BLOG 列表，而不是返回大量無意義的隨機結果）。
<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
前面幾個例子已經合宜地解決瞭一個臭名昭著的動態 SQL 問題。現在回到“if”示例，這次我們將“ACTIVE = 1”也設置成動態的條件，看看會發生什麼。
<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>
如果這些條件沒有一個能匹配上會發生什麼？最終這條 SQL 會變成這樣：
SELECT * FROM BLOG WHERE
這會導致查詢失敗。如果僅僅第二個條件匹配又會怎樣？這條 SQL 最終會是這樣:
SELECT * FROM BLOG WHERE AND title like ‘someTitle'
這個查詢也會失敗。這個問題不能簡單地用條件句式來解決，如果你也曾經被迫這樣寫過，那麼你很可能從此以後都不會再寫出這種語句瞭。
MyBatis 有一個簡單的處理，這在 90% 的情況下都會有用。而在不能使用的地方，你可以自定義處理方式來令其正常工作。一處簡單的修改就能達到目的：
<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 元素隻會在至少有一個子元素的條件返回 SQL 子句的情況下才去插入“WHERE”子句。而且，若語句的開頭為“AND”或“OR”，where 元素也會將它們去除。
如果 where 元素沒有按正常套路出牌，我們可以通過自定義 trim 元素來定制 where 元素的功能。比如，和 where 元素等價的自定義 trim 元素為：
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
prefixOverrides 屬性會忽略通過管道分隔的文本序列（註意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 屬性中的內容，並且插入 prefix 屬性中指定的內容。
類似的用於動態更新語句的解決方案叫做 set。set 元素可以用於動態包含需要更新的列，而舍去其它的。比如：
<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 元素會動態前置 SET 關鍵字，同時也會刪掉無關的逗號，因為用瞭條件語句之後很可能就會在生成的 SQL 語句的後面留下這些逗號。（譯者註：因為用的是“if”元素，若最後一個“if”沒有匹配上而前面的匹配上，SQL 語句的最後就會有一個逗號遺留）
若你對 set 元素等價的自定義 trim 元素的代碼感興趣，那這就是它的真面目：
<trim prefix="SET" suffixOverrides=","> ... </trim>
註意這裡我們刪去的是後綴值，同時添加瞭前綴值。
foreach
動態 SQL 的另外一個常用的操作需求是對一個集合進行遍歷，通常是在構建 IN 條件語句的時候。比如：
<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 元素的功能非常強大，它允許你指定一個集合，聲明可以在元素體內使用的集合項（item）和索引（index）變量。它也允許你指定開頭與結尾的字符串以及在迭代結果之間放置分隔符。這個元素是很智能的，因此它不會偶然地附加多餘的分隔符。
-釋義：
–collection ：collection屬性的值有三個分別是list、array、map三種，分別對應的參數類型為：List、array、map，上面傳的參數為數組，所以值為array
–item ： 表示在迭代過程中每一個元素的別名
-index ：表示在迭代過程中每次迭代到的位置（下標）
-open ：前綴
-close ：後綴
-separator ：分隔符，表示迭代時每個元素之間以什麼分隔
我們通常可以將之用到批量刪除、添加等操作中。
註意
你可以將任何可迭代對象（如 List、Set 等）、Map 對象或者數組對象傳遞給 foreach 作為集合參數。
當使用可迭代對象或者數組時，index 是當前迭代的次數，item 的值是本次迭代獲取的元素。
當使用 Map 對象（或者 Map.Entry 對象的集合）時，index 是鍵，item 是值。
以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。