MySQL動態SQL拼接範例詳解

2022-12-17 14:01:17

目標

能夠使用mybatis的標籤實現動態SQL拼接

分析

我們在前邊的學習過程中，使用的SQL語句都非常簡單。而在實際業務開發中，我們的SQL語句通常是動態拼接而成的，比如：條件搜尋功能的SQL語句。

# 提供了一個功能：使用者可以在頁面上根據username、sex、address進行搜尋
# 使用者輸入的搜尋條件：可以是一個條件，也可能是兩個、三個

# 只輸入一個條件：姓名是"王"
SELECT * FROM USER WHERE username LIKE '%王%'
# 只輸入一個條件：性別是「男」
SELECT * FROM USER WHERE sex = '男'
# 輸入兩個條件：姓名「王」，性別「男」
SELECT * FROM USER WHERE username LIKE '%王%' AND sex = '男'
# 輸入三個條件：姓名「王」，性別「男」，地址「北京」
SELECT * FROM USER WHERE username LIKE '%王%' AND sex = '男' AND address LIKE '%北京%';

在Mybatis中，SQL語句是寫在對映設定的XML檔案中的。Mybatis提供了一些XML的標籤，用來實現動態SQL的拼接。

常用的標籤有：

<if></if>：用來進行判斷，相當於Java裡的if判斷
<where></where>：通常和if配合，用來代替SQL語句中的where 1=1
<foreach></foreach>：用來遍歷一個集合，把集合裡的內容拼接到SQL語句中。例如拼接：in (value1, value2, ...)
<sql></sql>：用於定義sql片段，達到重複使用的目的

講解

1. 準備Mybatis環境

建立java專案，匯入jar包；準備JavaBean
建立對映器介面UserDao
建立對映組態檔UserDao.xml
建立全域性組態檔SqlMapConfig.xml
建立紀錄檔組態檔log4j.properties

2. <if>標籤：

語法介紹

<if test="判斷條件，使用OGNL表示式進行判斷">
	SQL語句內容， 如果判斷為true，這裡的SQL語句就會進行拼接
</if>

使用範例

根據使用者的名稱和性別搜尋使用者資訊。把搜尋條件放到User物件裡，傳遞給SQL語句

對映器介面UserDao上加方法

package com.demo.dao;
import com.demo.domain.User;
import java.util.List;

public interface UserDao {
    /**
     * 根據username和sex搜尋使用者
     * @param user 封裝了搜尋條件的User物件
     * @return 搜尋的結果
     */
    List<User> search1(User user);
}

對映檔案UserDao.xml裡設定statement

<?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.itheima.dao.UserDao">

    <!--
    if標籤：用於條件判斷
        語法：<if test="用OGNL表示式判斷"> 如果判斷為true，這裡的內容會拼接上去 </if>
        注意：標籤裡寫OGNL表示式，不要再加#{}、${}
        常用的OGNL表示式：
            比較：>, <, >=, <=, ==, != 或者 gt, lt, gte, lte, eq, neq
            邏輯：&&,||,! 或者 and, or, not
            呼叫方法：username.length(),  list.size()
    -->
    <select id="search1" resultType="User">
        select * from user where 1=1
        <if test="username != null and username.length()>0">
            and username like "%"#{username}"%"
        </if>
        <if test="sex != null and sex.length()>0">
            and sex = #{sex}
        </if>
    </select>
</mapper>

功能測試，在測試類里加測試方法

package com.demo;

import com.demo.dao.UserDao;
import com.demo.domain.User;
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 SqlTest {

    private UserDao userDao;
    private SqlSession session;
    private InputStream is;

    /**
     * 要求：根據username和sex搜尋使用者
     *      搜尋條件放到user物件裡
     */
    @Test
    public void testSearch(){
        User user = new User();
        // user.setUsername("王");
        // user.setSex("男");

        List<User> userList = userDao.search1(user);
        userList.forEach(System.out::println);
    }

    @Before
    public void init() throws IOException {
        //1. 讀取全域性組態檔
        is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 得到一個SqlSession物件
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        session = factory.openSession();
        userDao = session.getMapper(UserDao.class);
    }

    @After
    public void destroy() throws IOException {
        session.close();
        is.close();
    }
}

3. <where>標籤

語法介紹

在剛剛的練習的SQL語句中，我們寫了where 1=1。如果不寫的話，SQL語句會出現語法錯誤。Mybatis提供了一種代替where 1=1的技術：<where></where>標籤。

程式碼範例

把上一章節的實現程式碼進行優化，使用<where></where>標籤代替where 1=1

對映器UserDao的search1方法：已有，不用修改

/**
 * 根據username和sex搜尋使用者
 * @param user 封裝了搜尋條件的User物件
 * @return 搜尋的結果
 */
List<User> search1(User user);

在對映檔案UserDao.xml裡修改SQL語句

<!--
    where標籤：讓Mybatis幫我們生成一個where關鍵字
        Mybatis會智慧判斷：
            如果一個條件都沒有，就不生成where關鍵字
            如果有條件，會判斷是否有多餘的and關鍵字，把多餘的and去掉
        注意：建議把所有的where條件都放到where標籤裡邊
    -->
