前言

在我們編寫的sql語句的內(nèi)容并不是固定的，會通過一些條件判斷拼接成最終符合要求的sql語句。

本篇所講的動態(tài)SQL，是mybatis通過標(biāo)簽元素的形式， 如if, choose, when, otherwise, trim, where, set, foreach等標(biāo)簽完成對sql的拼接功能，使用起來也非常靈活方便，大大提高了開發(fā)人員的工作效率！

一、MyBatis動態(tài)SQL

1.動態(tài)SQL是什么

動態(tài)SQL是一種根據(jù)不同條件動態(tài)生成SQL語句的技術(shù)。在MyBatis中，我們可以使用動態(tài)SQL來根據(jù)不同的查詢條件生成不同的SQL語句，從而實(shí)現(xiàn)更靈活的查詢。

2.動態(tài)SQL的作用

1. 條件查詢：通過動態(tài)SQL，我們可以根據(jù)用戶輸入的條件動態(tài)生成查詢語句，從而實(shí)現(xiàn)靈活的條件查詢。例如，根據(jù)用戶選擇的不同篩選條件，我們可以動態(tài)地拼接SQL語句，實(shí)現(xiàn)根據(jù)不同條件查詢不同的結(jié)果。

2. 動態(tài)排序：有時(shí)候我們需要根據(jù)用戶的選擇對查詢結(jié)果進(jìn)行排序。通過動態(tài)SQL，我們可以根據(jù)用戶選擇的排序字段和排序方式動態(tài)生成排序語句，從而實(shí)現(xiàn)按照不同的規(guī)則對查詢結(jié)果進(jìn)行排序。

3. 動態(tài)更新：有時(shí)候我們需要根據(jù)不同的條件更新數(shù)據(jù)庫中的數(shù)據(jù)。通過動態(tài)SQL，我們可以根據(jù)不同的條件動態(tài)生成更新語句，從而實(shí)現(xiàn)根據(jù)不同的條件更新不同的數(shù)據(jù)。

4. 動態(tài)插入：有時(shí)候我們需要根據(jù)不同的條件插入數(shù)據(jù)到數(shù)據(jù)庫中。通過動態(tài)SQL，我們可以根據(jù)不同的條件動態(tài)生成插入語句，從而實(shí)現(xiàn)根據(jù)不同的條件插入不同的數(shù)據(jù)。

3.常用動態(tài)SQL元素

下面我們將以用戶表為例舉例說明：

1. where + if 元素

先來個(gè)簡單的需求，根據(jù) username 和 sex 來查詢user用戶，首先看下普通的sql：

<select id="selectUserByNameAndSex" resultType="com.ctb.model.User" 
                        parameterType="com.kzy.entity.User">
    select * from user where username=#{username} and sex=#{sex}
</select>

這種方式如果其中一個(gè)參數(shù)為空，可能就會導(dǎo)致最終查詢不到數(shù)據(jù)。

我們現(xiàn)在想實(shí)現(xiàn)如果username為空，將只根據(jù)sex來查詢；如果sex為空，我們將只根據(jù)username來查詢。那么可以使用 where + if 標(biāo)簽進(jìn)行判斷，sql如下：

<select id="selectUserByNameOrSex" resultType="com.ctb.model.User" 
                        parameterType="com.ctb.model.User">
    select * from user 
    <where>
        <if test="username != null">
           username=#{username}
        </if>
        <if test="sex != null">
           and sex=#{sex}
        </if>
    </where>
</select>

 if 元素：即根據(jù)條件判斷是否顯示其里面的內(nèi)容。                                                                  where 元素：自行判斷若下面的子元素有內(nèi)容，則此處會添加一個(gè)'where'，如果下面的子元素?zé)o內(nèi)容，即條件判斷都為空，則此處不添加'where'。此外，如果where標(biāo)簽內(nèi)容以'and' 或 'or'開頭的話，會將and/or自動剔除(否則 'where and/or' 連在一起會報(bào)語法錯(cuò)誤)。

2. set + if 元素

不僅查詢操作可能會用到動態(tài)sql，有時(shí)一些更新操作也會需要根據(jù)前端傳來的參數(shù)進(jìn)行判斷，拼接符合條件的sql語句，如下：

<update id="updateUserById" parameterType="com.ctb.model.User">
    update user u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
     where id=#{id}
</update>

若第一個(gè)條件 username 為空，那么 sql 語句為：update user u set u.sex=? where id=?

