MyBatis 動態 SQL

LZC發表於2020-02-22

動態 SQL

MyBatis 的強大特性之一便是它的動態 SQL。如果你有使用 JDBC 或其它類似框架的經驗,你就能體會到根據不同條件拼接 SQL 語句的痛苦。例如拼接時要確保不能忘記新增必要的空格,還要注意去掉列表最後一個列名的逗號。利用動態 SQL 這一特性可以徹底擺脫這種痛苦。

雖然在以前使用動態 SQL 並非一件易事,但正是 MyBatis 提供了可以被用在任意 SQL 對映語句中的強大的動態 SQL 語言得以改進這種情形。

動態 SQL 元素和 JSTL 或基於類似 XML 的文字處理器相似。在 MyBatis 之前的版本中,有很多元素需要花時間瞭解。MyBatis 3 大大精簡了元素種類,現在只需學習原來一半的元素便可。MyBatis 採用功能強大的基於 OGNL 的表示式來淘汰其它大部分元素。

準備

首先建立User實體類

public class User {
    private Integer id;
    private String username;
    private String userEmail;
    private String userCity;
    private Integer age;
}

建立user表

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
    user_email varchar(255) DEFAULT NULL,
    user_city varchar(255) DEFAULT NULL,
    age int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

if

定義介面方法

public List<User> findByUser(User user);

介面對應的 Mapper.xml 定義如下所示

<select id="findByUser" resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where
    <if test="username != null and username != ''">
        username = #{username}
    </if>
    <if test="userEmail != null and userEmail != ''">
        and user_email = #{userEmail}
    </if>
    <if test="userCity != null and userCity != ''">
        and user_city = #{userCity}
    </if>
</select>

如果if標籤上的test為true,那麼if標籤裡面的SQL語句將會被拼接。

如果username、userEmail、userCity都不為空,那麼SQL將會拼接成如下所示

select id, username, user_email userEmail, user_city userCity, age 
from user 
where username = ? and user_email = ? and user_city = ? 

如果只有username不為空,那麼SQL將會拼接成如下所示

select id, username, user_email userEmail, user_city userCity, age 
from user 
where username = ? 

但是這種方式存在一個缺點,假設此時username為空,userEmail、userCity都不為空。

我們來分析動態 SQL 程式碼,現在沒有給 username 賦值,即 username==null,所以 “username=#{username}” 這段程式碼不會新增到 SQL 語句中,那麼最終拼接好的動態 SQL 是這樣的:

select id, username, user_email userEmail, user_city userCity, age 
from user 
where and user_email = ? and user_city = ? 

where 後面直接跟 and,很明顯的語法錯誤,此時應該把緊跟在where後面的and刪掉。為了解決這個問題,可以使用where標籤。

where

將上面的SQL改成如下所示

    <select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <where>
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="userEmail != null and userEmail != ''">
                and user_email = #{userEmail}
            </if>
            <if test="userCity != null and userCity != ''">
                and user_city = #{userCity}
            </if>
        </where>
    </select>

如果where標籤裡面的if標籤有滿足條件的,那麼where標籤就會被拼接成where語句,若if標籤拼接的SQL最前面有and語句,那麼這個and將會被刪除。使用這種方法, 會自動刪除SQL中不需要的關鍵字,所以一般 if 標籤和 where 標籤會組合起來使用。

trim

trim標籤中的 prefixsuffix屬性會被用於生成實際的 SQL 語句,會和標籤內部的語句拼接。

如果語句的前面或後面遇到 prefixOverridessuffixOverrides屬性中指定的值,MyBatis 會自動將它們刪除。在指定多個值的時候,別忘了每個值後面都要有一個空格,保證不會和後面的 SQL 連線在一起。

prefix:給拼接的SQL語句加一個字首

suffix:給拼接的SQL語句加一個字尾

prefixOverrides:拼接的SQL語句前面遇到 prefixOverrides,MyBatis 會自動將它們刪除

