昔洛 的个人博客

Bug不空,誓不成佛

第八篇 动态 SQL
/      

第八篇 动态 SQL

一、动态SQL中的元素

    动态 SQL 是 MyBatis 的强大特性之一,MyBatis 3 采用了功能强大的基于 OGNL 的表达式来完成动态SQL,它消除了之前版本中需要了解的大多元素,使用不到原来一半的元素就能完成所需工作。
MyBatis 动态 SQL 中的主要元素如下表所示:

元素 说明
<if> 判断语句,用于但条件分支判断
<choose>(<when>、<otherwise>) 相当于 Java 中的 switch…case…default语句,应用于多条件分支判断
<where>、<trim>、<set> 辅助元素,用于处理一些 SQL 拼装、特殊字符问题
<foreach> 循环语句,常用语 in 语句等列举条件中
<bind> 从 OGNL 表达式中创建一个变量,并将其绑定到上下文,常用于模糊查询 的sql中

二、<if>元素

    在 MyBatis 中,<if>元素是最常用的第判断语句,它类似于 Java 中的 if 语句,主要用于实现某些简单的条件选择。
    在实际应用中,可以通过多个条件来精确地查询某个数据。例如,要查找某个客户的信息,可以通过姓名和职业来查找客户,也可以不填写职业直接通过姓名来查找客户,还可以都不填写而查询出所有客户,此时姓名和职业就是非必要条件。类似于这种情况,在 MyBatis 中就可以通过<if>元素来实现。下面通过一个示例演示这种情况下<if>元素的使用,步骤如下:
(1)在 Eclipse 中,创建一个新的 Web 工程项目,将第6篇 MyBatis 入门程序中的Jar 包和文件复制到本次项目中,并将配置文件中的数据库信息修改为外部引入的形式,同时创建一个 com.cn.utils 包,在该包下引入第七篇编写的工具类 MybatisUtils,这样就完成了项目的创建和基本配置,结构目录如下:
image.png
(2)修改映射文件 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"> 
<!-- namespace 表示命名空间 -->
<mapper namespace="com.cn.mapper.CustomerMapper">
	<!-- <if>元素使用 -->
	<select id="findCustomerByNameAndJobs"
				parameterType="com.cn.po.Customer"
				resultType="com.cn.po.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>

上述代码中,使用<if>元素的 test 属性分别对 username 和 jobs 进行了非空判断(test 属性多用于条件判断语句中,用于判断真假,大部分的场景中都是进行非空判断,有时候也需要判断字符串、数字和枚举等),如果传入的查询条件非空就进行动态 SQL 组装。
(3)在测试类 MybatisTest 中,编写测试方法 findCustomerByNameAndJobsTest(),如下所示:

package com.cn.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.cn.po.Customer;
import com.cn.utils.MybatisUtils;
public class MybatisTest {
	/*
	 * 根据客户姓名和职业组合条件查询客户信息列表
	 */
	@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.cn.mapper"+".CustomerMapper.findCustomerByNameAndJobs",customer);
		//输出查询结果信息
		for (Customer customer2 : customers) {
			//打印输出结果
			System.out.println(customer2);
		}
		//关闭 SqlSession
		session.close();
	}
}

上述代码中,findCustomerByNameAndJobsTest()方法中,首先通过 MybatisUtils 工具类获取了 SqlSession 对象,然后使用 Customer 对象封装了用户名为 jack 且职业为 teacher 的查询条件,这里使用了输出语句输出查询结果信息。最后程序执行完毕时,关闭了 SqlSession 对象。结果如下图:
image.png
从上图可以看出,已经查询出了 username 为 jack,并且 jobs 为 teacher 的客户信息。如果将封装到 Customer 对象中的 jack 和 teacher 两行代码注释,然后再次运行结果输出如下:
image.png
可以看出当未传递任何参数时,程序会将数据表中的所有数据查出。

三、<choose>、<when>、<otherwise>元素

    在使用<if>元素时,只要 test 属性中的表达式为 true,就会执行元素中的条件语句,但是在实际应用中,有时只需要从多个选项中选择一个去执行。
例如下面的场景:

“当客户名称不为空,则只根据客户名称进行客户筛选;
当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选;
当客户名称和客户职业都为空,则要求查询出所有电话不为空的客户信息。”

