百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

Mybaits映射器之动态SQL语句

wptr33 2024-12-01 05:03 15 浏览

前言:

今天我们一起聊聊Mybaits的映射器构建动态SQL语句来实现复杂的业务查询场景,围绕着Mybaits提供的7个用于构建动态sql的元素来进行分析。

但是,通常如果有一个比较复杂的查询场景,我们会先通过 Java 代码进行前期的处理(如参数处理),然后再去构建较为简单的 SQL 语句,而不会直接构建复杂的 SQL 语句进行查询,这也是符合当下大家较为认可的开发规范(尽可能使用简单的 SQL 语句)。

if 元素

if 元素,MyBatis 映射器中用于实现条件判断的元素。

if 元素是我们在 MyBatis 映射器中最常使用的动态 SQL 语句元素,没有之一。MyBatis 中的 if 元素与 Java 中的 if 关键字功能是一样的,用于实现条件判断,它只有一个属性 test,用于编写条件判断语句。

假设我们有如下需求,实现根据输入的用户信息来查询所有符合条件的用户,如果没有输入任何用户信息,则查询全部用户,那么我们可以这样来写 MyBatis 映射器中的 SQL 语句:

<select id="selectByRecord" parameterType="com.cw.entity.UserDO" resultType="com.cw.entity.UserDO">
	select * from user
	where 1 = 1
	<if test="user.name != null">
		and name = #{user.name, jdbcType=VARCHAR}
	</if>
	<if test="user.age != null">
		and age = #{user.age, jdbcType=INTEGER}
	</if>
	<if test="user.gender != null">
		and gender = #{user.gender, jdbcType=VARCHAR}
	</if>
	<if test="user.idType != null">
		and id_type = #{user.idType, jdbcType=INTEGER}
	</if>
	<if test="user.idNumber != null">
		and id_number = #{user.idNumber, jdbcType=VARCHAR}
	</if>
</select>

Mapper 接口中的方法:

List<UserDO> selectByRecord(@Param("user") UserDO userDO);

单元测试代码:

public void testSelectByRecord() throws IOException {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	UserDO userDO = new UserDO();
	userDO.setName("李四");
	userDO.setAge(20);
	userDO.setGender("F");
	userDO.setIdType(1);
	userDO.setIdNumber("11010119990708785X");
	
	List<UserDO> users = userMapper.selectByRecord(userDO);
	log.info("查询全部用户:{}", JSON.toJSONString(users));
	
	sqlSession.close();
}

查看控制台输出:

我们删除测试案例中的部分参数赋值,只留下为 name 字段赋值的部分,再来执行测试案例,并观察控制台输出的 SQL 语句:

可以看到,此时输出的查询语句中,条件语句中只剩下了根据 name 字段查询的部分了。

if 元素支持的运算符

在 if 元素中使用了运算符不等于(!=),除此之外我们还可以在 if 元素中使用其它的常见运算符:

注意,如果使用的运算符中含有“<”或者“&”,你需要使用它的转义符,否则在 XML 解析的过程中无法处理。例如,在上面的表格中,小于(<),小于等于(<=)和逻辑与(&&)需要使用它们的转义符。

实际上,MyBatis 支持的运算符远远不止这些,MyBatis 甚至连移位运算符(如右移运算符>>)和异或运算符(^或 xor)都可以支持。不过在日常工作中,上面表格中展示的运算符已经能够满足我们绝大部分的场景了。

where 元素

上面的案例中,我们使用了“丑陋”的条件语句:1 = 1。想必很多小伙伴都这么写过 MyBatis 的 SQL 语句吧?这也是无奈之举,因为 SQL 的语法规则中,条件语句的第一个条件不能以 and 开头,因此我们加上“1 = 1”来保证动态组装的条件不会作为条件语句的第一个条件。

虽然,MySQL 5.7 之后对“1 = 1”进行了优化,来保证不会产生性能问题,但是这么做会让代码变得非常丑陋,其次也会显得我们功力浅薄。

MyBatis 贴心的为我们准备了解决办法:where 元素,它会先根据 if 元素的处理结果生成子句,然后将子句前缀的“and”和“or”移除,最后在子句的开始处插入“where”,如果不存在子句,则不会插入“where”。

我们将上面的案例改写为使用 where 元素的方式,如下:

<select id="selectByRecord" parameterType="com.wyz.entity.UserDO" resultType="com.wyz.entity.UserDO">
	select * from user
	<where>
		<if test="user.name != null">
			and name = #{user.name, jdbcType=VARCHAR}
		</if>
		<if test="user.age != null">
			and age = #{user.age, jdbcType=INTEGER}
		</if>
		<if test="user.gender != null">
			and gender = #{user.gender, jdbcType=VARCHAR}
		</if>
		<if test="user.idType != null">
			and id_type = #{user.idType, jdbcType=INTEGER}
		</if>
		<if test="user.idNumber != null">
			and id_number = #{user.idNumber, jdbcType=VARCHAR}
		</if>
	</where>
</select>

可以看到控制台输出的 SQL 语句的条件语句中,首个条件前的 and 已经不见了:

现在我们删除所有参数赋值,再来执行测试案例:可以看到,此时控制台输出的 SQL 语句中,已经完全没有了 where 的身影。

set 元素

set 元素与 where 元素类似,不过 set 元素是用在 update 语句中的,set 元素会先根据 if 元素的处理结果生成子句,然后将子句结尾处的“,”移除,最后在子句的开始处插入“set”,如果不存在子句则不会插入“set”。与 where 元素不同的是,在 SQL 规范中,update 语句是必须包含 set 子句的,否则无法处理 SQL 语句。

写一个使用 set 元素更新用户信息的案例,首先来写 MyBatis 映射器中的 SQL 语句:

<update id="updateByRecord" parameterType="com.wyz.entity.UserDO">
  update user
  <set>
    <if test="user.name != null">
      name = #{user.name, jdbcType=VARCHAR},
    </if>
    <if test="user.age != null">
      age = #{user.age, jdbcType=INTEGER},
    </if>
    <if test="user.gender != null">
      gender = #{user.gender, jdbcType=VARCHAR},
    </if>
    <if test="user.idType != null">
      id_type = #{user.idType, jdbcType=INTEGER},
    </if>
    <if test="user.idNumber != null">
      id_number = #{user.idNumber, jdbcType=VARCHAR},
    </if>
  </set>
  where user_id = #{user.userId, jdbcType=INTEGER}
</update>

可以看到,在 update 语句中,最后一个赋值语句里我们同样在id_number = #{user.idNumber, jdbcType=VARCHAR}后添加了“,”。

接着我们来定义 Mapper 接口中的方法:

int updateByRecord(@Param("user") UserDO userDO);

测试代码:

public void testUpdateByRecord() throws IOException {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	UserDO userDO = new UserDO();
	userDO.setUserId(1);
	userDO.setName("王二麻子");
	userDO.setAge(19);
	userDO.setGender("M");
	userDO.setIdType(1);
	userDO.setIdNumber("123456789012345678");
	
	int count = userMapper.updateByRecord(userDO);
	
	sqlSession.commit();
	sqlSession.close();
}

执行测试案例,可以看到控制台输出的 SQL 语句中,set 元素成功的为 update 语句插入了 set 语句,并且为 set 语句中的最后一项赋值操作自动删除了“,”,如下:

trim 元素

上面我们展示了在 select 语句中和 update 语句中动态组装 where 语句和 set 语句,那么如果想要在 insert 语句中动态组装插入字段呢?是不是有类似 where 元素和 set 元的元素呢?

不过 MyBatis 提供了另一个更加灵活(意味着你需要做更多的处理来实现你的需求) 的元素:tirm 元素

trim 元素中定义了 4 个属性,可以将它们分为两组:

  • 前缀相关的属性: prefix 属性:子句中要在开始处插入的字符串; prefixOverrides 属性:子句中要被移除的前缀字符串;
  • 后缀相关的属性: suffix 属性:子句中要在结尾处插入的字符串; suffixOverrides 属性:子句中要被移除的后缀字符串。

trim 元素中,同样先是根据 if 元素的处理结果生成子句,然后根据 prefixOverrides 属性和 suffixOverrides 属性的配置移除子句前后缀的字符串,最后根据 prefix 属性和 suffix 属性的配置,在子句的开始处和结尾处插入字符串。

Tips:你可能会被属性名称中的 Overrides 所迷惑,认为是 prefix(suffix)中指定的字符串替换了 prefixOverrides(suffixOverrides)中指定的字符串,实际上在 MyBatis 的源码中,MyBatis 是通过移除后拼接来实现的。