<select id="search1" resultType="User">
    select * from user
    <where>
        <if test="username != null and username.length()>0">
            and username like "%"#{username}"%"
        </if>
        <if test="sex != null and sex.length()>0">
            and sex = #{sex}
        </if>
    </where>
</select>

在測試類裡進行功能測試：測試方法不需要修改

@Test
public void testSearch(){
    User user = new User();
    // user.setUsername("王");
    // user.setSex("男");

    List<User> userList = userDao.search1(user);
    userList.forEach(System.out::println);
}

4. <foreach>標籤語法介紹

foreach標籤，通常用於迴圈遍歷一個集合，把集合的內容拼接到SQL語句中。例如，我們要根據多個id查詢使用者資訊，SQL語句：

select * from user where id = 1 or id = 2 or id = 3;
select * from user where id in (1, 2, 3);

假如我們傳參了id的集合，那麼在對映檔案中，如何遍歷集合拼接SQL語句呢？可以使用foreach標籤實現。

<!--
foreach標籤：
	屬性：
		collection：被迴圈遍歷的物件，使用OGNL表示式獲取，注意不要加#{}
		open：迴圈之前，拼接的SQL語句的開始部分
		item：定義變數名，代表被迴圈遍歷中每個元素，生成的變數名
		separator：分隔符
		close：迴圈之後，拼接SQL語句的結束部分
	標籤體：
		使用#{OGNL}表示式，獲取到被迴圈遍歷物件中的每個元素
-->
<foreach collection="" open="id in(" item="id" separator="," close=")">
    #{id}
</foreach>

使用範例

有搜尋條件類QueryVO如下：

package com.itheima.domain;

public class QueryVO {
    private Integer[] ids;

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }
}

在對映器UserDao里加方法

/**
     * QueryVO裡有一個Integer[] ids
     * 要求：根據ids查詢對應的使用者列表
     */
List<User> search2(QueryVO vo);

在對映檔案UserDao.xml裡設定statement

    <!--
    foreach標籤：用於迴圈遍歷
        collection：被迴圈的集合/陣列
        item：定義一個變數
        separator：定義拼接時的分隔符
        open：拼接字串時的開始部分
        close：拼接字串時的結束部分

        相當於 for(Integer id: ids){}
        select * from user where id in(41, 42, 45)
    -->
    <select id="search2" resultType="User">
        <!--select * from user where id in(41, 42, 45)-->
        select * from user where
        <foreach collection="ids" open="id in(" item="id" separator="," close=")">
            #{id}
        </foreach>
    </select>

功能測試

    @Test
    public void testSearch2(){
        QueryVO vo = new QueryVO();
        vo.setIds(new Integer[]{41,42,43,44,45});
        List<User> userList = userDao.search2(vo);
        userList.forEach(System.out::println);
    }

5. <sql>標籤

在對映檔案中，我們發現有很多SQL片段是重複的，比如：select * from user。Mybatis提供了一個<sql>標籤，把重複的SQL片段抽取出來，可以重複使用。

語法介紹

在對映檔案中定義SQL片段：

<sql id="唯一標識">sql語句片段</sql>

在對映檔案中參照SQL片段：

<include refid="sql片段的id"></include>

使用範例

在查詢使用者的SQL中，需要重複編寫：select * from user。把這部分SQL提取成SQL片段以重複使用

要求：QueryVO裡有ids，user物件。根據條件進行搜尋修改QueryVO，增加成員變數user

package com.itheima.domain;

/**
 * @author liuyp
 * @date 2021/09/07
 */
public class QueryVO {
    private Integer[] ids;
    private User user;

    //get/set方法……
}

在對映器UserDao里加方法

    /**
     * 動態SQL拼接的綜合應用：if、where、foreach
     * 要求：QueryVo裡有ids、username、sex值，根據這些值進行搜尋
     */
    List<User> search3(QueryVO vo);

在對映檔案UserDao.xml裡設定statement

<select id="search3" resultType="User">
    <!--select * from user-->
    <include refid="selUser"/>
    <where>
        <if test="ids != null and ids.length > 0">
            <foreach collection="ids" open="and id in(" item="id" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <!--<if test="user != null">
                <if test="user.username != null and user.username.length() > 0">
                    and username like "%"#{user.username}"%"
                </if>
                <if test="user.sex != null and user.sex.length() > 0">
                    and sex = #{user.sex}
                </if>
            </if>-->
        <include refid="userCondition"/>
    </where>
</select>

<!--
    sql標籤：用於定義一個sql片段
    include標籤：什麼時候要參照某個SQL片段，就使用include標籤
    注意：引入SQL片段之後，最終的SQL語句必須要完全符合語法
    -->
<sql id="selUser">select * from user</sql>
<sql id="userCondition">
    <if test="user != null">
        <if test="user.username != null and user.username.length() > 0">
            and username like "%"#{user.username}"%"
        </if>
        <if test="user.sex != null and user.sex.length() > 0">
            and sex = #{user.sex}
        </if>
    </if>
</sql>

在測試類里加測試方法

    @Test
    public void testSearch3(){
        QueryVO vo = new QueryVO();
        vo.setIds(new Integer[]{41,42,43,44,45});

        // User user = new User();
        // user.setUsername("王");
        // user.setSex("男");
        // vo.setUser(user);

        List<User> userList = userDao.search3(vo);
        userList.forEach(System.out::println);
    }