Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了 解决 拼接SQL语句字符串时的痛点问题。
0、准备工作
1、引入依赖
<dependencies>
<!-- Mybatis核心 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- junit测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
2、数据表数据
CREATE TABLE t_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
CREATE TABLE t_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
age INT,
gender CHAR,
dept_id INT
);
INSERT INTO t_dept(dept_name) VALUES ('A'),('B'),('C');
INSERT INTO t_emp(emp_name,age,gender,dept_id) VALUES
('张三',20,'女',1),
('李四',22,'女',2),
('王五',21,'男',3),
('赵六',23,'男',1),
('田七',21,'女',3);
3、创建与数据表相对应 java 实体类
public class Emp {
private Integer empId;
private String empName;
private Integer age;
private String gender;
private Integer deptId;
public Emp() {
}
public Emp(Integer empId, String empName, Integer age, String gender, Integer deptId) {
this.empId = empId;
this.empName = empName;
this.age = age;
this.gender = gender;
this.deptId = deptId;
}
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;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", deptId=" + deptId +
'}';
}
}
public class Dept {
private Integer deptId;
private String deptName;
public Dept(Integer deptId, String deptName) {
this.deptId = deptId;
this.deptName = deptName;
}
public Dept() {
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Dept{" +
"deptId=" + deptId +
", deptName='" + deptName + '\'' +
'}';
}
}
4、jdbc.properties 文件
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC
jdbc.username=root
jdbc.password=root
5、MyBaits 核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
MyBatis核心配置文件中,标签的顺序:
properties?,settings?,typeAliases?,typeHandlers?, objectFactory?,objectWrapperFactory?,
reflectorFactory?, plugins?,environments?,databaseIdProvider?,mappers?
-->
<!-- 引入 properties 文件 ,此后可以在当前文件中使用的方式访问value-->
<properties resource="jdbc.properties"/>
<typeAliases>
<package name="com.chenyixin.ssm.pojo"/>
</typeAliases>
<!--设置连接数据库的环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<!--<mapper resource=""/>-->
<package name="com.chenyixin.ssm.mapper"/>
</mappers>
</configuration>
6、工具类:
package com.chenyin.ssm.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.InputStream;
public class SqlSessionUtil {
public static SqlSession getSqlSession() {
try {
// 获取核心的配置文件
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 创建 SqlSessionFactoryBuilder 对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 通过核心配置文件多对应的字节输入流创建工厂类 SqlSessionFactory ,生产 SqlSession 对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
// 创建 SqlSession 对象(自动操作事务)
return sqlSessionFactory.openSession(true);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
7、log4j.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
1、if 标签
if标签可通过 test 属性的表达式进行判断,若表达式的结果为 true,则标签中的内容会执行;反之 标签中的内容不会执行
DynamicMapper.xml 配置文件:
<!--List<Emp> getEmpByCondition(Emp emp);-->
<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>
测试类:
public class DynamicMapperTest {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
@Test
public void getEmpByCondition() {
Emp emp1 = new Emp(null,"张三",20,"女");
List<Emp> empList1 = mapper.getEmpByCondition(emp1);
empList1.forEach(System.out::println);
// Emp{empId=1, empName='张三', age=20, gender='女', dept=null}
Emp emp2 = new Emp(null,null,null,"男");
List<Emp> empList2 = mapper.getEmpByCondition(emp2);
empList2.forEach(System.out::println);
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=4, empName='赵六', age=23, gender='男', dept=null}
}
}
2、where 标签
where和if一般结合使用:a> 若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字b> 若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的 and去掉注意:where标签不能去掉条件最后多余的and
DynamicMapper.xml 配置文件:
<!--List<Emp> getEmpByCondition2(Emp emp);-->
<select id="getEmpByCondition2" 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 getEmpByCondition2() {
Emp emp1 = new Emp(null,"张三",20,"女");
List<Emp> empList1 = mapper.getEmpByCondition2(emp1);
System.out.println(empList1);
// [Emp{empId=1, empName='张三', age=20, gender='女', dept=null}]
Emp emp2 = new Emp(null,"",null,"女");
List<Emp> empList2 = mapper.getEmpByCondition2(emp2);
empList2.forEach(System.out::println);
// Emp{empId=1, empName='张三', age=20, gender='女', dept=null}
// Emp{empId=2, empName='李四', age=22, gender='女', dept=null}
// Emp{empId=5, empName='田七', age=21, gender='女', dept=null}
Emp emp3 = new Emp(null,"",null,"");
List<Emp> empList3 = mapper.getEmpByCondition2(emp3);
empList3.forEach(System.out::println);
// Emp{empId=1, empName='张三', age=20, gender='女', dept=null}
// Emp{empId=2, empName='李四', age=22, gender='女', dept=null}
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=4, empName='赵六', age=23, gender='男', dept=null}
// Emp{empId=5, empName='田七', age=21, gender='女', dept=null}
}
3、trim 标签
trim用于去掉或添加标签中的内容常用属性:prefix :在trim标签中的内容的前面添加某些内容prefixOverrides :在trim标签中的内容的前面去掉某些内容suffix :在trim标签中的内容的后面添加某些内容suffixOverrides :在trim标签中的内容的后面去掉某些内容
DynamicMapper.xml 配置文件:
<!--List<Emp> getEmpByCondition3(Emp emp);-->
<select id="getEmpByCondition3" resultType="emp">
select * from t_emp
<trim prefix="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>
</select>
测试:
@Test
public void getEmpByCondition3() {
Emp emp1 = new Emp(null,"张三",20,"女");
List<Emp> empList1 = mapper.getEmpByCondition3(emp1);
System.out.println(empList1);
// Emp{empId=1, empName='张三', age=20, gender='女', dept=null}
Emp emp2 = new Emp(null,"",null,"男");
List<Emp> empList2 = mapper.getEmpByCondition3(emp2);
empList2.forEach(System.out::println);
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=4, empName='赵六', age=23, gender='男', dept=null}
Emp emp3 = new Emp(null,"",null,"");
List<Emp> empList3 = mapper.getEmpByCondition3(emp3);
empList3.forEach(System.out::println);
// Emp{empId=1, empName='张三', age=20, gender='女', dept=null}
// Emp{empId=2, empName='李四', age=22, gender='女', dept=null}
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=4, empName='赵六', age=23, gender='男', dept=null}
// Emp{empId=5, empName='田七', age=21, gender='女', dept=null}
}
4、choose、when、otherwise
choose、when、otherwise 组合标签
相当于 java 中的 if...else if ...else
when 至少设置一个, otherwise 至多设置一个
DynamicMapper.xml 配置文件:
<!--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>
测试:
@Test
public void getEmpByChoose() {
Emp emp1 = new Emp(null,"李四",20,"女");
List<Emp> empList1 = mapper.getEmpByChoose(emp1);
empList1.forEach(System.out::println);
// [Emp{empId=2, empName='李四', age=22, gender='女', dept=null}]
Emp emp2 = new Emp(null,"",21,"男");
List<Emp> empList2 = mapper.getEmpByChoose(emp2);
empList2.forEach(System.out::println);
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=5, empName='田七', age=21, gender='女', dept=null}
Emp emp3 = new Emp(null,"",null,"男");
List<Emp> empList3 = mapper.getEmpByChoose(emp3);
empList3.forEach(System.out::println);
// Emp{empId=3, empName='王五', age=21, gender='男', dept=null}
// Emp{empId=4, empName='赵六', age=23, gender='男', dept=null}
}
5、foreach 标签
foreach 标签
属性
collection:设置要循环的数组或集合
item:用一个字符串表示数组或集合中的每一个数据
separator:设置每次循环的数据之间的分隔符(所设置的分割符默认左右都有一个空格)
open:循环的所有内容以什么开始
close:循环的所有内容以什么结束
index:循环的索引
5.1、 使用 foreach 标签 实现批量添加
DynamicMapper.xml 配置文件:
<!--int insertMoreEmp(@Param("emps") List<Emp> emps);-->
<insert id="insertMoreEmp" >
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
测试:
@Test
public void insertMoreEmp() {
Emp emp1 = new Emp(null, "小明", 20, "男");
Emp emp2 = new Emp(null, "小芳", 19, "女");
Emp emp3 = new Emp(null, "李华", 22, "女");
Emp emp4 = new Emp(null, "小华", 20, "女");
Emp emp5 = new Emp(null, "小王", 21, "男");
Emp emp6 = new Emp(null, "老王", 32, "男");
Emp emp7 = new Emp(null, "老华", 33, "男");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3,emp4,emp5,emp6,emp7);
int i = mapper.insertMoreEmp(emps);
System.out.println(i); // 7
}
5.2、使用 foreach 标签 实现批量删除
① 方式1
DynamicMapper.xml 配置文件:
<!--int deleteMoreEmp1(@Param("empIds") Integer[] empIds);-->
<delete id="deleteMoreEmp1">
delete from t_emp where emp_id in
(
<foreach collection="empIds" item="empId" separator=",">
#{empId}
</foreach>
);
</delete>
测试:
@Test
public void deleteMoreEmp1() {
Integer[] empIds = new Integer[]{7,9};
int i = mapper.deleteMoreEmp1(empIds);
System.out.println(i); // 2
}
结果:
② 方式2
DynamicMapper.xml 配置文件:
<!--int deleteMoreEmp2(@Param("empIds") Integer[] empIds);-->
<delete id="deleteMoreEmp2">
delete from t_emp where emp_id in
<foreach collection="empIds" item="empId" separator="," open="(" close=")">
#{empId}
</foreach>
</delete>
测试:
@Test
public void deleteMoreEmp2() {
Integer[] empIds = new Integer[]{6,10,11};
int i = mapper.deleteMoreEmp2(empIds);
System.out.println(i); // 3
}
结果:
③ 方式3
DynamicMapper.xml 配置文件:
<!--int deleteMoreEmp3(@Param("empIds") Integer[] empIds);-->
<delete id="deleteMoreEmp3">
delete from t_emp where
<foreach collection="empIds" item="empId" separator="or">
emp_id = #{empId}
</foreach>
</delete>
测试:
@Test
public void deleteMoreEmp3() {
Integer[] empIds = new Integer[]{8,12};
int i = mapper.deleteMoreEmp3(empIds);
System.out.println(i); // 2
}
结果:
6、sql 标签
sql 标签
可以记录一段 sql ,在需要的地方使用 include 标签进行引用
DynamicMapper.xml 配置文件:
<!--List<Emp> selectAll();-->
<sql id="empColumns">
emp_id,emp_name,age,gender
</sql>
<select id="selectAll" resultType="emp">
select <include refid="empColumns"/> from t_emp;
</select>
测试:
@Test
public void selectAll() {
List<Emp> emps = mapper.selectAll();
emps.forEach(System.out::println);
}
结果: