mybatis sql标簽_MyBatis手把手跟我做系列(三) --- 動态SQL标簽

上一章已經實作了最簡單的CRUD的操作,不過還是有一些細節問題需要進一步的學習

一.

#{}

與

${}

在前面的章節中,大家已經看過很多次#()這個符号了,雖然沒有專門的去講解,但是也應該能猜出

#{}

應該就是一個參數的占位符,是的,大家猜測的沒錯,不過這個占位符除了

#{}

之外,還有一個

${}

,兩者都是起到占位的作用,但又是完全不同的.

簡單來說,

#{}

其實就相當于我們之前在JDBC代碼中使用了PreparedStatement,并在SQL語句中使用了

?

替代符

而

${}

的占位,就相當于SQL語句直接使用了字元串拼接,而且還需要自己加上``哦.

上面的意思我們用僞代碼形容一下

select * from t_user where username=#{username}
就相當于
select * from t_user where username=?
而且會在随後的代碼執行中自動用你傳入的 `username` 參數将`?`替代掉,比如`username`的值是`張三`
select * from t_user where username='張三'

select * from t_user where username=${username}
比如 參數 username 的值是張三,那就相當于
select * from t_user where username=張三
注意這裡是錯誤的哦,因為張三是個字元串,你需要自己加上單引号
select * from t_user where username='${username}'才是正确的寫法
           

是以,總結來說:

#{}:

解析為一個JDBC預編譯語句(PreparedStatement)的參數标記,一個

#()

會被解析為一個參數占位符

?

,在資料庫中發生參數的替換

${}:

僅僅為一個純粹的String字元換的替換,而且在動态SQL的解析階段就講變量進行替換,而且如果參數就是一個簡單類型的話(意思是不是一個自定義對象,就是一個int,或者String),那麼參數的占位符隻能使用value,也就是上面的 username=”張三”,如果就隻有這麼一個值,使用

${}

替代符就隻能使用value占位,也就是要寫為

select * from t_user where username=${value}

是以一般情況下,我們當然優先使用

#()

,還能一定程度上防止SQL注入,但是有些時候使用

${}

卻很友善,比如在模糊查詢的時候

...
<!-- 根據名稱模糊查詢使用者資訊 -->
<select id="getUserByName" parameterType="string" resultMap="userMap">
    select * from t_user where username like '%${value}%'</select>
...
           

下面的截圖,展示了上面文字描述的一些問題

二.動态SQL

上面的例子隻是根據使用者的名字進行了模糊查詢,這個時候問題就來了,如果要使用多條件查詢呢?那就需要用到MyBatis提供的動态SQL功能了,通過

if, choose, when, otherwise, trim, where, set, foreach

标簽，可組合成非常靈活的SQL語句

1.

where

與

if

還是直接通過應用場景來解釋,相信大家之前都寫過多條件查詢的SQL語句了,基本的思考點就是到底有哪些條件?哪些條件不為空的時候才進行SQL拼接?多條件後面的and或者or該怎麼搞定?這個問題,在大家的學習過程中,最早期的代碼應該是這麼寫的

......
String sql = "select * from t_user where 1=1 "
if(username != null && !"".equals(username)){
    sql += " and username like '%" + username + "%'";
}if(userTel != null && !"".equals(userTel)){
    sql += " and userTel ='" + userTel + "'";
}
...
           

在早期的JDBC代碼中,為了避免SQL語句後面拼接

and

的問題,一般都會人為的在前面加上一個條件

1=1

,使用MyBatis其實也跳不過這個問題,不過我們使用MyBatis自帶的标簽來解決這個問題

<!--多條件查詢動态SQL-->
<select id="getUserBySelective" parameterType="user" resultMap="userMap">
    select * from t_user    
    <where>
        <if test="id >= 1">
            id=#{id}        
        </if>
        <if test="userTel != null">
            and user_tel like '%${userTel}%'        
        </if>
        <if test="username != null">
            and username like '%${username}%'        
        </if>
        <if test="registrationTime != null">
            and Date_Format(registration_time,'%Y-%m-%d')=#{registrationTime}        
        </if>
    </where>