此种情况下,使用<if>元素进行处理是非常不合适的。如果使用的是 Java 语言,这种情况显然更合适使用 switch…case…default 语句来处理。针对上面这种情况,MyBatis 中可以使用<choose>、<when>、<otherwise>元素组合实现上面的情况。
(1)在映射文件 CustomerMapper.xml 中,使用<choose>、<when>、<otherwise>元素执行上述情况的动态 SQL 代码:

<!-- <choose>(<when>、<otherwise>)元素使用 -->
<select id="findCustomerByNameOrJobs"
	parameterType="com.cn.po.Customer"
	resultType="com.cn.po.Customer">
	select * from 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>

在上述代码中,使用了<choose>元素进行SQL拼接,当地一个<when>元素中的条件为真,则只动态组装第一个<when>元素内的 SQL 片段,否则就继续向下判断第二个<when>元素中的条件是否为真,以此类推。当前面所有 when 元素中的条件都不为真时,则只组装<otherwise>元素内的SQL片段。
(2)在测试类 MybatisTest 中,编写测试方法 findCustomerBynameOrJobsTest(),其代码如下:

/*
 *  根据客户姓名或职业查询客户信息列表
 */
@Test
public void findCustomerByNameOrJobsTest() {
	//通过工具类生成 SqlSession 对象
	SqlSession session = MybatisUtils.getSession();
	//创建 Customer 对象,封装需要组合查询的条件
	Customer customer = new Customer();
	customer.setUsername("jack");
	customer.setJobs("teacher");
	//执行 SqlSession 的查询方法,返回结果集
	List<Customer> customers = session.selectList("com.cn.mapper"+".CustomerMapper.findCustomerByNameOrJobs",customer);
	//输出查询结果信息
	for (Customer customer2 : customers) {
		//打印输出结果
		System.out.println(customer2);
	}
	//关闭 SqlSession
	session.close();
}

运运行结果如下:
image.png
从上图可以看出,虽然同是传入了姓名和职业两个查询条件,但 MyBatis 所生成的 SQL 只是动态组装了客户姓名进行条件查询。
如果将上述代码中 "customer.setUsername(“jack”);"删除或者注释掉,然后再次执行该测试方法后,结果如下:
image.png
上图可以看出,MyBatis 生成的 SQL 组装了客户职业进行条件查询,同样查询了客户信息。如果将设置客户姓名和职业参数值的的两行代码都注释(即客户姓名和职业都为空),那么程序的执行结果如下图所示:
image.png
由图可知,当姓名和职业参数都为空时,MyBatis 的 SQL 组装了 <otherwise>元素中的 SQL 片段进行条件查询。

四、<where>、<trim>元素

在前两小节中,映射文件中编写的 SQL 后面都加入了 “where 1=1”的条件,如果将 where 后“1=1”的条件去掉,那么 MyBatis 所拼接出来的 SQL 将会去下所示:

select * from t_customer where and username like concat('%',?,'%')

上面 SQL 中,where 后直接跟的是 and,这在运行时肯定会报 SQL 语法错误,而加入了条件“1=1”后,既保证了 where 后面条件成立,又避免了 where 后面第一个词是 and 或者 or 之类的关键词。针对这种情况,MyBatis 提供了<where>元素来处理这样的问题。
将映射文件中的"where 1=1"条件删除,并使用<where>元素替换后的代码如下所示:

<!-- <where>元素 -->
<select id="findCustomerByNameAndJobs"
	parameterType="com.cn.po.Customer"
	resultType="com.cn.po.Customer">
   select * from 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>

上述配置代码中,使用<where>元素对"where 1=1" 条件进行了替换,<where>元素会自动判断组合条件下拼装的SQL语句,只有<where>元素内的条件成立时,才会在拼接SQL中加入where关键字,否则将不会添加;即使 where 之后的内容有多余的 “AND” 或 “OR”,<where>元素也会自动将他们去除。
除了使用<where>元素外,还可以通过<trim>元素来定制需要的功能,上述代码还可以修改为如下:

<!-- <trim>元素 -->
<select id="findCustomerByNameAndJobs"
	parameterType="com.cn.po.Customer"
	resultType="com.cn.po.Customer">
   select * from 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>

上述配置代码中,同样使用<trim>元素对“where 1=1”条件进行了替换,<trim>元素的作用是去除一些特殊的字符串,它的 prefix 属性代表的是语句的前缀(这里使用 where 来连接后面的SQL片段),而 prefixOverrides 属性代表的是需要去除的那些特殊字符串(这里定义了要去除 SQL 中的 and),上面的写法和使用<where>元素基本是等效的。

