MyBatis 動態SQL_ZenDei技術網路在線

動態SQl是MyBatis的強大特性之一，可以完成對SQL語句的動態組裝。比如說傳入一個User對象，要根據這個User中的數據查詢用戶的完整信息：如果User對象中只有name屬性有值，sql語句是：select * from user_tb where name=#{name} 如果User ...

動態SQl是MyBatis的強大特性之一，可以完成對SQL語句的動態組裝。

比如說傳入一個User對象，要根據這個User中的數據查詢用戶的完整信息：

如果User對象中只有name屬性有值，sql語句是：select * from user_tb where name=#{name}
如果User對象中只有tel屬性有值，sql語句是：select * from user_tb where tel=#{tel}
如果User對象的name、tel都有值，sql語句是：select * from user_tb where name=#{name} and tel=#{tel}

有時候sql語句不是一成不變的，要根據傳入的數據動態生成要執行的sql語句，動態sql就適合這種情況。

最好將日誌的控制台輸出級別設置為DEBUG，這樣在控台能看到SQL語句。

MyBatis中的動態sql元素

<if> 相當於java中的if，用於單分支的條件判斷
<choose>、<when>、<otherwise> 相當於java中的switch...case...default，用於多分支的條件判斷，從多個選項中選擇一個
<foreach> 迴圈，常和sql的in語句搭配使用
<where>、<trim>、<set> 輔助元素，用於一些處理sql拼裝、特殊字元的問題
<bind> 從OGNL表達式中創建一個變數，並將其綁定到上下文，常用於模糊查詢

<where>、<if>的使用

<select id="queryUser" parameterType="user" resultType="user">
         SELECT * FROM user_tb
         <where>
            <if test="name!=null and name!=''">
                name=#{name}
            </if>
            <if test="tel!=null and tel!=''">
                AND tel=#{tel}
            </if>
        </where>
</select>

<where>相當於sql語句中的關鍵字WHERE。

<if>中的test表示條件，條件成立就把元素體中的字元串拼接到sql語句中，否則不拼接。

tel不進行數學運算，使用字元串類型。

<where>中有<if>成立時，<where>才會在sql語句中添加WHERE關鍵字，否則不添加。

<where>會自動剔除元素體中多餘的AND、OR。

比如傳入的User對象只設置了tel，拼接的sql語句是：SELECT * FROM user_tb WHERE AND tel=#{tel}，<where>會自動剔除多餘的AND。

不使用<where>的寫法：

<select id="queryUser" parameterType="user" resultType="user">
         SELECT * FROM user_tb  WHERE 1=1
            <if test="name!=null and name!=''">
                AND name=#{name}
            </if>
            <if test="tel!=null and tel!=''">
                AND tel=#{tel}
            </if>
</select>

<where>會自動去掉多餘的AND，但不會自動加上缺少的AND，我們通常在每個<if>中都加上AND，防止把AND寫掉了。

<trim>的使用

<trim>可以定製需要的功能，比如使用<trim>達到<where>的效果：

<select id="queryUser" parameterType="user" resultType="user">
         SELECT * FROM user_tb
         <trim prefix="WHERE" prefixOverrides="AND">
            <if test="name!=null and name!=''">
                name=#{name}
            </if>
            <if test="tel!=null and tel!=''">
                AND tel=#{tel}
            </if>
         </trim>
</select>

prefix會在這段字元串之前加上指定的首碼，如果裡面的<if>都不滿足條件，則不加首碼。

prefixOverrides指定要去除的多餘的字元串。

<choose>、<when>、<otherwise>的使用

有時候我們只需要從多個選項中選擇一個，比如登錄教務系統，讓你選擇角色：資料庫管理員、教職工、學生：

<select id="queryPwd" parameterType="user" resultType="string">
         SELECT password FROM user_tb WHERE name=#{name}
         <choose>
             <when test="role=='admin'">
                 AND role='admin'
             </when>
             <when test="role=='teacher'">
                 AND role='teacher'
             </when>
             <when test="role=='student'">
                 AND role='student'
             </when>
         </choose>
</select>

傳入的值是admin、teacher、student，資料庫中的role欄位也是這幾個值，這種傳入的值和資料庫中存儲的值一致的可以直接這樣寫：

<select id="queryPwd" parameterType="user" resultType="string">
         SELECT password FROM user_tb WHERE name=#{name} AND role=#{role}
</select>

如果不一致，比如性別，傳入的是male、female，資料庫中存儲的是0、1，就需要使用<choose>轉換一下。

　　　　<choose>
             <when test="">
                 
             </when>
             <when test="">
                 
             </when>
             <otherwise>
                 
             </otherwise>
　　　　</choose>

<choose>相當於switch，<when>相當於case，<otherwise>相當於default。

<set>的使用

更新操作傳入一個pojo類的對象，但我們並不知道哪些屬性是有值的（需要更新的），不能更新全部欄位，因為有的欄位沒有手動賦值，不能用JVM賦的null、0去覆蓋數據表中原來的值。

<set>可解決此問題：

<select id="updateUser" parameterType="user">
         UPDATE user_tb
         <set>
             <if test="name!=null and name!=''">
                 name=#{name},
             </if>
             <if test="tel!=null and tel!=''">
                 tel=#{tel},
             </if>
             <if test="address!=null and address!=''">
                 name=#{name},
             </if>
         </set>
         where id=#{id}
 </select>

<set>用於傳入pojo類型，更新數據表的多個欄位。先判斷欄位是否有值，有值才更新該欄位。

<set>用於更新操作，會自動在這段字元串前面加sql關鍵字“SET”（裡面有<if>為真），並自動去除多餘的逗號（一般自己寫第一個<if>，然後copy下來改，最後面往往會多一個逗號）。

如果裡面的<if>都為假，即沒有要更新的欄位，不會自動在前面加”SET”，此時這個update語句有語法錯誤，會報錯，所以要保證至少有一個欄位需要更新。

test屬性的註意點

相等判斷：==，!=
邏輯與：只能用and，不能用&&
邏輯或：or、||均可

<foreach>的使用

<foreach>用於迭代集合、數組，常和in搭配使用。

比如按照手機號隨機抽取3位幸運觀眾（根據手機號碼查詢用戶信息）：

<select id="queryUser" parameterType="list" resultType="user">
         SELECT * FROM user_tb WHERE tel IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

        ArrayList<String> telList = new ArrayList<>();
        telList.add("110");
        telList.add("119");
        telList.add("120");
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.queryUser(telList);
        System.out.println(userList);

collection 要迭代的數據類型，數組=>array，List=>list，Map=>Map的key的數據類型。使用數組容易出錯，儘量用List代替數組。
index 本次迭代的下標，指定一個臨時變數表示下標
item 本次迭代的元素，指定一個臨時變數表示本次迭代的元素
open 拼接這段字元串時以什麼開頭
close 拼接這段字元串以什麼結尾
separator 迭代的元素之間用什麼連接（分隔）

collection是必需的，其餘均可選。

更嚴謹的寫法：

<select id="queryUser" parameterType="list" resultType="user">
         SELECT * FROM user_tb WHERE tel IN
         <if test="list!=null and list.size>0">
             <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                 #{item}
             </foreach>
         </if>
</select>

如果傳入的是List類型，要使用List對象本身時，約定使用list表示List對象本身。

<bind>的使用

模糊查詢時可以使用${}連接字元串，但不能防止sql註入。

mysql可以使用concat()連接字元串，oracle可以使用||連接字元串，但只能針對特定的資料庫使用，不利於項目移植。

mybatis的<bind>元素可以解決以上問題。

<select id="queryUser" parameterType="string" resultType="user">
        <bind name="pattern_name" value="'%'+name+'%'"/>
        SELECT * FROM user_tb WHERE name LIKE #{pattern_name}
</select>

<bind>定義一個變數，拼接好字元串。

在sql語句中引用該變數的值。