9、Mybatis之动态SQL
9.1.1、创建新module
创建名为mybatis_dynamicSQL的新module，过程参考5.1节
9.1.2、创建Emp实体类
package org.rain.mybatis.pojo;
/**
* @author liaojy
* @date 2023/6/20 - 0:09
*/
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;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
'}';
}
}9.1.3、创建Mapper接口和映射文件
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
<?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="org.rain.mybatis.mapper.DynamicSQLMapper">
</mapper>9.1.4、创建测试类
9.2.1、接口方法
List<Emp> getEmpsByCondition(Emp emp);9.2.2、映射文件
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<select id="getEmpsByCondition" resultType="Emp">
select * from t_emp where
<!--
if标签：通过test属性中的表达式判断标签中的内容是否会拼接到sql中
-->
<if test="empName != '' and empName != null">
emp_name = #{empName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="gender != '' and gender != null">
and gender = #{gender}
</if>
</select>9.2.3、测试方法
模拟符合所有if标签的test属性中的表达式判断为true的情况
@Test
public void testCetEmpsByCondition(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper dynamicSQLMapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp();
emp.setEmpName("张三");
emp.setAge(20);
emp.setGender("男");
List<Emp> emps = dynamicSQLMapper.getEmpsByCondition(emp);
System.out.println(emps);
sqlSession.close();
}9.2.4、执行效果
所有if标签的内容都已顺利拼接到sql中
9.3.1、if标签缺陷
注意：当部分或全部if标签的test属性中的表达式判断为false时，sql可能会因为where条件的拼接报错
9.3.2、缺陷修正
9.3.2.1、方式一：增加恒成立条件
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<select id="getEmpsByCondition" resultType="Emp">
select * from t_emp where 1=1
<if test="empName != '' and empName != null">
and emp_name = #{empName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="gender != '' and gender != null">
and gender = #{gender}
</if>
</select>
9.3.2.1、方式二：使用where标签
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<select id="getEmpsByCondition" resultType="Emp">
select * from t_emp
<!--
where标签的效果：
1、若标签中有条件成立，则自动生成where关键字，且能自动删除（前方）多余的and关键字
2、若标签中没有任何条件成立，则没有任何功能
-->
<where>
<if test="empName != '' and empName != null">
and emp_name = #{empName}
</if>
<if test="age != '' and age != null">
and age = #{age}
</if>
<if test="gender != '' and gender != null">
and gender = #{gender}
</if>
</where>
</select>
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<select id="getEmpsByCondition" resultType="Emp">
select * from t_emp
<!--
trim标签：用于添加或去掉标签中的内容
prefix属性：在标签中内容的前面添加指定的内容
suffix属性：在标签中内容的后面添加指定的内容
prefixOverrides属性：在标签中内容的前面去掉指定的内容
suffixOverrides属性：在标签中内容的后面去掉指定的内容
-->
<trim prefix="where" suffixOverrides="and">
<if test="empName != '' and empName != null">
emp_name = #{empName} and
</if>
<if test="age != '' and age != null">
age = #{age} and
</if>
<if test="gender != '' and gender != null">
gender = #{gender} and
</if>
</trim>
</select>
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<select id="getEmpsByCondition" resultType="Emp">
select * from t_emp
<where>
<!--
choose、when和otherwise标签组合（少用）：
when（相当于if...else）至少有一个，otherwise（相当于else）至多有一个
-->
<choose>
<when test="empName != '' and empName != null">
emp_name = #{empName}
</when>
<when test="age != '' and age != null">
age = #{age}
</when>
<when test="gender != '' and gender != null">
gender = #{gender}
</when>
</choose>
</where>
</select>9.6.1、批量添加
9.6.1.1、接口方法
void insertMoreEmp(@Param("emps") List<Emp> emps);9.6.1.2、映射文件
<!--void insertMoreEmp(@Param("emps") List<Emp> emps);-->
<!--
foreach标签：
collection属性：设置要循环的数组或集合
item属性：设置表示数组或集合中迭代元素的变量名
separator属性：设置每次foreach循环之间的分隔符
-->
<insert id="insertMoreEmp">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null ,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>9.6.1.3、测试方法
@Test
public void testInsertMoreEmp(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper dynamicSQLMapper = 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> emps = Arrays.asList(emp1, emp2, emp3);
dynamicSQLMapper.insertMoreEmp(emps);
sqlSession.close();
}9.6.1.4、执行效果
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
9.6.2、批量删除
9.6.2.1、接口方法
void deleteMoreEmp(@Param("empIds") Integer[] empIds);9.6.2.2、映射文件
<!--void deleteMoreEmp(@Param("empIds") Integer[] empIds);-->
<!--
foreach标签：
collection属性：设置要循环的数组或集合
item属性：设置表示数组或集合中迭代元素的变量名
separator属性：设置每次foreach循环之间的分隔符
open属性：设置foreach循环体的前缀
close属性：设置foreach循环体的后缀
-->
<delete id="deleteMoreEmp">
delete from t_emp where emp_id in
<foreach collection="empIds" item="empId" separator="," open="(" close=")">
#{empId}
</foreach>
</delete>9.6.2.3、测试方法
@Test
public void testDeleteMoreEmp(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper dynamicSQLMapper = sqlSession.getMapper(DynamicSQLMapper.class);
Integer[] empIds = {6, 7};
dynamicSQLMapper.deleteMoreEmp(empIds);
sqlSession.close();
}9.6.2.4、执行效果
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
++++++++++++++++++++++++++++分割线++++++++++++++++++++++++++++
9.6.2.5、映射文件（变体）
<delete id="deleteMoreEmp">
delete from t_emp where
<foreach collection="empIds" item="empId" separator="or">
emp_id = #{empId}
</foreach>
</delete>
<!--
sql标签：设置一个sql片段
-->
<sql id="empColumns">
emp_id,emp_name,age,gender
</sql>
<!--List<Emp> getEmpsByCondition(Emp emp);-->
<!--
include标签：
refid属性：通过sql标签的id引用相关sql片段
-->
<select id="getEmpsByCondition" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
</select>手机扫一扫
移动阅读更方便
你可能感兴趣的文章