天天看點
傳回

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

@

目錄

  • 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.1 動态 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 代碼

你想不想來學習 mybatis? 學習其使用和源碼呢？那麼， 在部落格園關注我吧！！

我自己打算把這個源碼系列更新完畢， 同時會更新相應的注釋。快去 star 吧！！

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 注入等

為了後面的示範， 建立了一個 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='學生表';

對應的項目結構

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

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

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

  1. 當隻輸入使用者名時， 使用使用者名進行模糊檢索；
  2. 當隻輸入性别時， 使用性别進行完全比對
  3. 當使用者名和性别都存在時， 用這兩個條件進行查詢比對查詢

接口函數

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

對應的動态 SQL

<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    <include refid="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;

@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<Student> studentsByName = studentMapper.selectByStudentSelective(search);
        for (int i = 0; i < studentsByName.size(); i++) {
            System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

        search.setName(null);
        search.setSex((byte) 1);
        System.out.println("隻有性别時的查詢");
        List<Student> studentsBySex = studentMapper.selectByStudentSelective(search);
        for (int i = 0; i < studentsBySex.size(); i++) {
            System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

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

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

隻有名字時的查詢， 發送的語句和結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

查詢的條件隻發送了

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

隻有性别時的查詢， 發送的語句和結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）
where 1=1 and sex=?

姓名和性别同時存在的查詢， 發送的語句和結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

查詢條件

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

有時候我們不希望更新所有的字段， 隻更新有變化的字段。

隻更新有變化的字段， 空值不更新。

接口方法

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

對應的 SQL

<update id="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}
           

@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();
    }

結果如下

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

隻有非空屬性才插入。

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

對應的SQL

<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student">
    insert into student
    <trim prefix="(" 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>
    </trim>
    <trim prefix="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>
    </trim>
  </insert>

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

@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("[email protected]");
        student.setLocked((byte) 0);

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

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

對應的結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

SQL 中， 隻有非空的字段才進行了插入。

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

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

下面是一個查詢的例子。

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

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

/**
     * - 當 studen_id 有值時， 使用 studen_id 進行查詢；
     * - 當 studen_id 沒有值時， 使用 name 進行查詢；
     * - 否則傳回空
     */
    Student selectByIdOrName(Student record);
           
<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    <include refid="Base_Column_List" />
    from student
    where 1=1
    <choose>
      <when test="studentId != null">
        and student_id=#{studentId}
      </when>
      <when test="name != null and name != ''">
        and name=#{name}
      </when>
      <otherwise>
        and 1=2
      </otherwise>
    </choose>
  </select>
           

@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 擷取， 結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

沒有 ID 則根據 name 擷取

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

沒有 ID 和 name, 傳回 null

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

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

不使用 where 1=1。

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

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

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

/**
     * 根據輸入的學生資訊進行條件檢索
     * 1. 當隻輸入使用者名時， 使用使用者名進行模糊檢索；
     * 2. 當隻輸入郵箱時， 使用性别進行完全比對
     * 3. 當使用者名和性别都存在時， 用這兩個條件進行查詢比對的用
     */
    List<Student> selectByStudentSelectiveWhereTag(Student student);
           
<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
    select
    <include refid="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>
           

@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<Student> studentsByName = studentMapper.selectByStudentSelectiveWhereTag(search);
        for (int i = 0; i < studentsByName.size(); i++) {
            System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }
        
        search.setSex((byte) 1);
        System.out.println("姓名和性别同時存在的查詢");
        List<Student> studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search);
        for (int i = 0; i < studentsBySex.size(); i++) {
            System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE));
        }

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

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

隻有名字時的查詢, 有 where

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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

updateByPrimaryKeySelective

沒有使用 标簽， 那麼我們就要想辦法處理字段全為空的條件， 字段不為空的條件等。 有了這個， 我們隻需要寫 if 标簽即可， 不需要處理類似的問題。

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

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

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

表示當 trim 中含有内容時， 添加 where， 且第一個為 and 或 or 時， 會将其去掉。 而如果沒有内容， 則不添加 where。

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

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

表示當 trim 中含有内容時， 添加 set， 且最後的内容為 , 時， 會将其去掉。 而沒有内容， 不添加 set

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

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. 參數是對象

使用屬性.屬性即可。

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

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

函數接口

/**
     * 擷取 id 集合中的使用者資訊
     * @param ids
     * @return
     */
    List<Student> selectByStudentIdList(List<Integer> ids);

對應 SQL

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

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

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

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

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

結果

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

可以通過foreach來實作批量插入。

/**
     * 批量插入學生
     */
    int insertList(List<Student> students);
           
<insert id="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>
  </insert>
           

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

        List<Student> students = new LinkedList<>();
        Student stu1 = new Student();
        stu1.setName("批量01");
        stu1.setPhone("13888888881");
        stu1.setLocked((byte) 0);
        stu1.setEmail("[email protected]");
        stu1.setSex((byte) 1);
        students.add(stu1);

        Student stu2 = new Student();
        stu2.setName("批量02");
        stu2.setPhone("13888888882");
        stu2.setLocked((byte) 0);
        stu2.setEmail("[email protected]");
        stu2.setSex((byte) 0);
        students.add(stu2);

        System.out.println(studentMapper.insertList(students));
        sqlSession.commit();
        sqlSession.close();
    }
MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

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>

更改後的查詢結果如下

MyBatis動态SQL（認真看看， 以後寫SQL就爽多了）

使用示例：我的 Github:mybatis-dynamic

作者：阿進的寫字台

出處：https://www.cnblogs.com/homejim/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

Mybatis使用 動态sql if choose where set trim foreach bind
上一篇: Win10遠端桌面 出現 身份驗證錯誤，要求的函數不受支援，這可能是由于CredSSP加密Oracle修正 解決方法
下一篇: SQL Serever學習9——基礎查詢語句 SQL語言概述 SQL彙總查詢 連接配接查詢子查詢資料庫中資料的管理

繼續閱讀