MySQL——动态SQL拼接

深藏阁楼爱情的钟 2024-04-01 12:54 416阅读 0赞

### 一、动态sql拼接 ###

#### 目标 ####

*  能够使用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环境 #####

1.  创建java项目，导入jar包；准备JavaBean
2.  创建映射器接口UserDao
3.  创建映射配置文件UserDao.xml
4.  创建全局配置文件SqlMapConfig.xml
5.  创建日志配置文件log4j.properties

##### 2. `<if>`标签： #####

###### 语法介绍 ######

<if test="判断条件，使用OGNL表达式进行判断">
    	SQL语句内容， 如果判断为true，这里的SQL语句就会进行拼接
    </if>

###### 使用示例 ######

*  根据用户的名称和性别搜索用户信息。把搜索条件放到User对象里，传递给SQL语句

1.  映射器接口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);
    }

1.  映射文件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">
    
        
        <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>

1.  功能测试，在测试类里加测试方法

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`

1.  映射器UserDao的search1方法：已有，不用修改

/**
     * 根据username和sex搜索用户
     * @param user 封装了搜索条件的User对象
     * @return 搜索的结果
     */
    List<User> search1(User user);

1.  在映射文件UserDao.xml里修改SQL语句

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

1.  在测试类里进行功能测试：测试方法不需要修改

@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="" open="id in(" item="id" separator="," close=")">
        #{id}
    </foreach>

###### 使用示例 ######

1.  有搜索条件类QueryVO如下：

package com.itheima.domain;
    
    public class QueryVO {
        
        private Integer[] ids;
    
        public Integer[] getIds() {
        
            return ids;
        }
    
        public void setIds(Integer[] ids) {
        
            this.ids = ids;
        }
    }

1.  在映射器UserDao里加方法

/**
         * QueryVO里有一个Integer[] ids
         * 要求：根据ids查询对应的用户列表
         */
    List<User> search2(QueryVO vo);

1.  在映射文件UserDao.xml里配置statement

<select id="search2" resultType="User">
            
            select * from user where
            <foreach collection="ids" open="id in(" item="id" separator="," close=")">
                #{id}
            </foreach>
        </select>

1.  功能测试

@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片段：

###### 使用示例 ######

在查询用户的SQL中，需要重复编写：`select * from user`。把这部分SQL提取成SQL片段以重复使用

*  要求：QueryVO里有ids，user对象。根据条件进行搜索

1.  修改QueryVO，增加成员变量user

package com.itheima.domain;
    
    /**
     * @author liuyp
     * @date 2021/09/07
     */
    public class QueryVO {
        
        private Integer[] ids;
        private User user;
    
        //get/set方法……
    }

1.  在映射器UserDao里加方法

/**
         * 动态SQL拼接的综合应用：if、where、foreach
         * 要求：QueryVo里有ids、username、sex值，根据这些值进行搜索
         */
        List<User> search3(QueryVO vo);

1.  在映射文件UserDao.xml里配置statement

<select id="search3" resultType="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>
            
            <include refid="userCondition"/>
        </where>
    </select>
    
    
    <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>

1.  在测试类里加测试方法

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