MyBatis動態SQL（認真看看，以後寫SQL就爽多了）-知識星球

作者：阿進的寫字臺

連結：http://www.cnblogs.com/homejim/p/9909657.html

0、一起來學習 mybatis

MyBatis 令人喜歡的一大特性就是動態 SQL。在使用 JDBC 的過程中，根據條件進行 SQL 的拼接是很麻煩且很容易出錯的。 MyBatis 動態 SQL 的出現，解決了這個麻煩。

MyBatis透過 OGNL 來進行動態 SQL 的使用的。

目前，動態 SQL 支援以下幾種標簽

元素	作用	備註
if	判斷陳述句	單條件分支
choose(when、otherwise)	相當於 Java 中的 if else	多條件分支
trim(where、set)	輔助元素	用於處理 SQL 拼接問題
foreach	迴圈陳述句	批次插入，更新，查詢時經常用到
bind	建立一個變數，並系結到背景關係中	用於相容不同的資料庫，防止 SQL 註入等

1、資料準備

為了後面的演示，建立了一個 Maven 專案 mybatis-dynamic, 建立了對應的資料庫和表

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '編號',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '電話',
  `email` varchar(50) DEFAULT NULL COMMENT '郵箱',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性別',
  `locked` tinyint(4) DEFAULT NULL COMMENT '狀態(0:正常,1:鎖定)',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '存入資料庫的時間',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改的時間',
  `delete` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='學生表';

對應的專案結構

2、if 標簽

if 標簽是我們最常使用的。在查詢、刪除、更新的時候很可能會使用到。必須結合 test 屬性聯合使用。

2.1 在 WHERE 條件中使用 if 標簽

這是常見的一種現象，我們在進行按條件查詢的時候，可能會有多種情況。

2.1.1 查詢條件

根據輸入的學生資訊進行條件檢索

1、當只輸入使用者名稱時，使用使用者名稱進行模糊檢索；

2、當只輸入性別時，使用性別進行完全匹配

3、當使用者名稱和性別都存在時，用這兩個條件進行查詢匹配查詢

2.1.2 動態 SQL

介面函式

/**
     * 根據輸入的學生資訊進行條件檢索
     * 1. 當只輸入使用者名稱時， 使用使用者名稱進行模糊檢索；
     * 2. 當只輸入郵箱時， 使用性別進行完全匹配
     * 3. 當使用者名稱和性別都存在時， 用這兩個條件進行查詢匹配的用
     * @param student
     * @return
     */
        List selectByStudentSelective(Student student);