suffixOverrides:拼接的SQL語句後面遇到 suffixOverrides,MyBatis 會自動將它們刪除

下面使用trim標籤來實現where標籤的功能

<select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <trim prefix="where" prefixOverrides="and">
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="userEmail != null and userEmail != ''">
                and user_email = #{userEmail}
            </if>
            <if test="userCity != null and userCity != ''">
                and user_city = #{userCity}
            </if>
        </trim>
    </select>

如果username為空,userEmail和userCity不為空,那麼if 標籤拼接的SQL語句如下所示

and user_email = #{userEmail} and user_city = #{userCity}

因為trim標籤設定了prefixOverrides=”and”,而上面的SQL前面有and語句,所以需要將上面的and語句刪掉,又因為trim標籤設定了prefix=”where”,所以需要在拼接的SQL語句前面加一個where語句

最後trim標籤的SQL語句被拼接成如下所示

where user_email = #{userEmail} and user_city = #{userCity}

choose

有時我們不想應用到所有的條件語句,而只想從中擇其一項。針對這種情況,MyBatis 提供了 choose 元素,它有點像 Java 中的 switch 語句。

<select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <where>
            <choose>
                <when test="username != null and username != ''">
                    username = #{username}
                </when>
                <when test="userEmail != null and userEmail != ''">
                    and user_email = #{userEmail}
                </when>
                <when test="userCity != null and userCity != ''">
                    and user_city = #{userCity}
                </when>
            </choose>
        </where>
    </select>

set

set 標籤用於 Update 操作,會自動根據引數選擇生成 SQL 語句。

介面定義如下

public int updateUser(User user);

介面對應的 Mapper.xml 定義如下所示

<update id="updateUser" parameterType="com.example.mybatis.entity.User">
       update user
       <set>
           <if test="username != null and username != ''">
               username=#{username},
           </if>
           <if test="userEmail != null and userEmail != ''">
               user_email=#{userEmail},
           </if>
           <if test="userCity != null and userCity != ''">
               user_city=#{userCity},
           </if>
           <if test="age != null">
              age=#{age}
           </if>
       </set>
       where id=#{id}
    </update>

foreach

foreach 標籤可以迭代生成一系列值

*用於 SQL 的 in 語句 *

介面定義如下所示

public List<User> getUsersByIds(List<Integer> ids);

介面對應的 Mapper.xml 定義如下所示

<!--
        collection: 指定要遍歷的集合
            預設情況下
                如果為Collection型別的,key為collection;
                如果為List型別的,key為list
                如果是陣列型別,key為array
            可以透過@Param("ids")來指定key
        item: 將當前遍歷的元素賦值給指定的變數
        open: 給遍歷的結果新增一個開始字元
        close: 給遍歷的結果新增一個結束字元
        separator: 每個元素之間的分隔符
    -->
<select id="getUsersByIds"
        resultType="com.example.mybatis.entity.User">
    select * from user
    where id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

用於批次插入

介面定義如下所示

public int addUserList(List<User> users);

介面對應的 Mapper.xml 定義如下所示

<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="user"  collection="list" separator=",">
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>

<!--返回自增主鍵-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="user"  collection="list" separator=",">
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>

<!--還可以這樣寫-->
<!--
    這種方式需要資料庫連線屬性設定allowMultiQueries=true
    這種分號分隔多個SQL還可以用於其他的批次操作,如修改、刪除
-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    <foreach item="user"  collection="list" separator=";">
        insert into user
        (username, user_email, user_city, age)
        values
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>


<!--如果是Oracle資料庫,則需要這樣寫-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    <foreach item="user" open="begin" close="end;"  collection="list">
        insert into user
        (username, user_email, user_city, age)
        values
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age});
    </foreach>
</insert>
本作品採用《CC 協議》,轉載必須註明作者和本文連結

相關文章