MySQL——动态SQL拼接-蒲公英云

一、动态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环境

创建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">
```
功能测试，在测试类里加测试方法

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 search1(User user);
在映射文件UserDao.xml里修改SQL语句

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

@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 search2(QueryVO vo);

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

<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 * from user

and username like “%”#{user.username}”%”

and sex = #{user.sex}

在测试类里加测试方法

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