动态SQL
动态SQL中的元素
动态SQL是MyBatis的强大特性之一,基于对象导航图语言(Object Graph Navigation, OGNL)的表达式来完成动态SQL,在很大程度上避免了单一SQL语句的反复堆砌,提高了SQL语句的复用性。常用的动态SQL元素如下:
元素 | 说明 |
---|---|
<if> | 判断语句,用于条件判断 |
<choose>(<when>、<otherwise>) | 相当于Java语句中的switch…case…default语句,用于多条件判断 |
<where> | 简化SQL语句中where的条件判断 |
<trim> | 可以灵活的去除多余的关键字 |
<set> | 用于SQL语句的动态更新 |
<foreach> | 循环语句,常用于in语句等列举条件中 |
条件查询操作
<if>元素
<if>
元素是最常用的判断元素,它类似于Java中的if
语句,主要用于实现某些简单的条件判断。
例如,现在我们要查找某个客户的信息,可以通过不写年龄通过姓名查找用户,也可以通过只写年龄不写姓名查找客户,还可以什么都不写查出所有客户,此时姓名和年龄就是非必须条件,实现步骤如下:
- 数据库准备
在名称为mybatis
的数据库中,创建一个t_customer
数据表,并插入几条数据,代码如下:
# 创建一个t_customer表,并插入几条测试数据
CREATE TABLE t_customer(
id INT(32) PRIMARY KEY AUTO_INCREMENT ,
username VARCHAR(50) ,
jobs VARCHAR(50) ,
phone VARCHAR(16)
);
# 插入3条数据
INSERT INTO mybatis.t_customer VALUES ('1', 'joy', 'teacher', '13733333333'),
('2', 'jack', 'teacher', '13522222222'),
('3', 'tom', 'worker', '15111111111')
- POJO类准备
在com.itheima.pojo
包下创建持久化类Customer
,在类中声明数据库中所有属性以及每个属性对应的getter/setter
方法、toString()
方法。代码如下:
package com.itheima.pojo;
/**
* 客户持久化类
* @author Zhang
*/
public class Customer {
// 客户id
private Integer 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 Integer getId() {
return id;
}
public void setId(Integer 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;
}
}
Customer
类与普通JavaBean
没什么区别,只不过属性与数据库表字段对应。实际上,Customer
就是一个POJO
(普通Java对象),MyBatis
就是采用POJO
作为持久化类完成对数据库的操作的。
- 创建映射文件
在项目com.itheima.mapper
包下创建映射文件CustomerMapper.xml
,在映射文件中,根据客户姓名和年龄组合条件查询客户信息,使用<if>
元素编写组合条件的动态SQL。具体代码如下:
<?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.itheima.mapper.CustomerMapper">
<!-- <if>元素的使用 -->
<select id="findCustomerByNameAndJobs" parameterType="com.itheima.pojo.Customer" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
WHERE 1 = 1
<if test="jobs !=null and username !=''">
AND username LIKE CONCAT('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
AND jobs = #{jobs}
</if>
</select>
</mapper>
12行-17行的代码使用\<if>
元素的test
属性进行真假判断,大部分开发中都用于进行非空判断,但也会有用于判断字符串、数字和枚举等场景使用。其中,CONCAT
是一个 SQL 函数,用于连接两个或多个字符串。它可以将多个字符串合并成一个字符串,通常用于构建更复杂的字符串输出。
- 修改核心配置文件
在配置文件mybatis-config.xml
中,引入CustomerMapper.xml
映射文件,将CustomerMapper.xml
映射文件加载到程序中。在mybatis-config.xml
中的\<mapper>
元素下添加代码如下:
<mapper resource="com/itheima/mapper/CustomerMapper.xml"/>
- 创建获取
SqlSession
对象的工具类
就是之前在项目src/main/java
目录下com.itheima.utils
包下创建的MyBatisUtils.java
类。
- 修改测试类
在测试类MyBatisTest.java
类中,添加一个单元测试,方法名为findCustomerByNameAndJobsTest()
,该方法用于根据看客户姓名和职业组合条件查询客户信息列表,添加如下代码:
@Test
public void findCustomerByNameAndJobsTest(){
// 通过工具类获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setUsername("jack");
customer.setJobs("teacher");
// 执行SqlSession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
// 输出查询结果信息
for (Customer customer2 : customers) {
// 打印输出结果
System.out.println(customer2);
}
// 关闭SqlSession
session.close();
}
这段代码是一个JUnit测试方法,用于测试通过MyBatis根据用户名和职业(name
和 jobs
)查询客户信息的功能。代码的详细解释如下:
SqlSession session = MyBatisUtils.getSession();
通过MyBatisUtils
类获取一个SqlSession
对象,它用于执行数据库操作(如查询、更新、插入等)。SqlSession
是MyBatis提供的与数据库交互的核心对象。Customer customer = new Customer();
创建一个Customer
对象,用于封装查询条件。customer.setUsername("jack");
和customer.setJobs("teacher");
设置Customer
对象的用户名为“jack”,职业为“teacher”,这是查询的条件,表示想要查询名字为“jack”且职业为“teacher”的客户。List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
通过session
对象执行MyBatis的查询方法selectList
。"com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs"
是指定的SQL映射语句的唯一标识符,指向MyBatis的XML配置文件中定义的findCustomerByNameAndJobs
查询。customer
是查询条件,会被传递到SQL语句中作为输入参数。- 结果返回一个
Customer
对象的List
集合,即满足条件的客户记录。
for (Customer customer2 : customers)
遍历查询结果列表,逐一输出查询到的每一个Customer
对象的信息。System.out.println(customer2);
打印每个查询到的Customer
对象的信息,展示查询结果。session.close();
关闭SqlSession
对象,释放数据库连接资源。
<choose>、<when>、<otherwise>元素
Mybatis提供了<choose>、<when>、<otherwise>
元素进行处理,这三个元素往往在一起使用,类似于Java中的switch...case...defalut...
,一般在如下情况下使用:
-
当客户名称不为空,则指根据客户名称进行客户筛选;
-
当客户名称为空,而客户职业不为空,则指根据客户职业进行客户筛选;
-
当客户名称和客户职业都为空,则要求查询所有电话不为空的客户信息。
示例代码如下:
- 在映射文件
CustomerMapper.xml
中,执行上述情况的动态SQL,如下:
<?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.itheima.mapper.CustomerMapper">
<!-- <if>元素的使用 -->
<select id="findCustomerByNameAndJobs" parameterType="com.itheima.pojo.Customer" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
WHERE 1 = 1
# 条件判断
<choose>
<when test="username !=null and username !=''">
AND username LIKE CONCAT('%', #{username}, '%')
</when>
<when test="jobs !=null and jobs !=''">
AND jobs = #{jobs}
</when>
<otherwise>
AND phone IS NOT NULL
</otherwise>
</choose>
</select>
</mapper>
上述代码中,<choose>
元素内部的<when>
元素由上至下依次判断,例如第一个<when>
元素块中条件成立,则只有其其中的内容与上面的SQL语句拼接,下面两个块都不执行;同样的,如果所有<when>
全部判断为false
,则将<otherwide>
中的内容拼接。
- 在测试类
MyBatisTest.java
中,编写方法findCustomerByNameOrJobsTest()
方法,用于根据客户或职业查询客户信息列表,如下:
/*
根据客户姓名或职业查询客户信息列表
*/
@Test
public void findCustomerByNameOrJobsTest() {
// 通过工具类获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setUsername("Tom");
customer.setJobs("teacher");
// 执行SqlSession的查询方法,并返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
// 输出查询结果信息
for (Customer customer2 : customers) {
// 打印输出结果
System.out.println(customer2);
}
// 关闭SqlSession
session.close();
}
- 再尝试把
CustomerMapper.xml
中第一个<when>
的代码注释,测试,再注释第二个,再试,两个一起注释,再试一次,观察结果。
<where>元素
刚才上述的SQL语句中,WHERE
语句后面写了“1 = 1
”,如果我们把它删掉,拼接时就会产生问题,WHERE
后面紧接着就是AND
,会在运行时报SQL语法错误。
我们可以使用<where>
元素来解决这个问题,<where>
元素会自动判断由组合条件拼装的SQL语句,只有<where>
元素内的某一个或多个条件成立时,才会在拼接SQL中加入关键字<where>
,否则将不会加入;即使WHERE
语句之后的内容出现了多余的AND
、OR
等,<where>
元素也会将他们自动去除,如下代码:
- 使用
<where>
元素替换"WHERE 1 = 1
"条件,再次执行测试类findCustomerByNameAndJobsTest()
测试方法:
<?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.itheima.mapper.CustomerMapper">
<select id="findCustomerByNameAndJobs" parameterType="com.itheima.pojo.Customer" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
<where>
<if test="username !=null and username !=''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
AND jobs = #{jobs}
</if>
</where>
</select>
</mapper>
<trim>元素
<trim>
元素用于删除多余的关键字,他可以直接实现<where>
元素的功能。
<trim>
包含4个属性,如下表:
属性 | 说明 |
---|---|
prefix | 指定给SQL语句增加前缀 |
prifixOverrides | 指定SQL语句重要去掉的前缀字符串 |
suffix | 指定给SQL语句增加的后缀 |
suffixOverrides | 指定SQL语句重要去掉的后缀字符串 |
再次改造代码如下:
- 在映射文件
CustomerMapper.xml
中添加使用<set>
元素执行更新操作的动态SQL,代码如下:
<?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.itheima.mapper.CustomerMapper">
<select id="findCustomerByNameAndJobs" parameterType="com.itheima.pojo.Customer" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
<trim prefix="WHERE" prefixOverrides="AND">
<if test="username !=null and username !=''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
AND jobs = #{jobs}
</if>
</trim>
</select>
</mapper>
- 在测试类编写测试方法
updateCustomerBySetTest()
,代码如下:
/*
更新客户信息
*/
@Test
public void updateCustomerBySetTest() {
// 获取SqlSession
SqlSession sqlSession = MyBatisUtils.getSession();
// 创建Customer对象,并向对象中添加数据
Customer customer = new Customer();
customer.setId(3);
customer.setPhone("13311111234");
// 执行SqlSession的更新方法,返回的是SQL语句影响的行数
int rows = sqlSession.update("com.itheima.mapper.CustomerMapper.updateCustomerBySet", customer);
// 通过返回结果判断更新操作是否执行成功
if (rows > 0) {
System.out.println("您成功修改了" + rows + "条数据!");
} else {
System.out.println("执行修改操作失败!");
}
// 提交事务
sqlSession.commit();
// 关闭SqlSession
sqlSession.close();
}
使用<trim>元素实现更新操作
- 在映射文件
CustomerMapper.xml
中添加使用<trim>
元素执行更新操作的动态SQL,代码如下:
<!-- <trim>元素-->
<update id="updateCustomerByTrim" parameterType="com.itheima.pojo.Customer">
UPDATE mybatis.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>
- 在测试类编写测试方法
updateCustomerByTrimTest()
,代码如下:
/*
使用<trim>元素更新客户信息
*/
@Test
public void updateCustomerByTrimTest() {
// 获取SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSession();
// 创建Customer对象,并向对象中添加数据
Customer customer = new Customer();
customer.setId(3);
customer.setPhone("13311111111");
// 执行SqlSession的更新方法,返回的试SQL语句影响的行数
int rows = sqlSession.update("com.itheima.mapper.CustomerMapper.updateCustomerByTrim", customer);
// 通过结果返回判断更新操作是否成功
if (rows > 0) {
System.out.println("您成功修改了" + rows + "条数据!");
} else {
System.out.println("执行修改操作失败!");
}
// 提交事务
sqlSession.commit();
// 关闭SqlSession
sqlSession.close();
}
复杂查询操作
<foreach>元素
<foreach>
元素用于遍历,能够支持数组
、List
、Set
接口的集合。
属性 | 说明 |
---|---|
item | 集合中每一个元素的名字,有别名则是别名,必选属性 |
index | 在List 数组中,index 是元素的序号;在 Map 中,index 是元素的key ; |
open | 表示<foreach> 语句代码开始,一般和close 合用 |
separator | 元素之间的分隔符,就例如separator="," ,会自动在元素间用"," 分隔,避免由于自行输入导致SQL错误 |
close | 表示<foreach> 语句代码关闭,一般和open 合用 |
collection | 用于指定遍历参数的类型,必须指定,使用规则如下: 1. 若传入参数为 1 个,参数类型为List ,collection 值为list 的中其参数名;2. 若传入参数为 1 个,参数类型是数组 ,collection 值为array 中其参数名;3. 若传入参数为 n 个,需要把参数封装为Map ,collection 值为Map 中对应的某个键。 |
<foreach>迭代数组
如果要查询id
为2、3的所有数据,需要使用<foreach>
进行遍历,实现批量查询,具体实现如下:
- 在映射文件
CustomerMapper.xml
中,添加如下代码:
<!-- <foreach>元素-->
<select id="findByArray" parameterType="java.util.Arrays" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
WHERE id IN
<foreach collection="array" item="id" index="index" open="(" separator="," close=")">
#{id}
</foreach>
</select>
- 在测试类
MyBatisTest.java
中编写测试方法findByArrayTest()
,代码如下:
/*
根据客户id批量查询客户信息
*/
@Test
public void findByArrayTest() {
// 获取SqlSession
SqlSession session = MyBatisUtils.getSession();
// 创建数组,封装查询id
Integer[] roleIds = {2, 3};
// 执行SqlSession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findByArray", roleIds);
// 输出查询信息
for (Customer customer : customers) {
// 打印输出结果
System.out.println(customer);
}
// 关闭SqlSession
session.close();
}
<foreach>迭代List
- 在映射文件
CustomerMapper.xml
中,添加如下代码:
<!-- <foreach>元素操作List-->
<select id="findByList" parameterType="java.util.List" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
WHERE id IN
<foreach collection="list" item="id" index="index" open="(" separator="," close=")">
#{id}
</foreach>
</select>
- 在测试类
MyBatisTest.java
中编写测试方法findByListTest()
,代码如下:
/**
* 根据客户id批量查询客户信息
*/
@Test
public void findByListTest() {
// 获取SqlSession
SqlSession session = MyBatisUtils.getSession();
// 创建List集合用来封装查询id
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
// 执行SqlSession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findByList", ids);
// 输出查询结果信息
for (Customer customer : customers) {
// 输出结果信息
System.out.println(customer);
}
// 关闭SqlSession
session.close();
}
<foreach>元素迭代Map
- 在映射文件
CustomerMapper.xml
中,添加如下代码:
<!-- <foreach>元素操作Map -->
<select id="findByMap" parameterType="java.util.Map" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
WHERE jobs=#{jobs} AND id IN
<foreach collection="id" index="index" item="roleMap" open="(" separator="," close= ")">
#{roleMap}
</foreach>
</select>
- 在测试类
MyBatisTest.java
中编写测试方法findByMapTest()
,代码如下:
/**
* 根据客户id批量查询客户信息
*/
@Test
public void findByMapTest() {
// 获取SqlSession
SqlSession session = MyBatisUtils.getSession();
// 创建List集合,封装查询id
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
Map<String, Object> conditionMap = new HashMap<>();
conditionMap.put("id", ids);
conditionMap.put("jobs", "teacher");
// 执行SqlSession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findByMap", conditionMap);
// 输出查询结果信息
for (Customer customer : customers) {
// 输出结果信息
System.out.println(customer);
}
// 关闭SqlSession
session.close();
}
案例:用户信息查询系统
- 多条件查询。当用户输入的姓名不为空时,则只根据姓名进行信息的查询。当用户输入的姓名为空而职业不为空时,则只根据职业进行信息的查询。当用户输入的姓名和职业都为空,则要求查询出所有id不为空的用户信息。
- 单条件查询出所有id值小于5的用户的信息。
代码实现
- 在映射文件
CustomerMapper.xml
中,添加如下代码:
<!-- 用户信息查询系统-->
<select id="findByNameAndJobs" parameterType="java.util.Map" resultType="com.itheima.pojo.Customer">
SELECT id, username, jobs, phone
FROM mybatis.t_customer
<where>
<!-- 当姓名不为空时,查询条件包含姓名 -->
<if test="username != null and username != ''">
username = #{username}
</if>
<!-- 当姓名为空且职业不为空时,查询条件包含职业 -->
<if test="jobs != null and jobs != ''">
AND jobs = #{jobs}
</if>
<!-- 当姓名和职业都为空时,查询所有 id 不为空的用户 -->
<if test="(username == null or username == '') and (jobs == null or jobs == '')">
AND id IS NOT NULL
</if>
</where>
</select>
- 在测试类
MyBatisTest.java
中编写测试方法findByNameAndJobsTest()
,代码如下:
/**
* 案例
*/
@Test
public void findByNameAndJobsTest() {
// 获取SqlSession
SqlSession session = MyBatisUtils.getSession();
// 创建Map集合,封装查询条件
Map<String, Object> conditionMap = new HashMap<>();
conditionMap.put("username", "joy"); // 可根据用户输入赋值
conditionMap.put("jobs", "teacher"); // 可根据用户输入赋值
// 执行查询,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findByNameAndJobs", conditionMap);
// 输出查询结果
for (Customer customer : customers) {
System.out.println(customer);
}
// 关闭SqlSession
session.close();
}
本章小结
本章主要讲解了动态 SQL的相关知识。首先讲解了动态 SQL中的元素;其次讲解了条件查询操作,包括<if>元素、<choose>元素、<when>元素、<otherwise>元素、<where>元素和<trim>元素的使用;然后讲解了更新操;最后讲解了复杂查询操作。通过学习本章的内容,读者可以了解常用动态SQL元素的主要作用并能掌握这些元素在实际开发中的应用方法。在 MyBats 框架中,这些动态 SQL元素十分重要,熟练掌握它们能够极大地提高开发效率。
常考面试题
- 请简述MyBatis动态SQL中的常用元素及其作用?
在 MyBatis 动态 SQL 中,常用的几个元素及作用可以简单说成这样:
<if>
:这个是用来根据条件去拼接 SQL 的,类似 Java 里的if
判断,常用于判断某个条件是否加到查询中。<where>
:它会自动加上WHERE
关键字,并且去掉多余的AND
或OR
,适合用在多个条件查询时,防止 SQL 拼接错误。<choose> / <when> / <otherwise>
:类似于switch-case
,让你在多个条件中选择一个执行,如果都不符合,执行otherwise
的部分。<foreach>
:这个用来遍历集合,比如你要做IN
查询或者批量插入时,用它生成一系列的值。<set>
:专门用在UPDATE
语句里,帮你自动去掉最后多余的逗号,防止 SQL 语法错误。
这些标签都是为了让 SQL 更加灵活,能根据传入的参数动态生成对应的 SQL 语句。
- 请简述在使用<foreach>时,collection属性需要注意的几点?
在使用 <foreach>
时,collection
属性有几个需要特别注意的点:
- 变量名必须一致:
collection
的值必须和传入的集合、数组或 Map 中的键名保持一致。如果你传递的是List
,比如ids
,那么collection="ids"
,不能随意命名为其他名字。 - 支持的类型:
collection
可以是List
、数组、Set
等集合类型,也可以是Map
。如果是Map
,你需要传递Map
中某个键对应的值作为集合,比如collection="mapKey"
。 - 默认值:如果你直接传递的是集合,而没有使用
Map
或其他包装类,collection
的默认值是list
或array
。所以,你可以用collection="list"
或collection="array"
来引用它。 - 注意索引:
index
属性表示当前遍历项的索引,而item
属性是遍历到的每一个元素。在复杂情况下,collection
与item
、index
需要配合使用,确保 SQL 生成正确。