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

Mybaits映射器之动态SQL语句

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

前言:

今天我们一起聊聊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用到的重要元素,在工作中我们也是必不可少的~

相关推荐

威信Chronosonic XVX全新旗舰全球首发 设计特点彻底公开

第一眼看到WilsonAudio新推出的ChronosonicXVX音箱,相信大家都会直觉认为它是两年前超级旗舰WAMMMasterChronosonic的缩小版,不过这个推测并不完全正确。C...

C#高精度Timer和Delay以及时间测量

在PCHMI7.0后在工具箱里会多一个MsTimer,以及Delay和Microsecond两个类。...

python教程从基础到精通,第9课—日期与时间

Hello,小伙伴们,祝大家五.一玩得快乐!刚学习完了七大数据类型,今天咱们来学习日期与时间的表示方法。Python标准库中提供了时间和日期的支持:calendar:日历相关;time、datetim...

软件测试|教你轻松玩转Python日期时间

Python基础之日期时间处理...

Go语言中互斥锁与读写锁,你知多少?

简述Golang中的锁机制主要包含互斥锁和读写锁互斥锁互斥锁是传统并发程序对共享资源进行控制访问的主要手段。在Go中主要使用sync.Mutex的结构体表示。一个简单的示例:funcmutex()...

变形金刚动画大电影——经典台词赏析

YOURDAYSARENUMBEREDNOW,DECEPTI-CREEPS你们活不了多久了,霸天虎小子。-{铁皮说的话,体现了铁皮的嫉恶如仇,可是后来铁皮在飞船上遇袭身亡,可谓是出师未捷身先...

Python时间日期模块使用教程(python3日期)

1.时间日期处理概述在日常编程中,时间日期处理是非常常见的需求,比如:记录日志时间...

亚马逊介绍AWS“无服务器”云服务改进:数据库可线上扩充容量等

IT之家11月29日消息,在今天于美国拉斯维加斯展开的亚马逊“AWSre:Invent2023”活动中,亚马逊计算部门资深副总裁PeterDeSantis,介绍了旗下三款云端服务,IT...

2.日期格式 datetime(日期时间显示格式)

fromdatetimeimportdatetime1.获取当前日期和时间now=datetime.now()#2025-05-3110:56:01.4687822.格式化日期...

【科普】时间单位大盘点(时间单位都有哪些?)

时间单位,是7种基本单位之一,长度、时间、质量、物质的量、光照度、电流和(热力学)温度是七种基本单位。本词条中时间单位以时间从大到小列。今天我们来盘点下时间的单位换算...

基于PHP的Laravel框架,盘点Github高星Web管理后台,效率为王!

在Web开发工作中,选择一个高效、稳定的后台管理系统是提高开发效率的关键。虽然PHP在近些年中的热度有所减退,但其上手简单、开源、灵活且被广泛应用的特点,仍然使其在编程语言排行榜中保持前十的位置。这表...

如何使用PHP编写一个简单的留言板?

留言板是一个常见的Web应用程序,允许用户在网站上发布和查看留言。在本文中,我们将使用PHP编写一个简单的留言板,介绍构建过程中的关键步骤和技巧。一、准备工作在开始编写留言板之前,我们需要准备好以下工...

产品经理提需求时要考虑的 15 个隐性需求

虽然世界充满未知的变化,但是有一些大的方向还是可以把握的,本文跟大家谈谈产品经理提需求时要考虑的15个隐性需求,enjoy~俗话说,计划赶不上变化快,无论需求文档做得如何细致,考虑得如何周全,总会...

关于 PHP 启动 MongoDb 找不到指定模块问题

前言:最近有一个小demo,需要通过PHP将用户行为记录储存到MongoDB,再用Spark做协同过滤。由于以前处理跨语言交互是通过消息中间件,这次本地使用MongoDB却弄出了几个问...

PHP程序员老鸟面试经历(php程序员怎么样)

在任何时代找任何工作都有面试这么一说的。特别是高端技术类的工种对技术理论和技术实操能力要求很严格。大部分公司招收技术员工的要求也越来愈高。至于PHP程序员也是如此,我估计大多数PHP老鸟已经不在意所...