我们使用 trim 元素实现具有动态插入功能的 insert 语句,如下:

<insert id="insertByRecord" parameterType="com.wyz.entity.UserDO">
	insert into user
	<trim prefix="(" suffix=")" suffixOverrides=",">
		<if test="user.userId != null">
			user_id,
		</if>
		<if test="user.name != null">
			name,
		</if>
		<if test="user.age != null">
			age,
		</if>
		<if test="user.gender != null">
			gender,
		</if>
		<if test="user.idType != null">
			id_type,
		</if>
		<if test="user.idNumber != null">
			id_number,
		</if>
	</trim>
	<trim prefix="values (" suffix=")" suffixOverrides=",">
		<if test="user.userId != null">
			#{user.userId, jdbcType=INTEGER},
		</if>
		<if test="user.name != null">
			#{user.name, jdbcType=VARCHAR},
		</if>
		<if test="user.age != null">
			#{user.age, jdbcType=INTEGER},
		</if>
		<if test="user.gender != null">
			#{user.gender, jdbcType=VARCHAR},
		</if>
		<if test="user.idType != null">
			#{user.idType, jdbcType=INTEGER},
		</if>
		<if test="user.idNumber != null">
			#{user.idNumber, jdbcType=VARCHAR},
		</if>
	</trim>
</insert>

定义接口方法:

int insertByRecord(@Param("user") UserDO userDO);

测试方法:

public void testInsertByRecord() throws IOException {
  Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
  SqlSession sqlSession = sqlSessionFactory.openSession();
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  
  UserDO userDO = new UserDO();
  userDO.setUserId(5);
  userDO.setName("赵六");
  userDO.setAge(1);
  userDO.setGender("F");
  userDO.setIdType(1);
  userDO.setIdNumber("123456789012345678");
  
  int count = userMapper.insertByRecord(userDO);
  
  sqlSession.commit();
  sqlSession.close();
}

来观察控制台的输出:

foreach 元素

foreach 元素提供了在映射器中遍历集合(对应 Java 中的 List,Set)和字典的能力(对应 Java 中的 Map)的能力,通常我们会在构建 in 条件语句中使用。foreach 元素提供了 6 个属性:

假设我们有一个查询页面,其中的一个查询条件为证件类型,并且证件类型允许多选,因此我们在处理查询语句时需要考虑到传入多个证件类型的场景,此时最容易想到的方式是使用 in 来构建条件语句。

我们先来写 Mapper 接口中的方法:

List<UserDO> selectByIdTypes(@Param("idTypes") List<Integer> idTypes);

映射器中的 SQL 语句:

<select id="selectByIdTypes" resultType="com.wyz.entity.UserDO">
	select * from user
	where id_type in
	<foreach collection="idTypes" item="idType" separator="," open="(" close=")">
		#{idType, jdbcType=INTEGER}
	</foreach>
</select>

单元测试:

public void testSelectByIdTypes() throws IOException {
  Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
  SqlSession sqlSession = sqlSessionFactory.openSession();
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  
  List<Integer> idTypes = List.of(1, 2);
  List<UserDO> users = userMapper.selectByIdTypes(idTypes);
}

执行单元测试,来看控制台输出的 SQL 语句:

使用 foreach 元素实现批量插入

foreach 元素的功能非常强大,我们不仅仅可以用 foreach 元素来构建 in 条件语句,也可以使用 foreach 元素来实现批量插入。

首先来类 Mapper 接口中的方法:

int batchInsert(@Param("users") List<UserDO> users);

接着我们来写 MyBatis 映射器中的 SQL 语句:

<insert id="batchInsert">
	insert into user(user_id, name, gender, age, id_type, id_number)
	values
	<foreach collection="users" item="user" open="(" separator="), (" close=")">
		#{user.userId, jdbcType=INTEGER},
		#{user.name, jdbcType=VARCHAR},
		#{user.gender, jdbcType=VARCHAR},
		#{user.age, jdbcType=INTEGER},
		#{user.idType, jdbcType=INTEGER},
		#{user.idNumber, jdbcType=VARCHAR}
	</foreach>
</insert>

单元测试:

public void testBatchInsert() throws IOException {
  Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
  SqlSession sqlSession = sqlSessionFactory.openSession();
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  
  List<UserDO> users = new ArrayList<>();
  for (int i = 0; i < 2; i++) {
    UserDO user = new UserDO();
    user.setUserId(3 + i);
    user.setName("批量" + (i + 1));
    user.setAge(18 + i);
    user.setGender("F");
    user.setIdType(1);
    user.setIdNumber("11010319981112325" + i);
    users.add(user);
  }
  userMapper.batchInsert(users);
  sqlSession.commit();
  sqlSession.close();
}

观察控制台输出的 SQL 语句:

除了以上两种简单的用法外,foreach 元素还可以帮助我们实现更多复杂的 SQL 语句,比如之前我利用 foreach 元素实现过联表 union all 之后的条件查询语句,更多的用法大家可以自行探索。

choose 元素

choose 元素的功能与 Java 中的 switch 相似,choose 元素包含两个子元素:when 元素和 otherwise 元素。choose 元素连同它的子元素在用法上与 Java 中的switch...case...default语句的用法相同。

比如说我们有个需求,根据传入的条件查询用户信息,允许传入姓名和性别,传入了姓名就按姓名查询(忽略性别),如果没有传入姓名就按照性别查询,如果两者都没有传入,就查询证件类型为 1 的用户,这里我们使用 UserDO 作为入参,那么 MyBatis 映射器中的 SQL 语句我们可以这么写:

<select id="selectByUserNameOrGender" parameterType="com.wyz.entity.UserDO" resultType="com.wyz.entity.UserDO">
  select * from user
  <where>
    <choose>
      <when test="user.name != null">
        and name = #{user.name, jdbcType=VARCHAR}
      </when>
      <when test="user.gender != null">
        and gender = #{user.gender, jdbcType=VARCHAR}
      </when>
      <otherwise>
        and id_type = 1
      </otherwise>
    </choose>
  </where>
</select>

单元测试的代码:

public void testSelectByUserNameOrGender() throws IOException {
  Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
  SqlSession sqlSession = sqlSessionFactory.openSession();
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  
  UserDO user = new UserDO();
  user.setName("张三");
  user.setGender("F");
  List<UserDO> users = userMapper.selectByUserNameOrGender(user);
}

单元测试中,我们构建查询参数时同时传入了姓名和性别,最后我们来执行单元测试,观察控制台的输出:

可以看到,在控制台输出的 SQL 语句的查询条件中只有 name 这一个条件,这也符合我们对 choose 元素的认知。

Tips:你可以在构建查询参数时删除为 name 字段赋值的 Java 代码来观察控制台输出的 SQL 语句,以及同时删除为 name 和 gender 赋值的 Java 代码,再来观察控制台输出的 SQL 语句。

bind 元素

ind 元素允许你在 MyBatis 映射器中创建一个自定义局部变量,最常见的场景就是我们在模糊查询中使用 bind 元素

