Mybatis系列第十講

案例sql腳本
if元素
choose/when/otherwise元素
where元素
set元素
trim元素
foreach元素
- 案例：in多值查詢
- 案例：批量插入
sql/include元素
bind元素
#和$

案例sql腳本

DROP DATABASE IF EXISTS `javacode2018`;
CREATE DATABASE `javacode2018`;

USE `javacode2018`;

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
  id int AUTO_INCREMENT PRIMARY KEY COMMENT '用戶id',
  name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
  age SMALLINT NOT NULL DEFAULT 1 COMMENT '年齡'
) COMMENT '用戶表';
INSERT INTO t_user VALUES (1,'路人甲Java',30),(2,'張學友',50),(3,'劉德華',50);

if元素

相當于java中的if判斷，語法：

<if test="判斷條件">
需要追加的sql
</if>

test的值為一個判斷運算式，寫法上采用OGNL運算式的方式，OGNL在struts2中用的比較多，本文暫時對ognl不做詳細介紹，有興趣的可以去查一下相關資料，

當test成立的時候，if體內部的sql會被拼接上，

如：

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    WHERE 1 = 1
    <if test="id!=null">
        AND id = #{id}
    </if>
    <if test="name!=null and name.toString()!=''">
        AND name = #{name}
    </if>
    <if test="age!=null">
        AND age = #{age}
    </if>
</select>

上面查詢用戶串列，引數為一個map，當map中id不為空的時候，將其作為條件查詢，如果name不為空，將name也作為條件，如果age不為空，將age也作為條件進行查詢

當只傳入id的時候，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

當3個引數都傳了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? AND name = ? AND age = ?

上面這種寫法相對于java代碼看起來是不是清爽了很多，也更方便維護，大家注意一下sql中有個WHERE 1=1，如果沒有這個，上面單通過if元素就不好實作了，mybatis也有解決方案，稍后會說明，

choose/when/otherwise元素

這個相當于java中的if…else if…else，語法：

<choose>
    <when test="條件1">
        滿足條件1追加的sql
    </when>
    <when test="條件2">
        滿足條件2追加的sql
    </when>
    <when test="條件n">
        滿足條件n追加的sql
    </when>
    <otherwise>
        都不滿足追加的sql
    </otherwise>
</choose>

choose內部的條件滿足一個，choose內部的sql拼接就會結束，

otherwise屬于可選的，當所有條件都不滿足的時候，otherwise將起效，

如：

傳入id、name、age作為條件，按順序進行判斷，如果id不為空，將id作為條件，忽略其他條件，如果id為空，會判斷name是否為空，name不為空將name作為條件，如果name為空，再看看age是否為空，如果age不為空，將age作為條件，

<select id="getList2" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    WHERE 1 = 1
    <choose>
        <when test="id!=null">
            AND id = #{id}
        </when>
        <when test="name!=null and name.toString()!=''">
            AND name = #{name}
        </when>
        <when test="age!=null">
            AND age = #{age}
        </when>
    </choose>
</select>

如果id、name、age都傳了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

如果值傳遞了name、age，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND name = ?

name判斷在age前面，所以name條件先匹配上了，

where元素

上面2個案例的sql中都有where 1=1這部分代碼，雖然可以解決問題，但是看起來不美觀，如果將where 1=1中1=1這部分干掉，上面的兩個案例都會出問題，where后面會多一個AND符號，mybatis中已經考慮到這種問題了，屬于通用性的問題，mybatis中通過where 元素來解決，當使用where元素的時候，mybatis會將where內部拼接的sql進行處理，會將這部分sql前面的AND 或者 OR給去掉，并在前面追加一個where，我們使用where元素來對上面的案例1進行改造，如下：

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="name!=null and name.toString()!=''">
            AND name = #{name}
        </if>
        <if test="age!=null">
            AND age = #{age}
        </if>
    </where>
</select>

where 1=1被替換成了where 元素，

當傳入id、name的時候，where內部的sql會變成這樣：

AND id = ? AND name = ?

mybatis會對上面這個sql進行處理，將前面的AND給去掉，并在前面追加一個where，變成了下面這樣

where id = ? AND name = ?

案例2也用where改造一下，變成了下面這樣：

<select id="getList2" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <where>
        <choose>
            <when test="id!=null">
                AND id = #{id}
            </when>
            <when test="name!=null and name.toString()!=''">
                AND name = #{name}
            </when>
            <when test="age!=null">
                AND age = #{age}
            </when>
        </choose>
    </where>
</select>

這下看起來是不是舒服很多了，

set元素

現在我們想通過用戶id更新用戶資訊，引數為UserModel物件，物件中的屬性如果不為空，就進行更新，我們可以這么寫：

<update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
    UPDATE t_user SET
    <if test="name!=null">
        name = #{name},
    </if>
    <if test="age!=null">
        age = #{age},
    </if>
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
    </where>
</update>

我們來看一下，當所有屬性都傳值了，sql變成了下面這樣：

UPDATE t_user SET name = ?, age = ?, where id = ?

上面這個sql是有問題的，where前面多了一個逗號，得想辦法將這個逗號去掉，這個逗號屬于最后一個需要更新的欄位后面的逗號，屬于多余的，mybatis中提供了set元素來解決這個問題，將上面的代碼改成下面這樣：

<update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
    UPDATE t_user
    <set>
        <if test="name!=null">
            name = #{name},
        </if>
        <if test="age!=null">
            age = #{age},
        </if>
    </set>
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
    </where>
</update>

我們將sql中的set去掉了，加了個set元素，set元素會對其內部拼接的sql進行處理，會將這部分sql前后的逗號給去掉并在前面加上set，

當傳入id和age的時候，生成的sql：

UPDATE t_user SET age = ? where id = ?

trim元素

這個元素的功能比較強大，先看一下他的語法：

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
</trim>

trim元素內部可以包含各種動態sql，如where、chose、sql等各種元素，使用trim包含的元素，mybatis處理程序：

先對trim內部的sql進行拼接，比如這部分sql叫做sql1
將sql1字串前面的部分中包含trim的prefixOverrides指定的部分給去掉，得到sql2
將sql2字串后面的部分中包含trim的suffixOverrides指定的部分給去掉，得到sql3
在sql3前面追加trim中prefix指定的值，得到sql4
在sql4后面追加trim中suffix指定的值，得到最終需要拼接的sql5

了解了這個程序之后，說明可以通過trim來代替where和set，我們使用trim來改造一下案例1，如下：

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <trim prefix="where" prefixOverrides="and|or">
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="name!=null and name.toString()!=''">
            AND name = #{name}
        </if>
        <if test="age!=null">
            AND age = #{age}
        </if>
    </trim>
</select>

注意上面的prefixOverrides的值的寫法，如果有多個需要覆寫的之間用|進行分割，suffixOverrides寫法和prefixOverrides的寫法類似，

我們在用trim來改造一下上面的update中的，如下：

<update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
    UPDATE t_user
    <trim prefix="SET" prefixOverrides="," suffixOverrides=",">
        <if test="name!=null">
            name = #{name},
        </if>
        <if test="age!=null">
            age = #{age},
        </if>
    </trim>
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
    </where>
</update>

上面的prefixOverrides和suffixOverrides都設定的是逗號，表示trim內部的sql前后的逗號會被去掉，最后會在前面拼接一個prefix指定的set，

大家有興趣的可以去看一下trim的java實作，代碼下面這個類中：

org.apache.ibatis.scripting.xmltags.TrimSqlNode

實際上where和set的實作是繼承了TrimSqlNode，where對應的java代碼：

public class WhereSqlNode extends TrimSqlNode {

  private static List<String> prefixList = Arrays.asList("AND ","OR ","AND\n", "OR\n", "AND\r", "OR\r", "AND\t", "OR\t");

  public WhereSqlNode(Configuration configuration, SqlNode contents) {
    super(configuration, contents, "WHERE", prefixList, null, null);
  }

}

set對應的 java代碼：

public class SetSqlNode extends TrimSqlNode {

  private static final List<String> COMMA = Collections.singletonList(",");

  public SetSqlNode(Configuration configuration,SqlNode contents) {
    super(configuration, contents, "SET", COMMA, null, COMMA);
  }

}

最后都是依靠TrimSqlNode來實作的，

foreach元素

相當于java中的回圈，可以用來遍歷陣列、集合、map等，

語法

<foreach collection="需要遍歷的集合" item="集合中當前元素" index="" open="" separator="每次遍歷的分隔符" close="">
動態sql部分
</foreach>

collection：可以是一個List、Set、Map或者陣列
item：集合中的當前元素的參考
index：用來訪問當前元素在集合中的位置
separator：各個元素之間的分隔符
open和close用來配置最后用什么前綴和后綴將foreach內部所有拼接的sql給包裝起來，

案例：in多值查詢

我們對案例1做個改造，map中支持放入用戶的id串列（ArrayList），對應的key為idList，然后支持多個用戶id查詢，此時我們需要用in來查詢，實作如下：

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="name!=null and name.toString()!=''">
            AND name = #{name}
        </if>
        <if test="age!=null">
            AND age = #{age}
        </if>
        <if test="idList!=null and idList.size()>=1">
            <foreach collection="idList" item="id" open="AND id in (" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

大家看一下上面idList那部分判斷，判斷這個引數不為空，并且size()大于1，表示這個集合不為空，然后會走if元素內部的foreach元素，

比如我們傳遞的idList對應的是[1,2]，最后產生的sql如下：

SELECT id,name,age FROM t_user WHERE id in ( ? , ? )

案例：批量插入

傳入UserModel List集合，使用foreach實作批量插入，如下：

<insert id="insertBatch" parameterType="list">
    INSERT INTO t_user (id,name,age) VALUES
    <foreach collection="collection" separator="," item="item">
        (#{item.id}, #{item.name}, #{item.age})
    </foreach>
</insert>

測驗用例

com.javacode2018.chat05.demo8.Demo8Test#insertBatch

@Test
public void insertBatch() throws IOException {
    try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<UserModel> userModelList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            userModelList.add(UserModel.builder().id(10 + i).name("mybatis-" + i).age(20 + i).build());
        }
        int count = mapper.insertBatch(userModelList);
        log.info("{}", count);
    }
}

