MyBatis(五）动态Sql

MyBatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。 

1.if标签：

if标签可通过test属性的表达式进行判断，若表达式的结果为true,则标签中的内容会执行；反之标签中的内容不会执行。

如果我们没有去传输某些请求参数，而我在服务器中却获取了请求参数，获取的都是null。

如果我们当前提交表单的，但是我们的文本框里面什么都没有输入，我们提交到服务器中的就是空字符串" "。

在服务器中判断一下，null和"  ",没有设置条件。不等于null和" ",是设置了这个条件，就要把条件拼接到sql中。

我们设置的实体类如下所示：

package com.rgf.mybatis.pojo;
 
public class Emp {
    private  Integer empId;
    private  String empName;
    private  Integer age;
    private  String gender;
 
    public Emp(Integer empId, String empName, Integer age, String gender) {
        this.empId = empId;
        this.empName = empName;
        this.age = age;
        this.gender = gender;
    }
 
    public Emp() {
    }
 
    public Integer getEmpId() {
        return empId;
    }
 
    public void setEmpId(Integer empId) {
        this.empId = empId;
    }
 
    public String getEmpName() {
        return empName;
    }
 
    public void setEmpName(String empName) {
        this.empName = empName;
    }
 
    public Integer getAge() {
        return age;
    }
 
    public void setAge(Integer age) {
        this.age = age;
    }
 
    public String getGender() {
        return gender;
    }
 
    public void setGender(String gender) {
        this.gender = gender;
    }
 
    @Override
    public String toString() {
        return "Emp{" +
                "empId=" + empId +
                ", empName='" + empName + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                '}';
    }
}

我们在utiles包里面封装的sqlsession如下所示：

package com.rgf.mybatis.utils;
 
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 java.io.IOException;
import java.io.InputStream;
 
public class SqlSessionUtil {
    public static SqlSession getSqlSession() {
        SqlSession sqlSession = null;
        try {
            //获取核心配置文件的输入流
            //此时我们为捕获异常，这个时候要抛的话要抛到调用他的地方。
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //获取SqlSessionFactoryBuilder对象
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
            //获取Sqlsession对象
            sqlSession = sqlSessionFactory.openSession(true);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlSession;
 
    }
}

我们对于jdbc和核心配置的文件可以在之前的博客中进行查找。 

我们创建接口DynamicSQLMapper，在其中设置该方法：

 /**
     * 根据条件查询员工信息
     * @param emp
     * @return
     */
    List getEmpByCondition(Emp emp);

我们设置的映射文件DynamicSQLMapper,xml如下所示：

"1.0" encoding="UTF-8" ?>
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
"com.rgf.mybatis.mapper.DynamicSQLMapper">
    "getEmpByCondition" resultType="Emp">
        select * from t_emp where
        <if test="empName !=null and empName!=''">
            emp_name=#{empName}
        if>
        <if test="age != null and age!=''">
            and  age=#{age}
        if>
       <if test="gender!=null and gender!=''">
           and gender=#{gender}
       if>
    

我们设置的测试类如下所示：

public class DynamicMapperTest {
    @Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"男");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }
}

我们运行之后如下所示：

if,通过test属性中的表达式判断标签中的内容是否有效（是否会拼接到sql中）

2.where标签

我们将测试修改如下所示：

@Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"",20,"男");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们将张三修改为空字符串，运行之后如下所示：

 我们看到sql语句当empName不满足的时候，直接把下一条满足的跟在了where后面。（where后面不可以直接跟and)

我们再将测试类修改如下所示：

@Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"",null,"");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们运行之后如下所示：

我们发现在条件都不满足的情况下，我们都不会拼接到where后面。我们的sql语句后面多出来一个where。

我们的解决方法如下所示：

解决方法一：

 我们将映射文件修改如下所示：

<!--
    动态SQL:
    1.if,通过test属性中的表达式判断标签中的内容是否有效（是否会拼接到sql中 ）
    -->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp where 1=1
        <if test="empName !=null and empName!=''">
            and  emp_name=#{empName}
        </if>
        <if test="age != null and age!=''">
            and  age=#{age}
        </if>
       <if test="gender!=null and gender!=''">
           and gender=#{gender}
       </if>
    </select>

我们点击运行后如下所示：

我们发现把当前所有数据都查找出来了。

 我们的测试类继续修改如下所示：

 @Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"",20,"男");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们运行后如下所示：

 解决方法二：

我们的测试方法如下所示：

 @Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"男");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们将我们的映射文件修改如下所示：

 <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
      <where>
          <if test="empName !=null and empName!=''">
              emp_name=#{empName}
          </if>
          <if test="age != null and age!=''">
              and  age=#{age}
          </if>
          <if test="gender!=null and gender!=''">
              and gender=#{gender}
          </if>
      </where>
 
    </select>

我们将代码运行之后如下所示：

 如果我们将测试类进行修改：

 @Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"",20,"男");
        List<Emp> list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们将empName改为空字符串，此时第一条if语句不成立，我们进行运行如下所示：

 我们继续进行测试：将年龄改为null,我们如下所示：

 @Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"",null,"");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

我们运行之后如下所示：

我们发现此时条件都不成立的时候，where标签没有任何功能。

where标签的功能总结如下所示：

a、若where标签中有条件成立，然后会自动生成where关键字

b、会自动将where标签中内容前多余的and去掉,但是其中内容后多余的and无法去掉

