背景

××項(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è)列名的都逗號(hào)。

基于OGNL的表達(dá)式的mybatis框架可以徹底解決這種痛苦。

動(dòng)態(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>

動(dòng)態(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元素動(dòng)態(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ù)編譯后,會(huì)動(dòng)態(tài)解析成一個(gè)參數(shù)標(biāo)記符?：

select * from student where studentName = ?

而使用${}在動(dòng)態(tài)解析時(shí)候，會(huì)傳入?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

動(dòng)態(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都會(huì)返回；反之若傳入了“title”，那么就會(huì)對(duì)“title”一列進(jìn)行模糊查找并返回 BLOG 結(jié)果（細(xì)心的讀者可能會(huì)發(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)。針對(duì)這種情況，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è)臭名昭著的動(dòng)態(tài) SQL 問題。現(xiàn)在回到“if”示例，這次我們將“ACTIVE = 1”也設(shè)置成動(dòng)態(tài)的條件，看看會(huì)發(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è)能匹配上會(huì)發(fā)生什么？最終這條 SQL 會(huì)變成這樣：

SELECT * FROM BLOG
WHERE

這會(huì)導(dǎo)致查詢失敗。如果僅僅第二個(gè)條件匹配又會(huì)怎樣？這條 SQL 最終會(huì)是這樣:

SELECT * FROM BLOG
WHERE
AND title like ‘someTitle'

這個(gè)查詢也會(huì)失敗。這個(gè)問題不能簡單地用條件句式來解決，如果你也曾經(jīng)被迫這樣寫過，那么你很可能從此以后都不會(huì)再寫出這種語句了。

MyBatis 有一個(gè)簡單的處理，這在 90% 的情況下都會(huì)有用。而在不能使用的地方，你可以自定義處理方式來令其正常工作。一處簡單的修改就能達(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 元素只會(huì)在至少有一個(gè)子元素的條件返回 SQL 子句的情況下才去插入“WHERE”子句。而且，若語句的開頭為“AND”或“OR”，where 元素也會(huì)將它們?nèi)コ?/p>

如果 where 元素沒有按正常套路出牌，我們可以通過自定義 trim 元素來定制 where 元素的功能。比如，和 where 元素等價(jià)的自定義 trim 元素為：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

prefixOverrides 屬性會(huì)忽略通過管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 屬性中的內(nèi)容，并且插入 prefix 屬性中指定的內(nèi)容。

類似的用于動(dòng)態(tài)更新語句的解決方案叫做 set。set 元素可以用于動(dòng)態(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 元素會(huì)動(dòng)態(tài)前置 SET 關(guān)鍵字，同時(shí)也會(huì)刪掉無關(guān)的逗號(hào)，因?yàn)橛昧藯l件語句之后很可能就會(huì)在生成的 SQL 語句的后面留下這些逗號(hào)。（譯者注：因?yàn)橛玫氖恰癷f”元素，若最后一個(gè)“if”沒有匹配上而前面的匹配上，SQL 語句的最后就會(huì)有一個(gè)逗號(hào)遺留）

若你對(duì) set 元素等價(jià)的自定義 trim 元素的代碼感興趣，那這就是它的真面目：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意這里我們刪去的是后綴值，同時(shí)添加了前綴值。

foreach

動(dòng)態(tài) SQL 的另外一個(gè)常用的操作需求是對(duì)一個(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è)元素是很智能的，因此它不會(huì)偶然地附加多余的分隔符。

-釋義：

-collection ：collection屬性的值有三個(gè)分別是list、array、map三種，分別對(duì)應(yīng)的參數(shù)類型為：List、array、map，上面?zhèn)鞯膮?shù)為數(shù)組，所以值為array

-item ： 表示在迭代過程中每一個(gè)元素的別名

　-index ：表示在迭代過程中每次迭代到的位置（下標(biāo)）

　-open ：前綴

　-close ：后綴

　-separator ：分隔符，表示迭代時(shí)每個(gè)元素之間以什么分隔

我們通?？梢詫⒅玫脚縿h除、添加等操作中。

注意

你可以將任何可迭代對(duì)象（如 List、Set 等）、Map 對(duì)象或者數(shù)組對(duì)象傳遞給 foreach 作為集合參數(shù)。

當(dāng)使用可迭代對(duì)象或者數(shù)組時(shí)，index 是當(dāng)前迭代的次數(shù)，item 的值是本次迭代獲取的元素。

當(dāng)使用 Map 對(duì)象（或者 Map.Entry 對(duì)象的集合）時(shí)，index 是鍵，item 是值。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論