通常我们在 MyBatis 中构建模糊查询语句,可能会使用如下几种方式:

  • 直接在 Java 代码中为参数添加“%”后传递到 SQL 语句中;
  • 直接在 MyBatis 映射器文件中拼接“%”,例如:select * from user where name like '${surname}%';
  • 使用 MySQL 提供的 concat 函数(Oracle 中使用 “||”)拼接“%”,例如:select * from user where name like concat(#{surname, jdbcType=VARCHAR}, '%')。

除了以上几种方法外,我们还可以使用 bind 元素来构建 MyBatis 映射器中的局部变量,来实现模糊查询的功能。例如,我们需要查询某个姓氏的用户有多少,可以这样构建 MyBatis 映射器中的 SQL 语句:

<select id="selectBySurname" resultType="com.wyz.entity.UserDO">
  <bind name="bindValue" value="surname + '%'" />
  select * from user where name like #{bindValue}
</select>

单元测试的代码:

public void testSelectBySurname() throws IOException {
  Reader mysqlReader = Resources.getResourceAsReader("mybatis-config.xml");
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(mysqlReader);
  SqlSession sqlSession = sqlSessionFactory.openSession();
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  
  
  List<UserDO> users = userMapper.selectBySurname("张");
  sqlSession.commit();
  sqlSession.close();
}

控制台输出的 SQL 语句:

MyBatis 映射器中允许使用 bind 元素来创建多个自定一局部变量,如:

<select id="selectBySurname" resultType="com.wyz.entity.UserDO">
  <bind name="bindValue" value="surname + '%'" />
  <bind name="bindValue2" value="'%' + surname + '%'" />
  select * from user where name like #{bindValue}
</select>

好了,以上就是我们在mybaits中构建动态sql用到的重要元素,在工作中我们也是必不可少的~

相关推荐

Python自动化脚本应用与示例(python办公自动化脚本)

Python是编写自动化脚本的绝佳选择,因其语法简洁、库丰富且跨平台兼容性强。以下是Python自动化脚本的常见应用场景及示例,帮助你快速上手:一、常见自动化场景文件与目录操作...

Python文件操作常用库高级应用教程

本文是在前面《Python文件操作常用库使用教程》的基础上,进一步学习Python文件操作库的高级应用。一、高级文件系统监控1.1watchdog库-实时文件系统监控安装与基本使用:...

Python办公自动化系列篇之六:文件系统与操作系统任务

作为高效办公自动化领域的主流编程语言,Python凭借其优雅的语法结构、完善的技术生态及成熟的第三方工具库集合,已成为企业数字化转型过程中提升运营效率的理想选择。该语言在结构化数据处理、自动化文档生成...

14《Python 办公自动化教程》os 模块操作文件与文件夹

在日常工作中,我们经常会和文件、文件夹打交道,比如将服务器上指定目录下文件进行归档,或将爬虫爬取的数据根据时间创建对应的文件夹/文件,如果这些还依靠手动来进行操作,无疑是费时费力的,这时候Pyt...

python中os模块详解(python os.path模块)

os模块是Python标准库中的一个模块,它提供了与操作系统交互的方法。使用os模块可以方便地执行许多常见的系统任务,如文件和目录操作、进程管理、环境变量管理等。下面是os模块中一些常用的函数和方法:...

21-Python-文件操作(python文件的操作步骤)

在Python中,文件操作是非常重要的一部分,它允许我们读取、写入和修改文件。下面将详细讲解Python文件操作的各个方面,并给出相应的示例。1-打开文件...

轻松玩转Python文件操作:移动、删除

哈喽,大家好,我是木头左!Python文件操作基础在处理计算机文件时,经常需要执行如移动和删除等基本操作。Python提供了一些内置的库来帮助完成这些任务,其中最常用的就是os模块和shutil模块。...

Python 初学者练习:删除文件和文件夹

在本教程中,你将学习如何在Python中删除文件和文件夹。使用os.remove()函数删除文件...

引人遐想,用 Python 获取你想要的“某个人”摄像头照片

仅用来学习,希望给你们有提供到学习上的作用。1.安装库需要安装python3.5以上版本,在官网下载即可。然后安装库opencv-python,安装方式为打开终端输入命令行。...

Python如何使用临时文件和目录(python目录下文件)

在某些项目中,有时候会有大量的临时数据,比如各种日志,这时候我们要做数据分析,并把最后的结果储存起来,这些大量的临时数据如果常驻内存,将消耗大量内存资源,我们可以使用临时文件,存储这些临时数据。使用标...

Linux 下海量文件删除方法效率对比,最慢的竟然是 rm

Linux下海量文件删除方法效率对比,本次参赛选手一共6位,分别是:rm、find、findwithdelete、rsync、Python、Perl.首先建立50万个文件$testfor...

Python 开发工程师必会的 5 个系统命令操作库

当我们需要编写自动化脚本、部署工具、监控程序时,熟练操作系统命令几乎是必备技能。今天就来聊聊我在实际项目中高频使用的5个系统命令操作库,这些可都是能让你效率翻倍的"瑞士军刀"。一...

Python常用文件操作库使用详解(python文件操作选项)

Python生态系统提供了丰富的文件操作库,可以处理各种复杂的文件操作需求。本教程将介绍Python中最常用的文件操作库及其实际应用。一、标准库核心模块1.1os模块-操作系统接口主要功能...

11. 文件与IO操作(文件io和网络io)

本章深入探讨Go语言文件处理与IO操作的核心技术,结合高性能实践与安全规范,提供企业级解决方案。11.1文件读写11.1.1基础操作...

Python os模块的20个应用实例(python中 import os模块用法)

在Python中,...