對應的動態 SQL

 <select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    "Base_Column_List" />
    from student
    where 1=1
    <if test="name != null and name !=''">
      and name like concat('%', #{name}, '%')
    if

>
    <if test=“sex != null”>
      and sex=#{sex}
    if>
  select>

在此 SQL 陳述句中， where 1=1 是多條件拼接時的小技巧，後面的條件查詢就可以都用 and 了。

同時，我們添加了 if 標簽來處理動態 SQL

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

>
  <if test=“sex != null”>
    and sex=#{sex}
  if>

此 if 標簽的 test 屬性值是一個符合 OGNL 的運算式，運算式可以是 true 或 false。如果運算式傳回的是數值，則0為 false, 非 0 為 true;

2.1.3 測試

@Test
    public void selectByStudent() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student search = new Student();
        search.setName("明");

        System.out.println("只有名字時的查詢");
        List studentsByName = studentMapper.selectByStudentSelective(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        search.setName(null);
        search.setSex((byte) 1);
        System.out.println("只有性別時的查詢");
        List studentsBySex = studentMapper.selectByStudentSelective(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        System.out.println("姓名和性別同時存在的查詢");
        search.setName("明");
        List studentsByNameAndSex = studentMapper.selectByStudentSelective(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        sqlSession.commit();
        sqlSession.close();
    }

只有名字時的查詢，傳送的陳述句和結果

查詢的條件只發送了

where 1=1 and name like concat('%', ?, '%')

只有性別時的查詢，傳送的陳述句和結果

查詢的條件只發送了

 where 1=1 and sex=?

姓名和性別同時存在的查詢，傳送的陳述句和結果

查詢條件

where 1=1 and name like concat('%', ?, '%') and sex=?

2.2 在 UPDATE 更新列中使用 if 標簽

有時候我們不希望更新所有的欄位，只更新有變化的欄位。

2.2.1 更新條件

只更新有變化的欄位，空值不更新。

2.2.2動態 SQL

介面方法

/**
     * 更新非空屬性
     */
    int updateByPrimaryKeySelective(Student record);

對應的 SQL

"updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student">
    update student
    <set>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      if

>
      <if test=“phone != null”>
        phone = #{phone,jdbcType=VARCHAR},
      if>
      <if test=“email != null”>
        email = #{email,jdbcType=VARCHAR},
      if>
      <if test=“sex != null”>
        sex = #{sex,jdbcType=TINYINT},
      if>
      <if test=“locked != null”>
        locked = #{locked,jdbcType=TINYINT},
      if>
      <if test=“gmtCreated != null”>
        gmt_created = #{gmtCreated,jdbcType=TIMESTAMP},
      if>
      <if test=“gmtModified != null”>
        gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
      if>
    set>
    where student_id = #{studentId,jdbcType=INTEGER}

2.2.3 測試

@Test
    public void updateByStudentSelective() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student student = new Student();
        student.setStudentId(1);
        student.setName("明明");
        student.setPhone("13838438888");
        System.out.println(studentMapper.updateByPrimaryKeySelective(student));

        sqlSession.commit();
        sqlSession.close();
    }

結果如下

2.3 在 INSERT 動態插入中使用 if 標簽

我們插入資料庫中的一條記錄，不是每一個欄位都有值的，而是動態變化的。在這時候使用 if 標簽，可幫我們解決這個問題。

2.3.1 插入條件

只有非空屬性才插入。

2.3.2 動態SQL

介面方法

/**
     * 非空欄位才進行插入
     */
    int insertSelective(Student record);

對應的SQL

"insertSelective" parameterType="com.homejim.mybatis.entity.Student">
    insert into student
    "(" suffix=")" suffixOverrides=",">
      <if test="studentId != null">
        student_id,
      if

>
      <if test=“name != null”>
        `name`,
      if>
      <if test=“phone != null”>
        phone,
      if>
      <if test=“email != null”>
        email,
      if>
      <if test=“sex != null”>
        sex,
      if>
      <if test=“locked != null”>
        locked,
      if>
      <if test=“gmtCreated != null”>
        gmt_created,
      if>
      <if test=“gmtModified != null”>
        gmt_modified,
      if>

    “values (” suffix=“)” suffixOverrides=“,”>
      <if test=“studentId != null”>
        #{studentId,jdbcType=INTEGER},
      if>
      <if test=“name != null”>
        #{name,jdbcType=VARCHAR},
      if>
      <if test=“phone != null”>
        #{phone,jdbcType=VARCHAR},
      if>
      <if test=“email != null”>
        #{email,jdbcType=VARCHAR},
      if>
      <if test=“sex != null”>
        #{sex,jdbcType=TINYINT},
      if>
      <if test=“locked != null”>
        #{locked,jdbcType=TINYINT},
      if>
      <if test=“gmtCreated != null”>
        #{gmtCreated,jdbcType=TIMESTAMP},
      if>
      <if test=“gmtModified != null”>
        #{gmtModified,jdbcType=TIMESTAMP},
      if>

這個 SQL 大家應該很熟悉，畢竟是自動生成的。

2.3.3 測試

@Test
    public void insertByStudentSelective() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student student = new Student();
        student.setName("小飛機");
        student.setPhone("13838438899");
        student.setEmail("xiaofeiji@qq.com");
        student.setLocked((byte) 0);

        System.out.println(studentMapper.insertSelective(student));

        sqlSession.commit();
        sqlSession.close();
    }

對應的結果

SQL 中，只有非空的欄位才進行了插入。

3、choose 標簽

choose when otherwise 標簽可以幫我們實現 if else 的邏輯。

一個 choose 標簽至少有一個 when, 最多一個otherwise

下麵是一個查詢的例子。

3.1 查詢條件

假設 name 具有唯一性，查詢一個學生

當 studen_id 有值時，使用 studen_id 進行查詢；
當 studen_id 沒有值時，使用 name 進行查詢；
否則傳回空

3.2 動態SQL

介面方法

/**
     * - 當 studen_id 有值時， 使用 studen_id 進行查詢；
     * - 當 studen_id 沒有值時， 使用 name 進行查詢；
     * - 否則傳回空
     */
    Student selectByIdOrName(Student record);

對應的SQL

<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    "Base_Column_List" />
    from student
    where 1=1
    
      <when test="studentId != null">
        and student_id=#{studentId}
      when

>
      <when test=“name != null and name != ””>
        and name=#{name}
      when>

        and 1=2


  select>

3.3 測試

@Test
    public void selectByIdOrName() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student student = new Student();
        student.setName("小飛機");
        student.setStudentId(1);

        Student studentById = studentMapper.selectByIdOrName(student);
        System.out.println("有 ID 則根據 ID 獲取");
        System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE));

        student.setStudentId(null);
        Student studentByName = studentMapper.selectByIdOrName(student);
        System.out.println("沒有 ID 則根據 name 獲取");
        System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE));

        student.setName(null);
        Student studentNull = studentMapper.selectByIdOrName(student);
        System.out.println("沒有 ID 和 name, 傳回 null");
        Assert.assertNull(studentNull);

        sqlSession.commit();
        sqlSession.close();
    }

有 ID 則根據 ID 獲取，結果

沒有 ID 則根據 name 獲取

沒有 ID 和 name, 傳回 null

4、trim(set、where)

這三個其實解決的是類似的問題。如我們在寫前面的[在 WHERE 條件中使用 if 標簽] SQL 的時候， where 1=1 這個條件我們是不希望存在的。

4.1 where

4.1.1 查詢條件

根據輸入的學生資訊進行條件檢索。

1、當只輸入使用者名稱時，使用使用者名稱進行模糊檢索；

2、當只輸入性別時，使用性別進行完全匹配

3、當使用者名稱和性別都存在時，用這兩個條件進行查詢匹配查詢

不使用 where 1=1。

4.1.2 動態 SQL

很顯然，我們要解決這幾個問題

當條件都不滿足時：此時 SQL 中應該要不能有 where ，否則導致出錯
當 if 有條件滿足時： SQL 中需要有 where，且第一個成立的 if 標簽下的 and | or 等要去掉

這時候，我們可以使用 where 標簽。

介面方法

/**
     * 根據輸入的學生資訊進行條件檢索
     * 1. 當只輸入使用者名稱時， 使用使用者名稱進行模糊檢索；
     * 2. 當只輸入郵箱時， 使用性別進行完全匹配
     * 3. 當使用者名稱和性別都存在時， 用這兩個條件進行查詢匹配的用
     */
    List selectByStudentSelectiveWhereTag(Student student);

對應的 SQL

