MyBatis学习笔记(十) —— 动态SQL

10、动态SQL

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

动态SQL：

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

2、where 标签：

​ a.若where标签中有条件成立，会自动生成where关键字

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

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

3、trim 标签

​ prefix、suffix: 在标签中内容前面或后面添加指定内容

​ prefixOverrides、suffixOverrides: 在标签中内容前面或后面去掉指定内容

4、choose、when、otherwise

​ 相当于java中的if…else if…else

5、foreach 标签

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

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

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

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

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

6、可以记录一段sql，在需要用的地方使用include标签进行引用。

10.1、if 标签

if标签可通过test属性的表达式进行判断，判断标签中的内容是否有效（是否会拼接到sql中），若表达式的结果为true，这标签中的内容会执行；反之标签中的内容不会执行

Emp.java

package com.fan.mybatis.pojo;/*** @Date: 2023/03/01* @Author: fan* @Description:*/
public class Emp {private Integer empId;private String empName;private Integer age;private String gender;public Emp() {}public Emp(Integer empId, String empName, Integer age, String gender) {this.empId = empId;this.empName = empName;this.age = age;this.gender = gender;}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;}@Overridepublic String toString() {return "Emp{" +"empId=" + empId +", empName='" + empName + '\'' +", age=" + age +", gender='" + gender + '\'' +'}';}
}

DynamicSQLMapper.java

package com.fan.mybatis.mapper;import com.fan.mybatis.pojo.Emp;import java.util.List;/*** @Date: 2023/03/01* @Author: fan* @Description:*/
public interface DynamicSQLMapper {/*** 根据条件查询员工信息* @param emp* @return*/List<Emp> getEmpByCondition(Emp emp);
}

DynamicSQLMapper.xml

<?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.fan.mybatis.mapper.DynamicSQLMapper"><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></select>
</mapper>

DynamicSQLMapperTest.java

package com.fan.mybatis.test;import com.fan.mybatis.mapper.DynamicSQLMapper;
import com.fan.mybatis.pojo.Emp;
import com.fan.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class DynamicMapperTest {@Testpublic void testGetEmpByCondition(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);Emp emp = new Emp(null,"张三",23,"男");List<Emp> list = mapper.getEmpByCondition(emp);list.forEach(System.out::println);}
}

运行测试：

10.2、where 标签

where和if一般结合使用：

a>若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字

b>若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的

and去掉

注意：where标签不能去掉条件最后多余的and

where标签主要是解决多条件查询拼接的问题。

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

第一种方式：添加恒成立的条件 1=1

<?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.fan.mybatis.mapper.DynamicSQLMapper"><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>
</mapper>

第二种方式：where标签选择动态生成where关键字

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

10.3、trim 标签

trim用于去掉或添加标签中的内容

常用属性：

prefix：在trim标签中的内容的前面添加某些内容

prefixOverrides：在trim标签中的内容的前面去掉某些内容

suffix：在trim标签中的内容的后面添加某些内容

suffixOverrides：在trim标签中的内容的后面去掉某些内容

<select id="getEmpByCondition" resultType="Emp">select * from t_emp<trim prefix="where" suffixOverrides="and"><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></trim>
</select>

运行测试：

10.4、choose、when、otherwise 标签

choose、when、otherwise

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

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

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

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

10.5、foreach 标签

foreach标签

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

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

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

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

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

10.5.1、批量添加

DynamicSQLMapper.java

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

DynamicSQLMapper.xml

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

DynamicSQLMapperTest.java

@Test
public void testInsertMoreEmp(){SqlSession sqlSession = SqlSessionUtils.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<Emp> list = Arrays.asList(emp1,emp2,emp3);mapper.insertMoreEmp(list);
}

运行测试，控制台输出结果如下：

查看数据库可以看到批量添加了3条数据

10.5.2、批量删除

DynamicSQLMapper.java

/**
* 批量删除
* @param empIds
*/
void deleteMoreEmp(@Param("empIds") Integer[] empIds);

DynamicSQLMapper.xml

批量删除的第一种写法：

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

批量删除的第二种写法：

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

批量删除的第三种写法：

<delete id="deleteMoreEmp">delete from t_emp where<foreach collection="empIds" item="empId" separator="or">emp_id = #{empId}</foreach>
</delete>

DynamicSQLMapperTest.java

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

运行测试，控制台输出如下

从数据库中可以看到批量删除了2条数据。

10.6、sql 标签

可以记录一段sql，在需要用的地方使用include标签进行引用。

<sql id="empColumns">emp_id,emp_name,age
</sql><select id="getEmpByCondition" resultType="Emp">select <include refid="empColumns"></include> from t_emp<trim prefix="where" suffixOverrides="and"><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></trim>
</select>