</select>
           

上面的語句就用到了MyBatis的where和if标簽,具體标簽的含義其實不用多做解釋大家也能看懂,而且最好的地方是完美的解決了之前and拼接的問題,下面的截圖給大家展示了這些效果

2.

trim

與

if

where标簽也完全可以用trim标簽替代,一般情況下的寫法是這個樣子的

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

其實關鍵就是

prefix

和

prefixOverrides

這兩個屬性

prefix

: 字首,這裡的意思就是SQL語句加上where

prefixOverrides

: 去掉第一個and或者是or,就是後面的拼接語句中出現了and或者or,如果出現在第一個條件中就去掉

上面的代碼,我們通過

trim

标簽替換

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="id >= 1">
        id=#{id}    
    </if>
    <if test="userTel != null">
        and user_tel like '%${userTel}%'    
    </if>
    <if test="username != null">
        and username like '%${username}%'    
    </if>
    <if test="registrationTime != null">
        and Date_Format(registration_time,'%Y-%m-%d')=#{registrationTime}    
    </if>
</trim>
           

3.

set

與

if

既然查詢是多條件的,我們回過頭看看之前寫過的新增

insert

和修改

update

方法,按照之前的寫法,如果有條件不傳,會出現什麼問題,我們看一下之前的修改方法

這樣肯定和實際情況不符,我們新加一個修改方法,根據傳入的值進行判斷修改,這樣改的關鍵點其實還是判斷要修改的值是否為空,不為空再進行修改,問題的關鍵點還是多個條件判斷的時候,最後一個逗号(,)截取的問題,在MyBatis中,使用

set

标簽

同樣,

<trim>

标簽也可以完全替換

<set>

<update id="updateUserByIdSelective" parameterType="user">
  update t_user  
    <trim prefix="set" suffixOverrides=",">
        <if test="userTel != null">
          user_tel=#{userTel},      
        </if>
        <if test="username != null">
          username=#{username},      
        </if>
        <if test="password != null">
          password=md5(#{password}),      
        </if>
        <if test="registrationTime != null">
          registration_time=#{registrationTime}      
        </if>
  </trim>
  where id=#{id}
</update>
           

suffixOverrides

: 去掉最後一個逗号(,)字尾

在新增語句中,也是一樣,如果隻是選擇性的要插入一些字段(當然前提是資料庫中該字段可以為null),看一下之前新增資料的效果

如果要選擇性的拼接SQL,新增的SQL語句拼接會涉及到表字段,字段對應的值以及左括号,逗号,右括号,這樣拼接判斷的時候複雜度稍微高一些,是以,這裡使用

<trim>

标簽是最好的選擇

<!--根據傳入的參數新增使用者資訊-->
<insert id="insertUserSelective" parameterType="user" useGeneratedKeys="true" keyProperty="id">
    insert into t_user    
    <trim prefix="(" suffix=")" suffixOverrides="," >
        <if test="id != null" >
            id,        
        </if>
        <if test="userTel != null">
            user_tel,        
        </if>
        <if test="username != null" >
            username,        
        </if>
        <if test="password != null" >
            password,        
        </if>
        <if test="registrationTime != null" >
            registration_time,        
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
        <if test="id != null" >
            #{id,jdbcType=INTEGER},        
        </if>
        <if test="userTel != null" >
            #{userTel,jdbcType=VARCHAR},        
        </if>
        <if test="username != null" >
            #{username,jdbcType=VARCHAR},        
        </if>
        <if test="password != null" >
            md5(#{password,jdbcType=VARCHAR}),        
        </if>
        <if test="registrationTime != null" >
            #{registrationTime,jdbcType=VARCHAR},        
        </if>
    </trim>
</insert>
           

4.

SQL

和

include

有時候可能某個 sql 語句我們用的特别多，為了增加代碼的重用性，簡化代碼，我們需要将這些代碼抽取出來，然後使用時直接調用。

比如上面新增語句的字段條件判斷,插入語句實在寫的太多,可以将這部分的代碼提出來,直接寫成代碼片段,到時候再用

<include>

标簽引用就可以了

這種用法特别是在寫查詢語句的時候,字段名較多的情況下,把經常要現實出來的字段用SQL标簽引入,在寫SQL語句的時候,再直接通過include引入字段就可以了

5.

choose(when,otherwise)

上面的

where

和

set

标簽都是在和

if

标簽配合,判斷标簽除了

if

之外,還有

choose(when,otherwise)

标簽,

choose(when,otherwise)

其實就類似于java的

switch

語句,把之前的查詢語句,從

where...if...

的組合換成

where...choose(when,otherwise)

,但是要注意兩者之間的差別,

choose(when,otherwise)

同時隻能成立一個條件

<!--多條件查詢動态SQL,使用where...choose(when...otherwise)組合-->
<select id="getUserBySelective2" parameterType="user" resultMap="userMap">
    select * from t_user    
     <where>
        <choose>
            <when test="id >= 1">
              id=#{id}            
             </when>
            <when test="userTel != null">
              and user_tel like '%${userTel}%'            
             </when>
            <when test="username != null">
              and username like '%${username}%'            
             </when>
            <otherwise>
              and Date_Format(registration_time,'%Y-%m-%d')=#{registrationTime}            
            </otherwise>
        </choose>
    </where>
</select>
           

6.

foreach

先來捋一捋代碼場景,比如同時要查詢多個值的場景,意思是SQL語句要寫成類似于下面的這個樣子:

select * from t_user where id=1 or id=4 or id=5 or id=8
           

或者

select * from t_user where id in(1, 4, 5, 8)
           

注意:

這個樣子的SQL語句,為了傳輸值友善,不破壞User類,我們再封裝一個Bean類,就叫做查詢Bean,這個Bean就是為了查詢而服務的,什麼意思呢?界面上可能會有各種條件的查詢,比如注冊的開始日期,結束日期,根據這種情況進行查詢,但是我們現在的User類其實隻是完全和資料庫對應的類,User類裡隻有一個注冊日期,沒有什麼注冊開始日期和結束日期的概念,但是在查詢的時候卻需要查詢這些東西,是以幹脆就專門建一個實體類,來處理封裝界面上要查詢的内容,這樣就可以友善的将界面上的資料傳輸給Dao層.這其實是分層架構中VO,PO,DO,DTO等等領域模型的概念,有興趣的可以自己查詢了解一下

無論如果,為了封裝友善,這裡新建立一個類

QueryBean.java:

//封裝要查詢的多個使用者的id
import java.util.List;public class QueryBean {    
    private List<Integer> ids;    
    public List<Integer> getIds() {        
        return ids;
    }    
    public void setIds(List<Integer> ids) {        
        this.ids = ids;
    }
}
           

在UserMapper.xml中添加根據多個id查詢的方法

<select id="getUserByQueryIds" parameterType="queryBean" resultMap="userMap">
    select * from t_user    
    <where>
        <!--
            collection:指定輸入對象中的集合屬性
            item:每次周遊生成的對象
            open:開始周遊時的拼接字元串
            close:結束時拼接的字元串
            separator:周遊對象之間需要拼接的字元串
            select * from t_user where (id=1 or id=4 or id=5 or id=8)
          -->
        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id=#{id}        
        </foreach>
    </where>
</select>
           

注意下圖中,foreach标簽中每個屬性的作用

也可以稍微換一種寫法

<select id="getUserByQueryIds" parameterType="queryBean" resultMap="userMap">
    select * from t_user    
    <where>
        <!--
            collection:指定輸入對象中的集合屬性
            item:每次周遊生成的對象
            open:開始周遊時的拼接字元串
            close:結束時拼接的字元串
            separator:周遊對象之間需要拼接的字元串
            select * from t_user where (id=1 or id=4 or id=5 or id=8)

        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id=#{id}
        </foreach>
        -->

        <!-- select * from t_user where id in (1, 4, 5, 8) -->
        <foreach collection="ids" item="id" open="id in (" close=")" separator=",">
            #{id}        
        </foreach>
    </where>
</select>