<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    "Base_Column_List" />
    from student
   <where>
    <if test="name != null and name !=''">
      and name like concat('%', #{name}, '%')
    if

>
    <if test=“sex != null”>
      and sex=#{sex}
    if>
   where>
  select>

4.1.3 測試

@Test
    public void selectByStudentWhereTag() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student search = new Student();
        search.setName("明");

        System.out.println("只有名字時的查詢");
        List studentsByName = studentMapper.selectByStudentSelectiveWhereTag(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        search.setSex((byte) 1);
        System.out.println("姓名和性別同時存在的查詢");
        List studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        System.out.println("姓名和性別都不存在時查詢");
        search.setName(null);
        search.setSex(null);
        List studentsByNameAndSex = studentMapper.selectByStudentSelectiveWhereTag(search);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        sqlSession.commit();
        sqlSession.close();
    }

只有名字時的查詢, 有 where

姓名和性別同時存在的查詢，有 where

姓名和性別都不存在時查詢, 此時， where 不會再出現了。

4.2 set

set 標簽也類似，在 [2.2 在 UPDATE 更新列中使用 if 標簽] 中，如果我們的方法 updateByPrimaryKeySelective 沒有使用

4.3 trim

set 和 where 其實都是 trim 標簽的一種型別，該兩種功能都可以使用 trim 標簽進行實現。

4.3.1 trim 來表示 where

如以上的 where 標簽，我們也可以寫成

<trim prefix="where" prefixOverrides="AND |OR">
trim>

表示當 trim 中含有內容時，新增 where，且第一個為 and 或 or 時，會將其去掉。而如果沒有內容，則不新增 where。

4.3.2 trim 來表示 set

相應的， set 標簽可以如下表示

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

表示當 trim 中含有內容時，新增 set，且最後的內容為 , 時，會將其去掉。而沒有內容，不新增 set

4.3.3 trim 的幾個屬性

prefix: 當 trim 元素包含有內容時，增加 prefix 所指定的字首
prefixOverrides: 當 trim 元素包含有內容時，去除 prefixOverrides 指定的字首
suffix: 當 trim 元素包含有內容時，增加 suffix 所指定的字尾
suffixOverrides：當 trim 元素包含有內容時，去除 suffixOverrides 指定的字尾

5、foreach 標簽

foreach 標簽可以對陣列， Map 或實現 Iterable 介面。

foreach 中有以下幾個屬性

collection: 必填，集合/陣列/Map的名稱.
item: 變數名。即從迭代的物件中取出的每一個值
index: 索引的屬性名。當迭代的物件為 Map 時，該值為 Map 中的 Key.
open: 迴圈開頭的字串
close: 迴圈結束的字串
separator: 每次迴圈的分隔符

其他的比較好理解， collection 中的值應該怎麼設定呢？

跟介面方法中的引數相關。

1、只有一個陣列引數或集合引數

預設情況：集合collection=list，陣列是collection=array

推薦：使用 @Param 來指定引數的名稱，如我們在引數前@Param(“ids”)，則就填寫 collection=ids

2、多引數

多引數請使用 @Param 來指定，否則SQL中會很不方便

3、引數是Map

指定為 Map 中的對應的 Key 即可。其實上面的 @Param 最後也是轉化為 Map 的。

4、引數是物件

使用屬性.屬性即可。

5.1 在 where 中使用 foreach

在 where條件中使用，如按id集合查詢，按id集合刪除等。

5.1.1 查詢條件

我們希望查詢使用者 id 集合中的所有使用者資訊。

5.1.2 動態 SQL

函式介面

/**
     * 獲取 id 集合中的使用者資訊
     * @param ids
     * @return
     */
    List selectByStudentIdList(List ids);

對應 SQL

<select id="selectByStudentIdList" resultMap="BaseResultMap">
    select
    "Base_Column_List" />
    from student
    where student_id in
    <foreach collection="list" item="id" open="(" close=")" separator="," index="i">
      #{id}
    foreach

>
select>

5.1.3 測試

@Test
    public void selectByStudentIdList() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        List ids = new LinkedList<>();
        ids.add(1);
        ids.add(3);

        List students = studentMapper.selectByStudentIdList(ids);
        for (int i = 0; i             System.out.println(ToStringBuilder.reflectionToString(students.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        sqlSession.commit();
        sqlSession.close();
    }

結果

5.2 foreach 實現批次插入

可以透過foreach來實現批次插入。

5.2.1 動態SQL

介面方法

/**
     * 批次插入學生
     */
    int insertList(List students);

對應的SQL

"insertList">
    insert into student(name, phone, email, sex, locked)
    values
    <foreach collection="list" item="student" separator=",">
      (
      #{student.name}, #{student.phone},#{student.email},
      #{student.sex},#{student.locked}
      )
    foreach

5.2.2 測試

@Test
    public void insertList() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        List students = new LinkedList<>();
        Student stu1 = new Student();
        stu1.setName("批次01");
        stu1.setPhone("13888888881");
        stu1.setLocked((byte) 0);
        stu1.setEmail("13888888881@138.com");
        stu1.setSex((byte) 1);
        students.add(stu1);

        Student stu2 = new Student();
        stu2.setName("批次02");
        stu2.setPhone("13888888882");
        stu2.setLocked((byte) 0);
        stu2.setEmail("13888888882@138.com");
        stu2.setSex((byte) 0);
        students.add(stu2);

        System.out.println(studentMapper.insertList(students));
        sqlSession.commit();
        sqlSession.close();
    }

結果

6、bind 標簽

bind 標簽是透過 OGNL 運算式去定義一個背景關係的變數，這樣方便我們使用。

如在 selectByStudentSelective 方法中，有如下

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

在 MySQL 中，該函式支援多引數，但在 Oracle 中只支援兩個引數。那麼我們可以使用 bind 來讓該 SQL 達到支援兩個資料庫的作用

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

更改後的查詢結果如下

7、程式碼

使用示例：https://github.com/homejim/mybatis-examples

●編號520，輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

Web開發

更多推薦《25個技術類公眾微信》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

閱讀原文

已推薦到看一看

你的朋友可以在“發現”-“看一看”看到你認為好看的文章。

取消
 推薦

我知道了

已取消，“好看”想法已同步刪除

知道了

0、一起來學習 mybatis

1、資料準備

2、if 標簽

2.1 在 WHERE 條件中使用 if 標簽

2.1.1 查詢條件

2.1.2 動態 SQL

2.1.3 測試

2.2 在 UPDATE 更新列中使用 if 標簽

2.2.1 更新條件

2.2.2動態 SQL

2.2.3 測試

2.3 在 INSERT 動態插入中使用 if 標簽

2.3.1 插入條件

2.3.2 動態SQL

2.3.3 測試

對應的結果

3、choose 標簽

3.1 查詢條件

3.2 動態SQL

3.3 測試

4、trim(set、where)

4.1 where

4.1.1 查詢條件

4.1.2 動態 SQL

4.1.3 測試

4.2 set

4.3 trim

4.3.1 trim 來表示 where

4.3.2 trim 來表示 set

4.3.3 trim 的幾個屬性

5、foreach 標簽

5.1 在 where 中使用 foreach

5.1.1 查詢條件

5.1.2 動態 SQL

5.1.3 測試

5.2 foreach 實現批次插入

5.2.1 動態SQL

5.2.2 測試

6、bind 標簽

7、程式碼

朋友將在看一看看到

分享想法到看一看