c、若where标签中没有任何一个条件成立，则where标签没有任何功能

 3、trim标签

我们将测试类修改为如下所示：

@Test
    public  void  testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"");
        List list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }

同时我们将映射文件里面的and放到内容后面，采用trim标签如下所示：

"getEmpByCondition" resultType="Emp">
        select * from t_emp
        "where" suffixOverrides="and">
            <if test="empName !=null and empName!=''">
                emp_name=#{empName} and
            if>
            <if test="age != null and age!=''">
                  age=#{age}  and
            if>
            <if test="gender!=null and gender!=''">
                gender=#{gender}
            if>
        
    
 

其中的trim的属性如下所示： 

其中有四个属性

prefix:是在其中内容前面加上某些内容

prefixOverrides:是在我们当前内容的前面去掉指定内容

suffix:是在我们当前标签中内容的后面去添加指定内容

suffixOverrides:是在我们当前标签中内容的后面去掉指定内容 

我们运行之后如下所示：

我们发现此时where有了，后面多余的and也没有了。 

4、choose(父标签）、when（相当于if-else-if)、otherwise(相当于else)

我们创建新的方法如下所示：

 /**
     * 使用choose查询员工信息
     * @param emp
     * @return
     */
    List getEmpByChoose(Emp emp);

我们所写的映射文件如下所示：

"getEmpByChoose" resultType="Emp">
    select * from t_emp 
    
        
            "empName != null and empName !='' ">
                emp_name=#{empName}
            
            "age != null and age != ''">
                age=#{age}
            
            "gender != null and gender != ''">
                gender=#{gender}
            
        
    

 我们所写的测试类如下所示：

 @Test
    public  void  testGetEmpByChoose(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"");
        List list = mapper.getEmpByChoose(emp);
        list.forEach(System.out::println);
    }

我们运行之后如下所示：

 我们发现只判断了emp_name,符合的话即停止进行判断。否则继续判断。

choose、when、otherwise

相当于java中的if.....else if....else

when至少设置一个，otherwise最多设置一个

5、foreach标签：（批量添加）

collection:设置要循环的数组或集合

item:用一个字符串表示数组或集合中的每一个数据

separator:设置每次循环的数据之间的分隔符

open:循环的所有内容以什么开始

close:循环的所有内容以什么结束

 我们设置的接口方法如下所示：

 /**
     * 批量添加员工信息
     * @param emps
     */
 
    void insertMoreEmp(@Param("emps") List emps);

我们设置的映射文件如下所示：

获取自动递增的id使用：keyProperty和useGenerateKeys

 "insertMoreEmp" >
        
        insert into t_emp values
        
        "emps" item="emp" separator=",">
            (null,#{emp.empName},#{emp.age},#{emp.gender},null)
        
    

我们设置的测试类如下所示：

@Test
    public  void  testInsertMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"小明1",20,"男");
        Emp emp2 = new Emp(null,"小明2",20,"男");
        Emp emp3 = new Emp(null,"小明3",20,"男");
        List list = Arrays.asList(emp1, emp2, emp3);
        mapper.insertMoreEmp(list);
    }

我们运行之后如下所示：

我们打开数据库表，我们发现成功的添加进去了：

 6、foreach标签（批量删除）：

我们在mapper接口里面设置的方法如下所示：

/**如果mapper接口方法的参数是一个list或者一个数组，mybatis都会把数据放在map中的，会以List为键，list集合为值，以这个参数为值放在map中。
     *如果是数组的话，mybatis也会把它放在map里面，以array为键，以当前的参数为值
     * 批量删除的功能
     * @param empIds
     */
    void deleteMoreEmp(@Param("empIds") Integer[]empIds);

我们编写的映射文件如下所示：

 "deleteMoreEmp">
        delete from t_emp where emp_id in
        (
        "empIds " item="empId" separator=",">
            #{empId}
        
        )
    

我们编写的测试类如下所示：

@Test
    public  void  testDeleteMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
       Integer [] empIds=new Integer[]{6,7};
       mapper.deleteMoreEmp(empIds);
    }

我们运行之后如下所示：

 我们也可以将映射文件修改如下所示：

"deleteMoreEmp">
        delete from t_emp where emp_id in
        
        "empIds " item="empId" separator="," open="(" close=")">
            #{empId}
        
    

我们也可以用另一种方式：

我们将映射文件修改如下所示：

"deleteMoreEmp" >
    delete from t_emp where
    "empIds" item="empId" separator="or">
 
        emp_id=#{empId}
    

7、sql标签

我们的sql用法如下所示：

 6、sql片段
   可以记录一段sql,在需要用的地方使用include标签进行引用
   "empColumns">
    emp_id,emp_name,age,gender,dept_id
 select "empColumns"> from t_emp where 1=1
    -->

 我们在映射文件里面设计如下所示：

"empColumns">
    emp_id,emp_name,age,gender,dept_id
 
 
    "getEmpByConditionOne" resultType="Emp">
        select "empColumns"> from t_emp where 1=1
        <if test="empName !=null and empName!=''">
            and  emp_name=#{empName}
        if>
        <if test="age != null and age!=''">
            and  age=#{age}
        if>
        <if test="gender!=null and gender!=''">
            and gender=#{gender}
        if>
     

我们所使用的动态sql设计总结如下所示：