MyBatis 動態(tài)SQL

內(nèi)容

Mybatis動態(tài)SQL在XML中支持的幾種標簽：

• if
• chose
• trim、set山害、where
• foreach
• bind
• 多數(shù)據(jù)支持
• OGNL

使用動態(tài)SQL還可以實現(xiàn)多數(shù)據(jù)的支持纠俭。由于動態(tài)SQL中大量使用OGNL，所以最后介紹了OGNL的用法

if用法

if標簽用于where語句中浪慌，通過判斷參數(shù)值來決定是否使用某個查詢條件柑晒；

if標簽用于update語句中，通過判斷參數(shù)值來決定是否更新某個字段眷射；

if標簽用于insert語句中匙赞，判斷是否插入某個字段的值。

在where條件中使用if

select id, 
    user_name userName, 
    user_password userPassword,
    user_email userEmail,
    user_info userInfo,
    head_img headImg,
    create_time createTime
from sys_user
where 1 = 1
<if test="userName != null and userName != ''">
    and user_name like concat('%', #{userName}, '%')
</if>
<if test="userEmail != null and userEmail != ''">
and user_email = #{userEmail}
</if>

需要注意的內(nèi)容：

• test的屬性值是一個符合OGNL要求的判斷表達式妖碉，表達式的結(jié)果可以是true或false涌庭。除此之外，所有的非0值都為true欧宜，只有0為false坐榆。為了方便理解，在表達式中冗茸，建議只用true和false作為結(jié)果

• 判斷條件properties != null或properties == null 適用于任何類型的字段席镀，用于判斷屬性值是否為空

• 判斷條件properties != ''或properties == ''只適用與string類型的字段，用于判斷是否為空字符串

• 當有多個判斷時夏漱，使用and或or進行連接豪诲，嵌套的判斷可以使用小括號分組

• 在OGNL表達式中，判斷的順序不會影響判斷的結(jié)果挂绰，也不會有空指針異常屎篱。但建議判斷順序與Java一致

• 注意SQL中where關(guān)鍵字后面的查詢條件1 = 1
  由于查詢條件都是動態(tài)的，當兩個if判斷都不滿足時葵蒂，最后生成的SQL就會以where結(jié)束交播，因此會報錯。加上1 = 1這個條件就可以避免

• 注意條件中的and或or
  當if判斷通過時践付，這部分拼接到where 1 = 1之后時仍然是合法的SQL

在update更新列中使用if

需求：只更新有變化的字段秦士，更新時不能將原來有值但沒有變化的字段更新為空或null

update sys_user 
<set>
    <if test="userName != null and userName != ''">
    user_name = #{userName},
    </if>
    <if test="userPassword != null and userPassword != ''">
    user_password = #{userPassword},
    </if>
    <if test="userEmail != null and userEmail != ''">
    user_email = #{userEmail},
    </if>
    <if test="userInfo != null and userInfo != ''">
    user_info = #{userInfo},
    </if>
    <if test="headImg != null">
    head_img = #{headImg, jdbcType=BLOB},
    </if>
    <if test="createTime != null">
    create_time = #{createTime, jdbcType=TIMESTAMP},
    </if>
    id = #{id},
</set>
where id = #{id}

需要注意的內(nèi)容：

• 每個if元素里面SQL語句后面的逗號
• where關(guān)鍵字前面的id = #{id}

注意以上兩點確保生成的SQL的正確性

在insert動態(tài)插入列中使用if

需求：在數(shù)據(jù)庫表插入數(shù)據(jù)的時候，如果某一列的參數(shù)值不為空永高，就用傳入的值隧土；如果傳入的參數(shù)為空，就用數(shù)據(jù)庫中的默認值（或空值）乏梁。

<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
    insert into sys_user(
        user_name, user_password, 
        <if test="userEmail != null and userEmail != ''">
            user_email,
        </if>
        user_info, head_img, create_time)
    values(
        #{userName}, #{userPassword}, 
        <if test="userEmail != null and userEmail != ''">
            #{userEmail}, 
        </if>
        #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})
</insert>

需要注意的內(nèi)容：

• 若在列的部分增加if條件次洼，則values部分也要增加相同的if條件关贵，必須保證上下可以互相對應(yīng)

choose用法

choose標簽用于實現(xiàn)if...else if...else...的邏輯遇骑。choose標簽包含when和otherwise兩個標簽，至多有一個otherwise標簽

需求：當參數(shù)id有值時優(yōu)先使用id查詢揖曾，當id為沒有值時落萎，就去判斷用戶名是否有值亥啦，如果有值就用用戶名查詢，如果用戶名沒有值练链，就使SQL查詢無結(jié)果翔脱。

<select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser">
select id, 
    user_name userName, 
    user_password userPassword,
    user_email userEmail,
    user_info userInfo,
    head_img headImg,
    create_time createTime
from sys_user
where 1 = 1
<choose>
    <when test="id != null">
    and id = #{id}
    </when>
    <when test="userName != null and userName != ''">
    and user_name = #{userName}
    </when>
    <otherwise>
    and 1 = 2
    </otherwise>
</choose>
</select>

需要注意內(nèi)容：

• 使用choose標簽的時候邏輯要嚴密，避免某些值出現(xiàn)問題導(dǎo)致SQL出錯媒鼓。
  例如届吁，如果沒有otherwise這個限制條件，所有的用戶都會被查詢出來绿鸣，如果我們在接口方法中使用對象作為返回值疚沐，當查詢結(jié)果是多個的時候就會報錯。添加otherwise條件后潮模，由于where條件不滿足亮蛔，因此在這種情況下就查詢不到結(jié)果。

where擎厢、set究流、trim 用法

where用法

where標簽的作用：如果該標簽包含的元素中有返回值，就插入一個where动遭；如果where后面的字符串是以and或or開頭的芬探，就將它們剔除。

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="@tk.mybatis.util.StringUtil@isNotEmpty(userName)">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

當if條件都不滿足的時候厘惦，where元素中沒有內(nèi)容灯节，所以在SQL中不會出現(xiàn)where；
當if條件滿足绵估，where元素的內(nèi)容就是以and開頭的查詢條件炎疆，where會自動去掉開頭的and。

set用法

set標簽的作用：如果該標簽包含的元素有返回值国裳，就插入一個set形入；
如果set后面的字符串是以逗號結(jié)尾的，就想這個逗號剔除缝左。

<update id="updateByIdSelective">
    update sys_user 
    <set>
        <if test="userName != null and userName != ''">
        user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != ''">
        user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != ''">
        user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != ''">
        user_info = #{userInfo},
        </if>
        <if test="headImg != null">
        head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
        create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id},
    </set>
    where id = #{id}
</update>   

需要注意的內(nèi)容：

當set標簽中沒有返回值時亿遂，形成的SQL為：

update sys_user where id = #{id}

所以，仍需要添加id = #{id}渺杉，使最后的SQL滿足語法

trim用法

where和set標簽的功能都可以用trim標簽來實現(xiàn)

where標簽對應(yīng)的trim實現(xiàn)：

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

需要注意的內(nèi)容：

• AND 和 OR后面的空格不能省略蛇数，這是為了避免匹配到andes、orders等單詞
• prefixOverriders包含的值除了AND和OR之外是越，還有：AND\n,OR\n,AND\r,OR\r,AND\t,OR\t

set標簽對應(yīng)的trim實現(xiàn)：

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

</trim>

trim標簽有如下屬性：

• prefix：當trim元素包含內(nèi)容時耳舅，會給內(nèi)容增加prefix指定的前綴
• prefixOverrides：當trim元素包含內(nèi)容時，會把prefixOverrides中指定的前綴字符串去掉
• suffix：當trim元素包含內(nèi)容時倚评，會給內(nèi)容增加suffix指定的前綴
• suffixOverrides：當trim元素包含內(nèi)容時浦徊，會把suffixOverrides中指定的后綴字符串去掉

foreach用法

foreach標簽可以對數(shù)組馏予、Map、實現(xiàn)Iterable接口的對象進行遍歷盔性。由于數(shù)組在處理時會轉(zhuǎn)化為List對象霞丧，因此foreach遍歷的對象可以分為兩大類：Iterable類型和Map類型

foreach標簽包含如下屬性：

• collection：必填，值為要迭代循環(huán)的屬性名
• item：變量名冕香，值為從迭代對象中取出的每一個值
• index：索引名蛹尝，在Iterable類型下為當前索引值，當Map類型下為Map的Key
• open：整個循環(huán)內(nèi)容開頭的字符串
• close：整個循環(huán)內(nèi)容結(jié)尾的字符串
• separator：每次循環(huán)的分隔符

foreach標簽

由于foreach遍歷的對象分為：Iterable類型和Map類型悉尾，所以collection的屬性值也分為不同情況：

1. 參數(shù)是一個Iterable
2. 參數(shù)是一個Map類型
3. 參數(shù)是一個對象
4. 有多個參數(shù)

實例

1. 參數(shù)是一個Iterable

前提：沒有使用@Param注解箩言，使用默認的名稱。如果使用了@Param注解焕襟，collection屬性的值為@Param注解中指定的內(nèi)容

• 當遍歷的對象是一個數(shù)組時陨收，collection屬性的默認值為array
• 當遍歷的對象是一個List時，collection屬性的默認值為list

2. 參數(shù)是一個Map類型

前提：沒有使用@Param注解鸵赖，使用默認的名稱务漩。如果使用了@Param注解，collection屬性的值為@Param注解中指定的內(nèi)容

• 當遍歷的對象是一個Map時它褪，collection屬性的默認值為_parameter

3. 參數(shù)是一個對象

指定為對象的屬性名即可饵骨。當使用對象內(nèi)多層嵌套的對象時，使用屬性.屬性的方式可以指定深層的屬性值

4. 有多個參數(shù)

當有多個參數(shù)的時候茫打，要使用@Param注解給每個參數(shù)指定一個名字居触，否則在SQL中使用參數(shù)時就會不方便。因此將collection屬性值設(shè)置為@Param直接指定的名字即可

foreach實現(xiàn)in集合

需求：根據(jù)傳入的用戶id集合查詢出所有符合條件的用戶

<select id="selectByIdList" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    where id in
    <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
        #{id}
    </foreach>
</select>

foreach實現(xiàn)批量插入

當參數(shù)類型是List時老赤，實現(xiàn)批量插入

<insert id="insertList">
    insert into sys_user(
        user_name, user_password,user_email,
        user_info, head_img, create_time)
    values
    <foreach collection="list" item="user" separator=",">
        (
        #{user.userName}, #{user.userPassword},#{user.userEmail},
        #{user.userInfo}, #{user.headImg, jdbcType=BLOB}, 
        #{user.createTime, jdbcType=TIMESTAMP})
    </foreach>
</insert>

從MyBatis 3.3.1 版本開始轮洋，MyBatis開始支持批量新增回寫主鍵值的功能。這個功能要求數(shù)據(jù)庫主鍵值為自增類型抬旺，透視還要求該數(shù)據(jù)庫提供的JDBC可以支持返回批量插入的主鍵值弊予。目前只有Mysql數(shù)據(jù)庫支持。

如果要在Mysql中實現(xiàn)批量插入返回自增主鍵值开财，只需要在原來代碼的基礎(chǔ)上進行如下修改即可：

<insert id="insertList" useGeneratedKeys="true" keyProperty="DTO對象的主鍵字段">

foreach實現(xiàn)動態(tài)update

當參數(shù)類型是Map時汉柒，實現(xiàn)批量更新

<update id="updateByMap">
    update sys_user 
    set 
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>

對應(yīng)的Mapper接口方法為：

void updateByMap(Map map);

當參數(shù)類型是Map時，foreach標簽的index屬性為Map的key责鳍，item屬性為Map的value

bind用法

bind標簽的作用：

• bind標簽使用OGNL表達式創(chuàng)建一個變量并將其綁定當上下文中
• bind標簽用于打印傳入的參數(shù)

bind標簽有兩個屬性碾褂，并且都為必選項：name、value

• name為綁定到上下文的變量名
• value為OGNL表達式

作用一：

在Mysql中历葛，concat函數(shù)支持多個參數(shù)正塌，但在Oracle中只支持兩個參數(shù)。所以下面的SQL在遷移數(shù)據(jù)庫時就會出現(xiàn)錯誤

<if test="userName != null and userName != ''">
    and user_name like concat('%', #{userName}, '%')
</if>

使用bind標簽改寫

<if test="userName != null and userName != ''">
    <bind name="userNameTemp" value="'%' + userName + '%'"/>
    and user_name like #{userNameTemp}
</if>

作用二：

打印SQL中傳入的參數(shù)

<update id="updateByIdSelective">
    <bind name="print" value="@tk.mybatis.util.StringUtil@print(_parameter,'updateByIdSelective方法')"/>
        update sys_user 
    <set>
        <if test="userName != null and userName != ''">
        user_name = #{userName},
        </if>
        <if test="userPassword != null and userPassword != ''">
        user_password = #{userPassword},
        </if>
        <if test="userEmail != null and userEmail != ''">
        user_email = #{userEmail},
        </if>
        <if test="userInfo != null and userInfo != ''">
        user_info = #{userInfo},
        </if>
        <if test="headImg != null">
        head_img = #{headImg, jdbcType=BLOB},
        </if>
        <if test="createTime != null">
        create_time = #{createTime, jdbcType=TIMESTAMP},
        </if>
        id = #{id},
    </set>
    where id = #{id}
</update>   

多數(shù)據(jù)支持

MyBatis多數(shù)據(jù)庫的支持除了可以使用bind標簽之外，還可以使用if標簽配合databaseIdProvider進行配置传货。

MyBatis可以根據(jù)不同的數(shù)據(jù)庫廠商執(zhí)行不同的語句屎鳍，這種多廠商的支持基于映射語句中的databaseId屬性

<select id="selectRolesByUserId" resultType="tk.mybatis.simple.model.SysRole" databaseId="mysql">
    select 
        r.id, 
        r.role_name roleName, 
        r.enabled,
        r.create_by createBy,
        r.create_time createTime,
        u.user_name as "user.userName",
        u.user_email as "user.userEmail"
    from sys_user u
    inner join sys_user_role ur on u.id = ur.user_id
    inner join sys_role r on ur.role_id = r.id
    where u.id = #{userId}
</select>

Mybatis會加載不帶databaseId屬性和帶有匹配當前數(shù)據(jù)庫databaseId屬性的所有語句宏娄。如果同時找到帶有databaseId和不帶databaseId的相同語句问裕，則后者會被拋棄。

為支持多廠商特性孵坚，只要像下面這樣在mybatis-config.xml文件中加入databaseIdProvider配置即可粮宛。

<databaseIdProvider type="DB_VENDER" />

這里的DB_VENDER值會通過DatabaseMetaData類的getDatabaseProductName()方法的返回值進行設(shè)置。由于該返回值非常長卖宠，并且包含相同產(chǎn)品的不同版本巍杈，所以通常設(shè)置屬性別名使其變短。

<databaseIdProvider type="DB_VENDER">
    <property name="SQL Server" value="sqlserver" />
    <property name="DB2" value="db2" />
    <property name="Oracle" value="oracle" />
    <property name="MySQL" value="mysql" />
    <property name="PostgreSQL" value="postgresql" />
    <property name="Derby" value="derby" />
    <property name="HSQL" value="hsqldb" />
    <property name="H2" value="h2" />
</databaseIdProvider>

除了增加上面的配置外扛伍，映射文件也需要進行修改筷畦，包含databaseId屬性的標簽有：

• insert
• delect
• update
• select
• selectKey
• sql

示例：在Mysql中contract函數(shù)可以接受多個參數(shù)，才Oracle中contract函數(shù)只接受兩個參數(shù)

mysql

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser" databaseId="mysql">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            and user_name like concat('%', #{userName}, '%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

oracle

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser" databaseId="oracle">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            and user_name like concat(concat('%', #{userName}),'%')
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

還有更簡單的寫法刺洒，只是因為數(shù)據(jù)庫的更換可能只會引起某個SQL語句的部分不同鳖宾，所以沒有必要使用上面的方式∧婧剑可以使用if標簽配合默認上下文中的_databaseId參數(shù)實現(xiàn)上面的功能鼎文，避免大量重復(fù)的SQL出現(xiàn)。當然也可以使用choose標簽

<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
    select id, 
        user_name userName, 
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
    from sys_user
    <where>
        <if test="userName != '' and userName != null">
            <if test="_databaseId == 'mysql'">
                and user_name like concat(concat('%', #{userName}),'%')
            </if>
            <if test="_databaseId == 'oracle'">
                and user_name like concat(concat('%', #{userName}),'%')
            </if>
        </if>
        <if test="userEmail != '' and userEmail != null">
        and user_email = #{userEmail}
        </if>
    </where>
</select>

OGNL用法

在Mybatis的動態(tài)SQL中大量使用了OGNL表達式因俐，Mybatis常用的OGNL表達式有：

1. e1 and e2 , e1 or e2 , !e1 (not e1)

2. e1 == e2 (e1 eq e2) , e1 != e2 (e1 neq e2) , e1 gt e2 , e1 gte e2 , e1 lt e2 , e1 lte e2

3. e1 + e2 , e1 - e2 , e1 * e2 , e1/e2 , e1%e2

4. e.method(args) ： 調(diào)用對象方法

5. e.property ：調(diào)用對象屬性

6. @class@methd(args)：調(diào)用類的靜態(tài)方法

7. @class@field：調(diào)用類的靜態(tài)字段

8. e[key]：按索引取值（數(shù)組拇惋、List、Map）

調(diào)用對象方法

<if test="list != null and list.size() > 0">

</if>

調(diào)用類型靜態(tài)方法

<if test="!@org.apache.commons.collections.CollectionUtils@isEmpty(list)">

</if>

需要注意的內(nèi)容：

• 對于5抹剩、8中的用法撑帖，可以多層嵌套使用；不管e是不是null澳眷，必須保證屬性或索引必須存在磷仰，否則會報錯
• 對于用法6，還可以實現(xiàn)一些特殊功能境蔼，例如：打印傳遞進SQL中的參數(shù)

<bind name="print" value="@tk.mybatis.util.StringUtil@print(_parameter)"/>

其中print方法的內(nèi)容大致為

public void print(Object param){
    System.out.println(param);
}

《MyBatis從入門到精通》（劉增輝）