mybatis 動(dòng)態(tài)SQL查詢方法總結(jié)

這篇文章主要講解了“mybatis 動(dòng)態(tài)SQL查詢方法總結(jié)”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“mybatis 動(dòng)態(tài)SQL查詢方法總結(jié)”吧！

背景

××項(xiàng)目需要提供系統(tǒng)部分函數(shù)第三方調(diào)用接口，基于安全性和避免暴露數(shù)據(jù)庫(kù)表信息的基礎(chǔ)上進(jìn)行函數(shù)接口的設(shè)計(jì)，根據(jù)第三方調(diào)用身份的權(quán)限提供某張表的自定義集合。

本項(xiàng)目基于mybatis的持久層框架，支持定制化的SQL，這樣可以避免拼接sql語(yǔ)句的痛苦。

例如拼接時(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語(yǔ)句中parameterType向SQL語(yǔ)句傳參有兩種方式：#{}和${}

我們經(jīng)常使用的是#{},一般解說(shuō)是因?yàn)檫@種方式可以防止SQL注入，簡(jiǎn)單的說(shuō)#{}這種方式SQL語(yǔ)句是經(jīng)過(guò)預(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大大精簡(jiǎn)了元素種類，現(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>

這條語(yǔ)句提供了一種可選的查找文本功能。如果沒(méi)有傳入“title”，那么所有處于“ACTIVE”狀態(tài)的BLOG都會(huì)返回；反之若傳入了“title”，那么就會(huì)對(duì)“title”一列進(jìn)行模糊查找并返回 BLOG 結(jié)果（細(xì)心的讀者可能會(huì)發(fā)現(xiàn)，“title”參數(shù)值是可以包含一些掩碼或通配符的）。

如果希望通過(guò)“title”和“author”兩個(gè)參數(shù)進(jìn)行可選搜索該怎么辦呢？首先，改變語(yǔ)句的名稱讓它更具實(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)用到所有的條件語(yǔ)句，而只想從中擇其一項(xiàng)。針對(duì)這種情況，MyBatis 提供了 choose 元素，它有點(diǎn)像 Java 中的 switch 語(yǔ)句。

還是上面的例子，但是這次變?yōu)樘峁┝恕皌itle”就按“title”查找，提供了“author”就按“author”查找的情形，若兩者都沒(méi)有提供，就返回所有符合條件的 BLOG（實(shí)際情況可能是由管理員按一定策略選出 BLOG 列表，而不是返回大量無(wú)意義的隨機(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 問(wèn)題?，F(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>

如果這些條件沒(méi)有一個(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è)問(wèn)題不能簡(jiǎn)單地用條件句式來(lái)解決，如果你也曾經(jīng)被迫這樣寫(xiě)過(guò)，那么你很可能從此以后都不會(huì)再寫(xiě)出這種語(yǔ)句了。

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

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

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

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

類似的用于動(dòng)態(tài)更新語(yǔ)句的解決方案叫做 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ì)刪掉無(wú)關(guān)的逗號(hào)，因?yàn)橛昧藯l件語(yǔ)句之后很可能就會(huì)在生成的 SQL 語(yǔ)句的后面留下這些逗號(hào)。（譯者注：因?yàn)橛玫氖恰癷f”元素，若最后一個(gè)“if”沒(méi)有匹配上而前面的匹配上，SQL 語(yǔ)句的最后就會(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 條件語(yǔ)句的時(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）變量。它也允許你指定開(kāi)頭與結(jié)尾的字符串以及在迭代結(jié)果之間放置分隔符。這個(gè)元素是很智能的，因此它不會(huì)偶然地附加多余的分隔符。

-釋義：

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

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

　-index ：表示在迭代過(guò)程中每次迭代到的位置（下標(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 是值。

感謝各位的閱讀，以上就是“mybatis 動(dòng)態(tài)SQL查詢方法總結(jié)”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)mybatis 動(dòng)態(tài)SQL查詢方法總結(jié)這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！