一起聊聊MySQL動態SQL拼接

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於動態SQL拼接的相關內容，在實際業務開發中，我們的SQL語句通常是動態拼接而成的，例如條件搜尋功能的SQL語句等等，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

一、動態sql拼接

#目標

• #能夠使用mybatis的標籤實作動態SQL拼接
• 分析
• 我們在前邊的學習過程中，使用的SQL語句都很簡單。而在實際業務開發中，我們的SQL語句通常是動態拼接而成的，例如：條件搜尋功能的SQL語句。 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false"># 提供了一个功能：用户可以在页面上根据username、sex、address进行搜索 # 用户输入的搜索条件：可以是一个条件，也可能是两个、三个 # 只输入一个条件：姓名是&quot;王&quot; 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 '%北京%';</pre><div class="contentsignin">登入後複製</div></div>在Mybatis中，SQL語句是寫在映射配置的XML檔案中的。 Mybatis提供了一些XML的標籤，用來實現動態SQL的拼接。
• 常用的標籤有：

：用來判斷，相當於Java裡的if判斷：通常和if配合，用來取代SQL語句中的

where 1=1

#：用來遍歷一個集合，把集合裡的內容拼接到SQL語句。例如拼接：

in (value1, value2, ...)

：用來定義sql片段，達到重複使用的目的

• 講解

1. 準備Mybatis環境

建立java項目，匯入jar包；準備JavaBean

#建立映射器介面UserDao##建立映射設定檔UserDao.xml

建立全域設定檔SqlMapConfig.xml

建立日誌設定檔log4j.properties

• 2.
  標籤：

• 語法介紹

  <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.

標籤

語法介紹

• #在剛剛的練習的SQL語句中，我們寫了
where 1=1

。如果不寫的話，SQL語句會出現文法錯誤。 Mybatis提供了一個取代

where 1=1的技術：標籤。

程式碼範例

把上一章節的實作程式碼進行最佳化，使用

標籤取代

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>

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

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

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

• 4.

標籤

• 語法介紹

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標籤實作。 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">&lt;!-- foreach标签： 属性： collection：被循环遍历的对象，使用OGNL表达式获取，注意不要加#{} open：循环之前，拼接的SQL语句的开始部分 item：定义变量名，代表被循环遍历中每个元素，生成的变量名 separator：分隔符 close：循环之后，拼接SQL语句的结束部分 标签体： 使用#{OGNL}表达式，获取到被循环遍历对象中的每个元素 --&gt;&lt;foreach collection=&quot;&quot; open=&quot;id in(&quot; item=&quot;id&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt;     #{id}&lt;/foreach&gt;</pre><div class="contentsignin">登入後複製</div></div>

使用範例有搜尋條件類別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片段是重複的，例如：###select * from user###。 Mybatis提供了一個######標籤，把重複的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);
    }

推荐学习：mysql视频教程

以上是一起聊聊MySQL動態SQL拼接的詳細內容。更多資訊請關注PHP中文網其他相關文章！