若第一個(gè)條件不為空，那么 sql 語句為：update user u set u.username = ? ,u.sex = ? where id=?

多個(gè)條件以此類推，最后一個(gè)if標(biāo)簽里的內(nèi)容結(jié)尾不用加逗號，是為了防止出現(xiàn) '... , where ...' 語法錯(cuò)誤。

3. choose + when + otherwise 元素

有時(shí)候，我們不想使用所有的條件，而只是想從多個(gè)條件中選擇一個(gè)使用。針對這種情況，MyBatis 提供了 choose 元素，它有點(diǎn)像 Java 中的 switch 語句。

<select id="selectUserByChoose" resultType="com.ctb.model.User" parameterType="com.ctb.model.User">
    select * from user
    <where>
        <choose>
            <when "id != null and test=id !='' ">
                id=#{id}
            </when>
            <when test="username != null and username !='' ">
                and username=#{username}
            </when>
            <otherwise>
                and sex=#{sex}
            </otherwise>
        </choose>
    </where>
</select>

業(yè)務(wù)還是查詢user，三個(gè)條件分別為 id,username,sex，但是策略變?yōu)橹贿x擇一個(gè)作為查詢條件：傳入 “id” ，對應(yīng)的sql語句就是 select * from user where  id=?； 

傳入 “username” ，對應(yīng)的sql語句就是 select * from user where  username=?；

如果前兩者都沒有傳入，那么默認(rèn)選擇<otherwise>標(biāo)簽里的內(nèi)容，對應(yīng)的查詢語句為 select * from user where sex=?；

4. 自定義 trim 元素

如果 where 或者 set 元素與你期望的不太一樣，你也可以通過自定義 trim 元素來定制 where 或 set 的功能，非常實(shí)用！

 <1>. 自定義 trim 元素改寫上面的 where + if 語句

<select id="selectUserByNameOrSex" resultType="com.ctb.model.User" parameterType="com.ctb.model.User">
    select * from user
    <!-- <where>
            <if test="username != null">
               username=#{username}
            </if>
            <if test="sex != null">
               and sex=#{sex}
            </if>
        </where>  -->
    <!-- 改寫后的效果 -->
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username != null">
            username=#{username}
        </if>
        <if test="sex != null">
            and sex=#{sex}
        </if>
    </trim>
</select>

prefix：插入 prefix 屬性中指定的內(nèi)容，即前綴。

prefixoverride：前綴覆蓋，去掉第一個(gè)and 或者or，即 prefixoverride 屬性中的內(nèi)容，此處為了防止拼接sql時(shí)出現(xiàn)" where and "這種情況。

<2>. 自定義 trim 元素改寫上面的 set + if 語句

<update id="updateUserById" parameterType="com.ctb.model.User">
    update user u
        <!-- <set>
                <if test="username != null and username != ''">
                u.username = #{username},
                </if>
                <if test="sex != null and sex != ''">
                u.sex = #{sex}
                </if>
            </set> -->
    <!-- 修改后的內(nèi)容 -->
    <trim prefix="set" suffixOverrides=",">
        <if test="username != null and username != ''">
            u.username = #{username},
        </if>
        <if test="sex != null and sex != ''">
            u.sex = #{sex},
        </if>
    </trim>
    where id=#{id}
</update>

suffixoverride：后綴覆蓋，去掉最后一個(gè)逗號，即 suffixoverride 屬性中的內(nèi)容，此處為了防止拼接sql時(shí)出現(xiàn)尾部多個(gè)逗號這種情況。

5. foreach 元素

foreach 元素的非常強(qiáng)大，它允許你指定一個(gè)集合，聲明可以在元素體內(nèi)使用的集合項(xiàng)（item）和索引（index）變量，它也允許你指定開頭與結(jié)尾的字符串以及集合項(xiàng)迭代之間的分隔符，不會有不必要的語法錯(cuò)誤。

你可以將任何可迭代對象（如 List、Set 等）、Map 對象或者數(shù)組對象作為集合參數(shù)傳遞給 foreach 。當(dāng)使用可迭代對象或者數(shù)組時(shí)，index 是當(dāng)前迭代的序號，item 的值是本次迭代獲取到的元素。當(dāng)使用 Map 對象（或者 Map.Entry 對象的集合）時(shí)，index 是鍵，item 是值。

例如：現(xiàn)在我們有個(gè)簡單的sql語句：select * from user where id in (1,2,3)；

現(xiàn)在我們來對它進(jìn)行改寫 ：

<select id="selectUserByListId" parameterType="java.util.List" resultType="com.ctb.model.User">
    select * from user
    where id in
        <!--
            collection:指定輸入對象中的集合屬性,可以是array數(shù)組,也可是list集合
            item:每次遍歷生成的對象
            open:開始遍歷時(shí)的拼接字符串
            close:結(jié)束時(shí)拼接的字符串
            separator:遍歷對象之間需要拼接的字符串
            select * from user where id in (1,2,3)
          -->
        <foreach collection="list" item="id" open="(" close=") " separator=",">
            #{id}
        </foreach>
</select>

foreach 元素內(nèi)各個(gè)屬性對應(yīng)的含義：                                                                                 

collection:指定輸入對象中的集合屬性,可以是array數(shù)組,也可是list集合
item:每次遍歷生成的對象
open:開始遍歷時(shí)的拼接字符串
close:結(jié)束時(shí)拼接的字符串
separator:遍歷對象之間需要拼接的字符串

6.SQL片段重用

該作用于當(dāng)我們經(jīng)常需要使用某表的某些字段時(shí)，我們可以它分裝起來，便于直接引用。

<sql id="userColumns">
  id, name, sex,birthday
</sql>
<select id="selectUsers" resultMap="BaseResultMap">
  SELECT <include refid="userColumns" />
  FROM t_user
</select>
<select id="selectUsersWithPrice" resultMap="BaseResultMap">
  SELECT <include refid="userColumns" />, info
  FROM t_user
</select>

        在這個(gè)示例中，通過<sql>標(biāo)簽定義了一個(gè)名為bookColumns的SQL片段，包含了書籍表的列名。然后，在不同的查詢語句中通過<include>標(biāo)簽引用了該SQL片段，實(shí)現(xiàn)了列名的重用。

二、模糊查詢的三種SQL方式、#和$的區(qū)別 三種模糊查詢的方法是：

1.使用#{字段名}

<select id="like1" resultType="com.ctb.model.Book" parameterType="java.lang.String" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like #{bname}
  </select>

測試結(jié)果

2.使用${字段名}

<select id="like2" resultType="com.ctb.model.Book" parameterType="java.lang.String" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like ${bname}
  </select>

測試結(jié)果：  

3.使用concat{'%',#{字段名},'%'}

<select id="like3" resultType="com.ctb.model.Book" parameterType="java.lang.String" >
  select
  <include refid="Base_Column_List" />
  from t_mvc_book
  where bname like concat('%',#{bname},'%')
</select>

測試結(jié)果： 

#與$的區(qū)別 

1.$ 符號（sql拼接符號) 

• $符號占位符是簡單的字符串替換，不進(jìn)行預(yù)編譯和參數(shù)類型處理，也不會進(jìn)行轉(zhuǎn)義。
• $符號占位符直接將參數(shù)的值替換到SQL語句中，可以用于動態(tài)拼接SQL語句的部分內(nèi)容。
• $符號占位符存在SQL注入的風(fēng)險(xiǎn)，因?yàn)閰?shù)值直接替換到SQL語句中，可能導(dǎo)致惡意注入攻擊。
• 沒有 '引號'

2. # 符號（占位符) 

• #符號占位符是預(yù)編譯的占位符，會對參數(shù)進(jìn)行類型處理和安全處理。
• #符號占位符將參數(shù)值作為預(yù)編譯參數(shù)傳遞給數(shù)據(jù)庫，可以防止SQL注入攻擊。
• #符號占位符可以用于動態(tài)生成SQL語句的條件部分，例如WHERE子句、ORDER BY子句等。
• 有引號

注：

1) mybatis中使用OGNL表達(dá)式傳遞參數(shù)

2) 優(yōu)先使用#{...}

3) ${...}方式存在SQL注入風(fēng)險(xiǎn) 

#{...} 更加安全可靠，適用于大多數(shù)情況下。而 ${...} 則更加靈活，但需要注意潛在的安全風(fēng)險(xiǎn)。在使用 ${...} 時(shí)，要確保參數(shù)值的來源可信，避免直接將用戶輸入的數(shù)據(jù)作為參數(shù)值傳遞進(jìn)去。

四、MyBatis結(jié)果映射

Mybatis中結(jié)果集的處理分為兩種：

resultMap：適合使用返回值是自定義實(shí)體類的情況

resultType：適合使用返回值的數(shù)據(jù)類型是非自定義的，即jdk的提供的類型

映射場景

