为什么需要使用动态sql?
在实际项目的开发中,开发人员在使用JDBC或其他持久层框架进行开发时,经常需要根据不同的条件拼接SQL语句,拼接SQL语句时还要确保不能遗漏必要的空格、标点符号等,这种编程方式给开发人员带来了非常大的不便,而MyBatis提供的SQL语句动态组装功能,恰能很好地解决这一问题。
动态SQL中的元素
<if>元素
通过一个具体的案例演示单条件判断下<if>元素的使用,案例具体实现步骤如下。
1.引入依赖
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>_20230410</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
2.数据库建表
3.根据数据库字段创建实体类Customer
package cn.hdc.pojo;
public class Customer {
private int id;
private String username;
private String jobs;
private String phone;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", username='" + username + '\'' +
", jobs='" + jobs + '\'' +
", phone='" + phone + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getJobs() {
return jobs;
}
public void setJobs(String jobs) {
this.jobs = jobs;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
4.创建CustomerMapper接口,申明要实现的方法 findCustomerByUsernameAndJobs
package cn.hdc.mapper;
import cn.hdc.pojo.Customer;
import java.util.List;
public interface CustomerMapper {
public List<Customer> findCustomerByUsernameAndJobs(Customer customer);
}
5.根据接口alt+insert快速创建CustomerMapper.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="cn.hdc.mapper.CustomerMapper">
<select id="findCustomerByUsernameAndJobs" resultType="cn.hdc.pojo.Customer">
select * from t_customer where 1 = 1
<if test="username != null and username != ''">
and username like concat('%',#{username},'%')
</if>
<if test="jobs != null and jobs != ''">
and jobs = #{jobs}
</if>
</select>
</mapper>
6.创建db.properties,配置数据库连接属性
driver.driver=com.mysql.cj.jdbc.Driver
driver.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
driver.username=root
driver.password=666666
7.配置mybatis-config.xml
<?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>
<properties resource="db.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver.driver}"/>
<property name="url" value="${driver.url}"/>
<property name="username" value="${driver.username}"/>
<property name="password" value="${driver.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="cn.hdc.mapper.CustomerMapper"></mapper>
</mappers>
</configuration>
8.根据CustomerMapper接口,alt+insert 快速创建测试类:
package cn.hdc.mapper;
import cn.hdc.pojo.Customer;
import cn.hdc.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
import static org.junit.Assert.*;
public class CustomerMapperTest {
private SqlSession session;
@Before
public void setUp() throws Exception {
session = MybatisUtils.getSession();
}
@After
public void tearDown() throws Exception {
session.close();
}
@Test
public void findCustomerByUsernameAndJobs() {
Customer customer = new Customer();
//customer.setUsername("jack");
customer.setJobs("teacher");
List<Customer> customers = session.selectList("cn.hdc.mapper.CustomerMapper.findCustomerByUsernameAndJobs", customer);
for (Customer c : customers) {
System.out.println(c);
}
}
}
运行结果:
项目结构:
<choose>、<when>、<otherwise>元素
<choose><when><otherwise>使用场景
在使用<if>元素时,只要test属性中的表达式为true,就会执行元素中的条件语句,但是在实际应用中,有时只需要从多个选项中选择一个去执行。例如下面的场景︰“当客户名称不为空,则只根据客户名称进行客户筛选;当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选。当客户名称和客户职业都为空,则要求查询出所有电话不为空的客户信息。"针对上面情况,使用<if>元素进行处理是不合适的。MyBatis提供了<choose>、<when>、<otherwise>元素进行处理,这三个元素往往组合在一起使用,作用相当于Java语言中的if...else if...else。
代码结构不变,只是在上一个例子中CustomerMapper.xml添加如下代码:
在接口中申明方法:
添加测试方法:
运行结果:
<where>、<trim>元素
<where>元素
上述代码配置中,<where>元素会自动判断由组合条件拼装的SQL语句,只有<where>元素内的某一个或多个条件成立时,才会在拼接SQL中加入where关键字,否则将不会添加;即使where之后的内容有多余的“AND”或“OR”,<where>元素也会自动将他们去除。
<trim>元素
<trim>元素用于删除多余的关键字,它可以直接实现<where>元素的功能<trim>元素包含4个属性。
上述配置代码中,<trim>元素的作用是去除一些多余的前缀字符串,它的prefix属性代表的是语句的前缀(where ),而prefixOverrides属性代表的是需要去除的前缀字符串(SQL中的“AND”或“OR”)。
更新操作
<set>元素
在Hibernate框架中,如果想要更新某一个对象,就需要发送所有的字段给持久化对象,然而在实际应用中,大多数情况下都是更新某一个或几个字段。如果更新的每一条数据都要将其所有的属性都更新一遍,那么执行效率是非常差的。为了解决更新数据的效率问题,MyBatis提供了<set>元素。<set>元素主要用于更新操作,它可以在动态SQL语句前输出一个SET关键字,并将SQL语句中最后一个多余的逗号去除。<set>元素与<if>元素结合可以只更新需要更新的字段。
代码示例:
CustomerMapper.xml中添加以下代码:
<update id="updateCustomerBySet" parameterType="cn.hdc.pojo.Customer">
update t_customer
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="jobs!=null and jobs!=''">
jobs=#{jobs},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
</set>
where id = #{id}
</update>
接口中声明此方法:
创建测试方法:
运行结果:
第二种方法使用<trim>
<update id="updateCustomerBySet" parameterType="cn.hdc.pojo.Customer">
update t_customer
<trim prefix="set" suffixOverrides=",">
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="jobs!=null and jobs!=''">
jobs=#{jobs},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
</trim>
where id = #{id}
</update>
- 在映射文件中使用<set>元素和<if>元素组合进行update语句动态SQL组装时,如果<set>元素内包含的内容都为空,则会出现SQL语法错误。因此,在使用<set>元素进行字段信息更新时,要确保传入的更新字段不能都为空。
- 除了使用<set>元素外,还可以通过<trim>元素来实现更新操作。其中,<trim>元素的prefix属性指定要添加的<trim>元素所包含内容的前缀为set ,suffixOverrides属性指定去除的<trim>元素所包含内容的后缀为逗号。
复杂查询操作
<foreach>元素的属性
<collection>属性的取值
在遍历参数时,<collection>属性的值是必须指定的。不同情况下,该属性的取值也是不一样的,主要有以下三种情况:List类型、数值类型、Map类型。
- 若入参为单参数且参数类型是一个List , collection属性值为list。
- 若入参为单参数且参数类型是一个数组,collection属性值为array。
- 若传入参数为多参数,就需要把参数封装为一个Map进行处理,collection属性值为Map。若传入参数为多参数,就需要把参数封装为一个Map进行处理,collection属性值为Map。
<foreach>元素迭代数组
例如,要从数据表t_customer中查询出id为1、2、3的客户信息,就可以利用数组作为参数,存储id的属性值1、2、3,并通过<foreach>元素迭代数组完成客户信息的批量查询操作。
CustomerMapper.xml
CustomerMapper接口:
测试方法:
运行结果:
<foreach>元素迭代List
CustomerMapper.xml
CustomerMapper接口:
创建测试方法:
运行结果:
<foreach>元素迭代Map
CustomerMapper.xml
CustomerMapper接口:
创建测试方法:
运行结果:
基于上述知识点实现一个:学生信息查询系统
功能:
多条件查询
- 当用户输入的学生姓名不为空,则只根据学生姓名进行学生信息的查询;
- 当用户输入的学生姓名为空,而学生专业不为空,则只根据学生专业进行学生的查询;
- 若学生姓名和学生专业都为空,则则要求查询出所有学号不为空的学生信息;
单条件查询
- 查询出所有id值小于5的学生的信息;
1.pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>_20230418</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
2.db.properites
driver.driver=com.mysql.cj.jdbc.Driver
driver.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
driver.username=root
driver.password=666666
3.创建数据库,建表
4. 根据数据库表字段生成实体类Student
package cn.hdc.pojo;
public class Student {
private int id;
private String name;
private String major;
private String sno;
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", major='" + major + '\'' +
", sno='" + sno + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public String getSno() {
return sno;
}
public void setSno(String sno) {
this.sno = sno;
}
}
5.创建接口,声明要实现的方法StudentMapper
package cn.hdc.mapper;
import cn.hdc.pojo.Student;
import java.util.List;
public interface StudentMapper {
public Student findStudentByNameAndMajor(Student student);
public Student findStudentByList(List<Integer> list);
}
6.根据接口创建映射文件StudentMapper.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="cn.hdc.mapper.StudentMapper">
<select id="findStudentByNameAndMajor" resultType="cn.hdc.pojo.Student">
select * from dm_student
<where>
<choose>
<when test="name != null and name != ''">
and name like concat('%',#{name},'%')
</when>
<when test="major != null and major != ''">
and major = #{major}
</when>
<otherwise>
and sno is not null
</otherwise>
</choose>
</where>
</select>
<select id="findStudentByList" resultType="cn.hdc.pojo.Student">
select *
from dm_student
where id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
</mapper>
7.配置到mybaits-config.xml,扫描xxxMapper.xml
<?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>
<!-- 环境配置 -->
<!-- 加载类路径下的属性文件 -->
<properties resource="db.properties"/>
<settings>
<!-- 全局启用或禁用延迟加载。当禁用时,所有关联对象都会即时加载。默认:true -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 当启用时,有延迟加载属性的对象在被调用时将会完全加载任意属性 . 默认:true-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- 数据库连接相关配置 ,db.properties文件中的内容-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver.driver}"/>
<property name="url" value="${driver.url}"/>
<property name="username" value="${driver.username}"/>
<property name="password" value="${driver.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="cn.hdc.mapper"/>
</mappers>
</configuration>
8.编写测试类:
package cn.hdc.mapper;
import cn.hdc.pojo.Student;
import cn.hdc.utils.MybaitsUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
public class StudentMapperTest {
private SqlSession session;
@Before
public void setUp() throws Exception {
session = MybaitsUtils.getSession();
}
@After
public void tearDown() throws Exception {
session.commit();
session.close();
}
@Test
public void findStudentByNameAndMajor() {
Student student = new Student();
student.setName("张三");
student.setMajor("英语");
List<Student> students = session.selectList("cn.hdc.mapper.StudentMapper.findStudentByNameAndMajor", student);
for (Student stu : students) {
System.out.println(stu);
}
}
@Test
public void findStudentByList() {
List<Integer> list = new ArrayList<>();
for (int i = 0; i < 5; i++) {
list.add(i);
}
List<Student> students = session.selectList("cn.hdc.mapper.StudentMapper.findStudentByList", list);
for (Student stu : students) {
System.out.println(stu);
}
}
}
9.项目目录结构: