MyBatis 動態 SQL 最全教程，這樣寫 SQL 太優雅了！

Java技术栈發表於2024-04-26

一、MyBatis動態 sql 是什麼

動態 SQL 是 MyBatis 的強大特性之一。在 JDBC 或其它類似的框架中，開發人員通常需要手動拼接 SQL 語句。根據不同的條件拼接 SQL 語句是一件極其痛苦的工作。

例如，拼接時要確保新增了必要的空格，還要注意去掉列表最後一個列名的逗號。而動態 SQL 恰好解決了這一問題，可以根據場景動態的構建查詢。

動態SQL（code that is executed dynamically），它一般是根據使用者輸入或外部條件動態組合的SQL語句塊。動態SQL能靈活的發揮SQL強大的功能、方便的解決一些其它方法難以解決的問題。相信使用過動態SQL的人都能體會到它帶來的便利，然而動態SQL有時候在執行效能 (效率)上面不如靜態SQL，而且使用不恰當，往往會在安全方面存在隱患 (SQL 注入式攻擊)。

1.Mybatis 動態 sql 是做什麼的?

Mybatis 動態 sql 可以讓我們在 Xml 對映檔案內，以標籤的形式編寫動態 sql，完成邏輯判斷和動態拼接 sql 的功能。

2.Mybatis 的 9 種動態 sql 標簽有哪些？

3.動態 sql 的執行原理？

原理為：使用 OGNL 從 sql 引數物件中計算表示式的值，根據表示式的值動態拼接 sql，以此來完成動態 sql 的功能。

推薦一個開源免費的 Spring Boot 實戰專案：

https://github.com/javastacks/spring-boot-best-practice

二、MyBatis標籤

1.if標籤：條件判斷

MyBatis if 類似於 Java 中的 if 語句，是 MyBatis 中最常用的判斷語句。使用 if 標籤可以節省許多拼接 SQL 的工作，把精力集中在 XML 的維護上。

1）不使用動態sql

<select id="selectUserByUsernameAndSex"
        resultType="user" parameterType="com.ys.po.User">
    <!-- 這裡和普通的sql 查詢語句差不多，對於只有一個引數，後面的 #{id}表示佔位符，裡面          不一定要寫id,
         寫啥都可以，但是不要空著，如果有多個引數則必須寫pojo類裡面的屬性 -->
    select * from user where username=#{username} and sex=#{sex}
</select>

if 語句使用方法簡單，常常與 test 屬性聯合使用。語法如下:

<if test="判斷條件">    SQL語句</if>

2）使用動態sql

上面的查詢語句，我們可以發現，如果 #{username} 為空，那麼查詢結果也是空，如何解決這個問題呢？使用 if 來判斷，可多個 if 語句同時使用。

以下語句表示為可以按照網站名稱（name）或者網址（url）進行模糊查詢。如果您不輸入名稱或網址，則返回所有的網站記錄。但是，如果你傳遞了任意一個引數，它就會返回與給定引數相匹配的記錄。

<select id="selectAllWebsite" resultMap="myResult">  
    select id,name,url from website 
    where 1=1    
   <if test="name != null">        
       AND name like #{name}   
   </if>    
   <if test="url!= null">        
       AND url like #{url}    
   </if>
</select>

2.where+if標籤

where、if同時使用可以進行查詢、模糊查詢

注意，<if>失敗後， <where> 關鍵字只會去掉庫表欄位賦值前面的and，不會去掉語句後面的and關鍵字，即注意，<where> 只會去掉<if> 語句中的最開始的and關鍵字。所以下面的形式是不可取的