①返回單表的對應(yīng)的實(shí)體類，僅有一個(gè)查詢結(jié)果，可以用resultType/resultMap。

②返回單表的對應(yīng)的實(shí)體類，有多個(gè)查詢結(jié)果，可以用resultType/resultMap。

③返回多表對應(yīng)結(jié)果，僅有一個(gè)查詢結(jié)果，通常用resultType也可以用resultMap。

④返回多表對應(yīng)結(jié)果，有多個(gè)查詢結(jié)果，通常用resultType也可以用resultMap。

⑤返回單個(gè)列段，僅有一個(gè)查詢結(jié)果，就用resultType。

⑥返回單個(gè)列段，有多個(gè)查詢結(jié)果，就用resultType。 

resultType進(jìn)行結(jié)果映射

首先，我們需要定義一個(gè)User類，用于存儲查詢結(jié)果：

public class User {
    private int id;
    private String name;
    private int age;
    private String gender;
    private String email;
    // getter和setter方法省略
}

然后，在MyBatis的mapper文件中，我們可以使用resultType來映射查詢結(jié)果到User對象上。具體配置如下：

<select id="getUserById" resultType="com.ctb.model.User">
  SELECT id, name, age, gender, email
  FROM user
  WHERE id = #{id}
</select>

在上面的配置中，我們使用了resultType屬性來指定查詢結(jié)果映射到的Java對象的全限定名。這樣，當(dāng)執(zhí)行查詢語句時(shí)，MyBatis會自動將查詢結(jié)果映射到指定的Java對象上。

resultMap進(jìn)行結(jié)果映射

首先，我們需要定義一個(gè)Order類，用于存儲查詢結(jié)果：

public class Order {
    private int id;
    private int userId;
    private int productId;
    private double price;
    private int quantity;
    // getter和setter方法省略
}

然后，在MyBatis的mapper文件中，我們可以使用resultMap來映射查詢結(jié)果到Order對象上。具體配置如下：

<resultMap id="OrderResultMap" type="com.example.Order">
  <id property="id" column="id" />
  <result property="userId" column="user_id" />
  <result property="productId" column="product_id" />
  <result property="price" column="price" />
  <result property="quantity" column="quantity" />
</resultMap>
<select id="getOrderById" resultMap="OrderResultMap">
  SELECT id, user_id, product_id, price, quantity
  FROM order
  WHERE id = #{id}
</select>

在上面的配置中，我們定義了一個(gè)名為"OrderResultMap"的resultMap，它的type屬性指定了要映射的Java對象的全限定名。接下來，我們?yōu)槊總€(gè)字段指定了對應(yīng)的屬性名和數(shù)據(jù)庫表中的列名。最后，在查詢語句中引用這個(gè)resultMap即可將查詢結(jié)果映射到Order對象上。

需要注意的是，如果查詢結(jié)果中的某個(gè)字段在Java對象中沒有對應(yīng)的屬性，那么該字段將被映射為null。此外，如果查詢結(jié)果中的某個(gè)字段在Java對象中有多個(gè)對應(yīng)的屬性，那么該字段的值將被映射為一個(gè)列表或數(shù)組。

總結(jié)

resultType 用于指定查詢結(jié)果的類型。它可以是Java的基本數(shù)據(jù)類型（如int、String等），也可以是自定義的Java對象。當(dāng)查詢結(jié)果只有一列時(shí)，可以使用 resultType 來指定該列的類型。例如，如果查詢結(jié)果只有一個(gè)整數(shù)列，可以將 resultType 設(shè)置為 int 。

resultMap 用于定義復(fù)雜的查詢結(jié)果映射關(guān)系。當(dāng)查詢結(jié)果包含多個(gè)列，并且需要將這些列映射到一個(gè)Java對象中時(shí)，可以使用 resultMap 來定義映射規(guī)則。 resultMap 可以指定每個(gè)列與Java對象的屬性之間的映射關(guān)系，以及一些其他的映射規(guī)則，如類型轉(zhuǎn)換、關(guān)聯(lián)關(guān)系等。

resultType 適用于簡單的查詢結(jié)果映射，而 resultMap 適用于復(fù)雜的查詢結(jié)果映射。

MyBatis動態(tài)SQL、模糊查詢與結(jié)果映射到這就結(jié)束了。

到此這篇關(guān)于MyBatis動態(tài)SQL、模糊查詢與結(jié)果映射的文章就介紹到這了,更多相關(guān)MyBatis動態(tài)SQL結(jié)果映射內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論