五、<set>元素

    在 Hibernate 中,如果想要更新某一个对象,就需要发送所有的字段给持久化对象,然而实际应用中,大多数情况下都是更新的某一个或几个字段。如果更新的每一条数据都要将其所有的属性都更新一遍,那么其执行效率是非常差的。
为解决上述情况,MyBatis 中提供了<set>元素来完成这一工作。<set>元素主要用于更新操作,其主要作用是在动态包含的 SQL 语句前输出一个 SET 关键字,并将 SQL 语句中最后一个多余的逗号去除。
以入门案例中的更新操作为例,使用<set>元素对映射文件中更新客户信息的 SQL 语句进行修改的代码如下:

<!-- <set>元素 -->
<update id="updateCustomer" parameterType="com.cn.po.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>

在上述配置的 SQL 语句中,使用了<set>和<if>元素相结合的方式来组装 update 语句。其中<set>元素会动态前置 SET 关键字,同时也会消除 SQL 语句中最后一个多余的逗号;<if>元素用于判断相应的字段是否传入值,如果传入的更新字段非空,就将此字段进行动态 SQL 组装,并更新此字段,否则此字段不执行更新。
为了验证上述配置,可以在测试类中编写测试方法 updateCustomerTest(),代码如下:

/*
 * 更新客户
 */
@Test
public void updateCustomerTest() {
	//获取 SqlSession
	SqlSession sqlSession = MybatisUtils.getSession();
	//创建 Customer 对象,并向对象中添加数据
	Customer customer = new Customer();
	customer.setId(3);
	customer.setPhone("1331112134");
	//执行 SqlSession 的更新方法,返回的是 SQL 语句影响的行数
	int rows = sqlSession.update("com.cn.mapper"+".CustomerMapper.updateCustomer",customer);
	//通过返回结果判断更新操作是否执行成功
	if(rows>0) {
		System.out.println("您成功修改了"+rows+"条数据!");
	}else {
		System.out.println("执行修改操作失败!!!");
	}
	//提交事务
	sqlSession.commit();
	//关闭 SqlSession
	sqlSession.close();
}

由上述代码可知,与入门案例中的更细男方法有所不同的是,这里只设置了其 id 和 Phone 的属性值,也就是需要修改 id 为 3 的客户电话信息。
运行结果如下所示:
image.png
注意:在映射文件中使用<set>和<if>元素组合进行update语句动态SQL组装时,如果<set>元素包含的内容都为空,则会出现SQL语法错误。所以在使用<set>元素进行字段信息更新时,要确保传入的更新字段不能都为空。

六、<foreach>元素

    在实际开发中,有事可能会遇到这样的情况:假设在一个客户表中有1000条数据,现在需要将 id 值小于100的客户信息全部查询出来,这要怎么做呢?有人会说“可以一条一条查出来”,那更多的条数怎么办呢,这显然是不可取的,有的人会想到可以在 Java 中使用循环,将查询方法放在循环语句中,然后通过条件循环的方式查询出所需要的数据。这种查询方式虽然可行,但每执行一次循环语句,都需要向数据库中发送一条查询SQL,其查询效率是非常低的。针对这种情况,MyBatis 中已经提供了一种用于数组和集合循环遍历的方式,那就是使用<foreach>元素,<foreach>元素通常在构建 IN 条件语句时使用,方式如下:

<!-- <foreach>元素使用 -->
<select id="findCustomerByIds" parameterType="List" resultType="com.cn.po.Customer">
	select * from t_customer where id in
	<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>

在上述代码中,使用了<foreach>元素对传入的集合进行遍历并进行了动态 SQL 组装。关于<foreach>元素中使用的几种属性的秒数具体如下:

  • item:配置的是循环中的当前元素。
  • index:配置的是当前元素在集合的位置下标。
  • collection:配置的 list 是传递过来的参数类型(首字母小写),他可以是一个 array、list(或 collection)、Map 集合的键、POJO 包装类中数组或集合类型的属性名等。
  • open 和 close:配置的是以什么符号将这些集合元素包装起来。
  • separator:配置的是各个元素的间隔符。

注意:你可以将任何可迭代对象(如列表、集合等)和任何的字典或者数组对象传递给<foreach>作为集合参数。当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。当时用字典(或者 Map.Entry 对象的集合)时,index 是键,item 是值。

为验证上述配置,可以在测试类 MybatisTest 中,编写测试方法 findCustomerByIdsTest(),代码如下:

/*
 * 根据客户编号批量查询客户信息
 */
@Test
public void findCustomerByIdsType() {
	//获取 SqlSession
	SqlSession sqlSession = MybatisUtils.getSession();
	//创建 List 集合,封装查询 id
	List<Integer> ids = new ArrayList<Integer>();
	ids.add(1);
	ids.add(2);
	//执行 SqlSession 的查询方法,返回结果集
	List<Customer> customers = sqlSession.selectList("com.cn.mapper"+".CustomerMapper.findCustomerByIds",ids);
	//输出查询结果信息
	for(Customer customer : customers) {
		//打印输出结果
		System.out.println(customer);
	}
	//关闭 SqlSession
	sqlSession.close();
}

在上述代码中,执行查询操作时传入了一个客户编号集合 ids。
运行结果如下:
image.png
从图可以看出,使用<foreach>元素已对传入的客户编号集合进行了动态 SQL 组装,最终成功批量查询出了对应的客户信息。

特别注意:

在使用<foreach>时最关键也是最容易出错的就是 collection 属性,该属性是必须指定的,而且在不同的情况下,该属性的值是不一样的。主要有以下3中情况:
(1)如果传入的但参数且参数类型是一个数组或者 List 的时候,collection 属性分别为 array 和 list(或 collection)。
(2)如果传入的参数是多个的时候,就需要把它们封装成一个 Map 了,当然单参数也可以封装成 Map 集合,这时候 collection 属性值就为 Map 的键。
(3)如果传入的参数就是 POJO 包装类的时候,collection 属性值就为该包装类中需要进行遍历的数组或集合的属性名。
所以在设置 collection 属性值得时候,必须按照实际情况配置,否则程序就会出现异常,例如,将上述<foreach>元素中 collection 的属性值设置为 array,则程序执行后,将出现如下图所示的错误:
image.png

七、<bind>元素

    在进行模糊查询编写 SQL 语句的时候,如果使用“${}”进行字符串拼接,则无法防止 SQL 注入问题;如果使用 concat 函数进行拼接,则只针对 MySQL 数据库有效;如果使用的是 Oracle 数据库,则要使用连接符号“||”。这样,映射文件中的 SQL 就要根据不同的情况提供不同形式的实现,这显然是比较麻烦的,且不利于项目的移植。为此,MyBatis 提供了<bind>元素来解决这一问题,我们完全不必试用数据库语言,只要使用 MyBatis 的语言即可与所需参数连接。
MyBatis 的<bind>元素可以通过 OGNL 表达式来创建一个上下文变量,方式如下:

<!-- <bind>元素的使用:根据客户名模糊查询客户信息 -->
<select id="findCustomerByName" parameterType="com.cn.po.Customer" resultType="com.cn.po.Customer">
	<!-- _parameter.getUsername()也可直接写成传入的字段属性名,即 username -->
	<bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'"/>
	select * from t_customer
	where 
	username like #{pattern_username}
</select>

上述配置代码中,使用<bind>元素定义了一个 name 为 pattern_username 的变量,<bind>元素中 value 的属性值就是拼接的查询字符串,其中 _parameter.getUsername()表示传递进来的参数(也可以直接写成对应的参数变量名,如 username)。在 SQL 语句中,直接应用<bind>元素的 name 属性值即可进行动态 SQL 组装。
为验证上述配置是否能正确执行,可在测试类 MybatisTest 中编写测试方法 findCustomerByNameTest()进行测试,代码如下:

/*
 * <bind>元素的使用:根据客户名模糊查询客户信息
 */
@Test
public void findCustomerByNameTest() {
	//获取 SqlSession
	SqlSession sqlSession = MybatisUtils.getSession();
	//创建 Customer 对象,封装查询的条件
	Customer customer = new Customer();
	customer.setUsername("j");
	//执行 SqlSession 的查询方法,返回结果集
	List<Customer> customers = sqlSession.selectList("com.cn.mapper"+".CustomerMapper.findCustomerByName",customer);
	//输出查询结果信息
	for(Customer customer2 : customers) {
		//打印输出结果
		System.out.println(customer2);
	}
	//关闭 SqlSession
	sqlSession.close();
}

运行结果如下:
image.png

他们说学习说会的第一句话都是"Hello world"?
评论
歌名 - 歌手
0:00