傳入了3個用戶資訊，運行輸出

39:52.241 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==>  Preparing: INSERT INTO t_user (id,name,age) VALUES (?, ?, ?) , (?, ?, ?) , (?, ?, ?) 
39:52.317 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Parameters: 10(Integer), mybatis-0(String), 20(Integer), 11(Integer), mybatis-1(String), 21(Integer), 12(Integer), mybatis-2(String), 22(Integer)
39:52.327 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - <==    Updates: 3
39:52.327 [main] INFO  c.j.chat05.demo8.Demo8Test - 3

sql/include元素

這兩2個元素一般進行配合使用，可以實作代碼重用的效果，

sql元素可以用來定義一段動態sql，語法如下：

<sql id="sql片段id">
各種動態sql
</sql>

其他地方需要使用的時候需要通過include關鍵字進行引入：

<include refid="需要引入的sql片段的id"/>

注意：refid值的寫法，refid的值為mapper xml的namespace的值.sql的id，如果在同一個mapper中，namespace可以省略，直接寫對應的sql的id就可以了，如：

<include refid="findSql"/>
<include refid="com.javacode2018.chat05.demo8.mapper.UserMapper.findSql"/>

來個案例

下面定義2個查詢，他們的查詢條件一樣，最后將條件抽出來用sql元素定義了一個片段，然后進行共用，

<sql id="findSql">
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="name!=null and name.toString()!=''">
            AND name = #{name}
        </if>
        <if test="age!=null">
            AND age = #{age}
        </if>
        <if test="idList!=null and idList.size()>=1">
            <foreach collection="idList" item="id" open="AND id in (" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</sql>

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <include refid="com.javacode2018.chat05.demo8.mapper.UserMapper.findSql" />
</select>

<select id="getList1Count" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT count(*) FROM t_user
    <include refid="findSql" />
</select>

bind元素

bind元素允許我們通過ognl運算式在背景關系中自定義一個變數，最后在動態sql中可以使用這個變數，

語法

<bind name="變數名稱" value="ognl運算式">

案例

對sql、include中的案例進行擴展，添加一個按照用戶名模糊查詢，用戶名在map中對應的key為likeName，主要修改上面sql片段部分，在sql中加入下面部分：

<if test="likeName!=null and likeName.trim()!=''">
  <bind name="nameLike" value="'%'+likeName.trim()+'%'" />
  AND name like #{nameLike}
</if>

先判斷傳入的引數likeName是否不為空字串，然后使用bind元素創建了一個變數nameLike，值為’%’+likeName.trim()+’%’，

對應的測驗用例：

com.javacode2018.chat05.demo8.Demo8Test#getModelList
@Test
public void getModelList() throws IOException {
    try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("likeName","java");
        List<UserModel> userModelList = mapper.getList1(paramMap);
        log.info("{}", userModelList);
    }
}

運行輸出：

06:25.633 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==>  Preparing: SELECT id,name,age FROM t_user WHERE name like ? 
06:25.671 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: %java%(String)
06:25.690 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <==      Total: 1
06:25.691 [main] INFO  c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31)]

注意輸出中的第二部分，引數的值為%java%，

#和$

#和KaTeX parse error: Expected 'EOF', got '#' at position 15: 一般都是結合變數來使用，如：#?{}、{}這種來進行使用，

#{}:為引數占位符？，即sql預編譯，相當于使用jdbc中的PreparedStatement中的sql占位符，可以防止sql注入

${}:為字串替換，即字串拼接，不能訪問sql注入，

#{}的用法上面已經有很多案例了，此處我們來一個${}的案例，

下面通過orderSql變數傳入任意的排序sql，如下：

<select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
    SELECT id,name,age FROM t_user
    <if test="orderSql">
        ${orderSql}
    </if>
</select>

傳入值：

orderSql = "order by id asc,age desc"

最后運行產生的sql如下：

20:32.138 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==>  Preparing: SELECT id,name,age FROM t_user order by id asc,age desc 
20:32.173 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: 
20:32.196 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <==      Total: 6
20:32.197 [main] INFO  c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31), UserModel(id=2, name=張學友, age=50), UserModel(id=3, name=劉德華, age=50), UserModel(id=10, name=mybatis-0, age=20), UserModel(id=11, name=mybatis-1, age=21), UserModel(id=12, name=mybatis-2, age=22)]

mybatis會對$包含部分進行sql替換，

易兮科技

Java

18級大連海事大學在校生
網路工程 & 國際經濟與貿易雙學位
中山市易兮軟體開發有限公司創始人
易兮科技團隊負責人
Java語言愛好者

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/178931.html

標籤：其他

上一篇：快來學習 SQL ！！！

Mybatis系列第十講 動態SQL，這么多種你都會？