<select id="findQuery" resultType="Student">
    <include refid="selectvp"/>
    <where>
        <if test="sacc != null">
            sacc like concat('%' #{sacc} '%')
        </if>
        <if test="sname != null">
            AND sname like concat('%' #{sname} '%')
        </if>
        <if test="sex != null">
            AND sex=#{sex}
        </if>
        <if test="phone != null">
            AND phone=#{phone}
        </if>
    </where>
</select>

這個“where”標籤會知道如果它包含的標籤中有返回值的話，它就插入一個‘where’。此外，如果標籤返回的內容是以AND 或OR 開頭的，則它會剔除掉。

3.set標籤

set可以用來修改

<update id="upd">
    update student
    <set>
        <if test="sname != null">sname=#{sname},</if>
        <if test="spwd != null">spwd=#{spwd},</if>
        <if test="sex != null">sex=#{sex},</if>
        <if test="phone != null">phone=#{phone}</if>
    sid=#{sid}
    </set>
    where sid=#{sid}
</update>

4.choose(when,otherwise) 語句

有時候，我們不想用到所有的查詢條件，只想選擇其中的一個，查詢條件有一個滿足即可，使用 choose 標籤可以解決此類問題，類似於 Java 的 switch 語句

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
      select * from user
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
  </select>

也就是說，這裡我們有三個條件，id、username、sex，只能選擇一個作為查詢條件

如果 id 不為空，那麼查詢語句為：select * from user where id=?
如果 id 為空，那麼看username 是否為空，如果不為空，那麼語句為 select * from user where username=?;
如果 username 為空，那麼查詢語句為 select * from user where sex=?

5.trim

trim標記是一個格式化的標記，可以完成set或者是where標記的功能

推薦一個開源免費的 Spring Boot 實戰專案：

https://github.com/javastacks/spring-boot-best-practice

①、用 trim 改寫上面第二點的 if+where 語句

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user
    <!-- <where>
        <if test="username != null">
           username=#{username}
        </if>
         
        <if test="username != null">
           and sex=#{sex}
        </if>
    </where>  -->
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username != null">
           and username=#{username}
        </if>
        <if test="sex != null">
           and sex=#{sex}
        </if>
    </trim>
</select>

prefix：字首
prefixoverride：去掉第一個and或者是or

②、用 trim 改寫上面第三點的 if+set 語句

<!-- 根據 id 更新 user 表的資料 -->
<update id="updateUserById" parameterType="com.ys.po.User">
    update user u
        <!-- <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set> -->
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex},
            </if>
        </trim>
     
     where id=#{id}
</update>

suffix：字尾
suffixoverride：去掉最後一個逗號（也可以是其他的標記，就像是上面字首中的and一樣）

③、trim+if同時使用可以新增

<insert id="add">
    insert  into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="sname != null">sname,</if>
        <if test="spwd != null">spwd,</if>
        <if test="sex != null">sex,</if>
        <if test="phone != null">phone,</if>
    </trim>

    <trim prefix="values (" suffix=")"  suffixOverrides=",">
        <if test="sname != null">#{sname},</if>
        <if test="spwd != null">#{spwd},</if>
        <if test="sex != null">#{sex},</if>
        <if test="phone != null">#{phone}</if>
    </trim>

</insert>

6.MyBatis foreach標籤

foreach是用來對集合的遍歷，這個和Java中的功能很類似。通常處理SQL中的in語句。

foreach 元素的功能非常強大，它允許你指定一個集合，宣告可以在元素體內使用的集合項（item）和索引（index）變數。它也允許你指定開頭與結尾的字串以及集合項迭代之間的分隔符。這個元素也不會錯誤地新增多餘的分隔符

你可以將任何可迭代物件（如 List、Set 等）、Map 物件或者陣列物件作為集合引數傳遞給 foreach。當使用可迭代物件或者陣列時，index 是當前迭代的序號，item 的值是本次迭代獲取到的元素。當使用 Map 物件（或者 Map.Entry 物件的集合）時，index 是鍵，item 是值。

//批次查詢
<select id="findAll" resultType="Student" parameterType="Integer">
    <include refid="selectvp"/> WHERE sid in
    <foreach item="ids" collection="array"  open="(" separator="," close=")">
        #{ids}
    </foreach>
</select>
//批次刪除
<delete id="del"  parameterType="Integer">
    delete  from  student  where  sid in
    <foreach item="ids" collection="array"  open="(" separator="," close=")">
        #{ids}
    </foreach>
</delete>

整合案例

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yzx.mapper.StuMapper">
    <sql id="selectvp">
        select  *  from  student
    </sql>
    
    <select id="find" resultType="Student">
        <include refid="selectvp"/>
    </select>

    <select id="findbyid"  resultType="student">
        <include refid="selectvp"/>
        WHERE 1=1
        <if test="sid != null">
            AND sid like #{sid}
        </if>
    </select>

        <select id="findQuery" resultType="Student">
            <include refid="selectvp"/>
            <where>
                <if test="sacc != null">
                    sacc like concat('%' #{sacc} '%')
                </if>
                <if test="sname != null">
                    AND sname like concat('%' #{sname} '%')
                </if>
                <if test="sex != null">
                    AND sex=#{sex}
                </if>
                <if test="phone != null">
                    AND phone=#{phone}
                </if>
            </where>
        </select>

    <update id="upd">
        update student
        <set>
            <if test="sname != null">sname=#{sname},</if>
            <if test="spwd != null">spwd=#{spwd},</if>
            <if test="sex != null">sex=#{sex},</if>
            <if test="phone != null">phone=#{phone}</if>
        sid=#{sid}
        </set>
        where sid=#{sid}
    </update>

    <insert id="add">
        insert  into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sname != null">sname,</if>
            <if test="spwd != null">spwd,</if>
            <if test="sex != null">sex,</if>
            <if test="phone != null">phone,</if>
        </trim>

        <trim prefix="values (" suffix=")"  suffixOverrides=",">
            <if test="sname != null">#{sname},</if>
            <if test="spwd != null">#{spwd},</if>
            <if test="sex != null">#{sex},</if>
            <if test="phone != null">#{phone}</if>
        </trim>

    </insert>
    <select id="findAll" resultType="Student" parameterType="Integer">
        <include refid="selectvp"/> WHERE sid in
        <foreach item="ids" collection="array"  open="(" separator="," close=")">
            #{ids}
        </foreach>
    </select>

    <delete id="del"  parameterType="Integer">
        delete  from  student  where  sid in
        <foreach item="ids" collection="array"  open="(" separator="," close=")">
            #{ids}
        </foreach>
    </delete>



</mapper>

測試類:

package com.yzx.test;

import com.yzx.entity.Student;
import com.yzx.mapper.StuMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class StuTest {
    SqlSession sqlSession=null;
    InputStream is=null;

    @Before
    public   void  before() throws IOException {
        //1.讀取核心配置檔案
        is= Resources.getResourceAsStream("sqlMapperConfig.xml");
        //2.拿到工廠構建類
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        //3.拿到具體工廠
        SqlSessionFactory build=sqlSessionFactoryBuilder.build(is);
        //4.拿到session
        sqlSession = build.openSession();
    }

    @After
    public  void  after(){
        //7，提交事務
        sqlSession.commit();
        //8.關閉資源
        sqlSession.close();
        if(is!=null){
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        };
    }

    //查詢所有
    @Test
    public  void  find(){
        //5.獲取具體的mapper介面
        StuMapper mapper=sqlSession.getMapper(StuMapper.class);
        //6.呼叫執行
        List<Student> list=mapper.find();
        list.forEach(a-> System.out.println(a));
    }
    //查詢單個
    @Test
    public  void  findbyid(){

        StuMapper mapper=sqlSession.getMapper(StuMapper.class);
        List<Student> list=mapper.findbyid(2);
        list.forEach(a-> System.out.println(a));
    }
    //模糊查詢
    @Test
    public  void  findQuery(){

        StuMapper mapper=sqlSession.getMapper(StuMapper.class);

        Student  stu=new Student();
        stu.setSname("小");
        stu.setSex("男");
        List<Student> list=mapper.findQuery(stu);
        list.forEach(a-> System.out.println(a));
    }
    //修改
    @Test
    public  void  upd(){

        StuMapper mapper=sqlSession.getMapper(StuMapper.class);

        Student  stu=new Student();
        stu.setSid(3);
        stu.setSname("小若");
        stu.setSex("人妖");
        int i=mapper.upd(stu);
        System.out.println("修改了"+i+"條資料"+"  "+stu.toString());

    }
    //新增
    @Test
    public  void  add(){

        StuMapper mapper=sqlSession.getMapper(StuMapper.class);

        Student  stu=new Student();
        stu.setSname("小賀");
        stu.setSex("男");
        stu.setPhone("99999999");
        int i=mapper.add(stu);
        System.out.println("新增了"+i+"條資料"+"  "+stu.toString());

    }

    //批次操作
    @Test
    public  void  findAll(){

        StuMapper mapper=sqlSession.getMapper(StuMapper.class);
        Integer[] i={1,2,3,4};
        List<Student> list=mapper.findAll(i);
        list.forEach(a-> System.out.println(a));
    }
    //批次操作

    //批次刪除
    @Test
    public  void  del(){
        StuMapper mapper=sqlSession.getMapper(StuMapper.class);
        Integer[] i={1,2,3,4};
        int i1=mapper.del(i);
        System.out.println("刪除了"+i1+"條資料");
    }
}

7.sql

在實際開發中會遇到許多相同的SQL，比如根據某個條件篩選，這個篩選很多地方都能用到，我們可以將其抽取出來成為一個公用的部分，這樣修改也方便，一旦出現了錯誤，只需要改這一處便能處處生效了，此時就用到了<sql>這個標籤了。

當多種型別的查詢語句的查詢欄位或者查詢條件相同時，可以將其定義為常量，方便呼叫。為求<select>結構清晰也可將 sql 語句分解。

<sql id="selectvp">
    select  *  from  student
</sql>

8.include

這個標籤和<sql>是天仙配，是共生的，include用於引用sql標籤定義的常量。比如引用上面sql標籤定義的常量

refid這個屬性就是指定<sql>標籤中的id值（唯一標識）

<select id="findbyid"  resultType="student">
    <include refid="selectvp"/>
    WHERE 1=1
    <if test="sid != null">
        AND sid like #{sid}
    </if>
</select>

9.如何引用其他XML中的SQL片段

比如你在com.xxx.dao.xxMapper這個Mapper的XML中定義了一個SQL片段如下：

<sql id="Base_Column_List"> ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY</sql>

此時我在com.xxx.dao.PatinetMapper中的XML檔案中需要引用，如下：

<include refid="com.xxx.dao.xxMapper.Base_Column_List"></include>

三、MyBatis關聯查詢

1.MyBatis一對多關聯查詢

<!--一對多-->
<resultMap id="myStudent1" type="student1">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <result property="sex" column="sex"/>
    <result property="sage" column="sage"/>
    <collection property="list" ofType="teacher">
        <id property="tid" column="tid"/>
        <result property="tname" column="tname"/>
        <result property="tage" column="tage"/>
    </collection>
</resultMap>

<!--一對多-->
<select id="find1" resultMap="myStudent1">
    select  *  from  student1  s  left  join  teacher  t  on s.sid=t.sid
</select>

2.MyBatis多對一關聯查詢

<!--多對一-->
<resultMap id="myTeacher" type="teacher">
    <id property="tid" column="tid"/>
    <result property="tname" column="tname"/>
    <result property="tage" column="tage"/>
    <association property="student1" javaType="Student1">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="sex" column="sex"/>
        <result property="sage" column="sage"/>
    </association>
</resultMap>


<!--多對一-->
<select id="find2" resultMap="myTeacher">
select  *  from  teacher  t right join student1 s on  t.sid=s.sid
</select>

3.MyBatis多對多關聯查詢

<!--多對多 以誰為主表查詢的時候，主表約等於1的一方,另一方相當於多的一方-->
<select id="find3" resultMap="myStudent1">
    select  *  from  student1 s  left join relevance r on  s.sid=r.sid  left join teacher t on  r.tid=t.tid
</select>

版權宣告：本文為CSDN博主「變成禿頭怪」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處連結及本宣告。原文連結：https://blog.csdn.net/SGLP521/article/details/121509714

更多文章推薦：

1.Spring Boot 3.x 教程，太全了！

2.2,000+ 道 Java面試題及答案整理(2024最新版)

3.免費獲取 IDEA 啟用碼的 7 種方式（2024最新版）

覺得不錯，別忘了隨手點贊+轉發哦！

MyBatis 動態 SQL
2020-02-22
MyBatisSQL
mybatis動態SQL
2024-10-30
MyBatisSQL
【sql調優】動態取樣
2010-09-08
SQL
TDengine 如何進行 SQL 寫入？官方最全教程來了
2022-09-09
SQL
1 Mybatis動態SQL
2022-05-05
MyBatisSQL
Mybatis--動態SQL
2020-12-14
MyBatisSQL
MyBatis(七) 動態SQL
2018-07-09
MyBatisSQL
MyBatis基礎：MyBatis動態SQL（3）
2017-09-05
MyBatisSQL
mybatis動態sql總結
2021-08-11
MyBatisSQL
Mybatis-06 動態Sql
2021-02-21
MyBatisSQL
Mybatis 動態 SQL 詳解
2017-08-30
MyBatisSQL
MyBatis4：動態SQL
2016-03-17
MyBatisSQL
MyBatis框架之SQL對映和動態SQL
2018-06-20
MyBatis框架SQL
Mybatis介紹之動態SQL
2021-03-17
MyBatisSQL
Java-Mybatis動態SQL整理
2021-08-17
JavaMyBatisSQL
MyBatis對動態SQL的支援
2018-07-16
MyBatisSQL
mybatis動態sql與分頁
2020-10-28
MyBatisSQL
這樣的sql怎麼寫？
2007-08-20
SQL
這樣在管理後臺裡實現 403 頁面實在是太優雅了
2023-02-27
Mybatis 動態執行SQL語句
2021-07-29
MyBatisSQL
Mybatis深入瞭解（五）----動態SQL
2016-07-16
MyBatisSQL
PHPer這樣寫程式碼也許更優雅
2018-06-21
PHP
程式碼這樣寫更優雅 (Python 版)
2017-03-01
Python
程式碼這樣寫更優雅(Python 版)
2017-06-11
Python
史上最全SQL優化方案
2019-02-01
SQL優化
【MyBatis學習總結（五），動態SQL】
2020-10-26
MyBatisSQL
Mybatis where 1=1 動態sql問題
2020-11-26
MyBatisSQL
Mybatis學習筆記（6）-動態SQL
2018-02-12
MyBatis筆記SQL
mybatis入門基礎(五)----動態SQL
2015-07-01
MyBatisSQL
程式碼這樣寫不止於優雅（Python版）
2017-03-08
Python
程式碼這樣寫不止於優雅（Python 版）
2017-06-15
Python
SQL優化這麼做就對了
2021-02-17
SQL優化
MyBatis學習之三、動態SQL語句
2013-08-01
MyBatisSQL
【禁止血壓飆升】阿里大佬寫的 Controller 太優雅了！
2024-08-08
阿里Controller
Laravel 優雅填充 SQL 資料
2021-01-26
LaravelSQL
Mybatis（一）Porxy動態代理和sql解析替換
2021-04-29
MyBatisSQL
MyBatis標籤實現的動態SQL語句
2018-09-11
MyBatisSQL
動態SQL
2015-03-20
SQL