29、Mybatis进阶:where、trim、choose、set、foreach使用

用 MyBatis 与 H2 实际核对 where、trim、choose、set、foreach,明确空集合、空条件和更新范围。

简明结论

动态标签只解决 SQL 组合的一部分问题;空集合、无条件查询和更新字段仍需明确业务规则。

动态 SQL 的关键是按参数生成合法且符合业务含义的语句。where/trim 处理前缀,set 处理更新列的逗号,choose 选择分支,foreach 展开集合;标签能拼出合法 SQL,不等于空参数时的业务行为一定正确。

完整 Mapper 与测试

复用 SelectorProvider 文章的共享 POM,使用 MyBatis 3.5.16、H2 2.2.224、JDK 21。将 XML 放在 src/test/resources/EmployeeMapper.xml,测试类放在 src/test/java/site。XML 的 OGNL 属性使用 and 与单引号空字符串,避免原资料中双引号和未转义 & 破坏 XML。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="employee">
  <select id="where" resultType="int">
    SELECT id FROM employee
    <where><if test="name != null and name != ''">AND name = #{name}</if></where>
    ORDER BY id
  </select>
  <select id="trim" resultType="int">
    SELECT id FROM employee
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="name != null and name != ''">AND name = #{name}</if>
    </trim>
    ORDER BY id
  </select>
  <select id="choose" resultType="int">
    SELECT id FROM employee WHERE
    <choose><when test="id != null">id = #{id}</when>
      <when test="name != null">name = #{name}</when><otherwise>active = 1</otherwise></choose>
    ORDER BY id
  </select>
  <select id="foreach" resultType="int">
    SELECT id FROM employee
    <where><choose><when test="ids != null and ids.size() > 0">
      id IN <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </when><otherwise>1 = 0</otherwise></choose></where>
    ORDER BY id
  </select>
  <update id="set">
    UPDATE employee <set>name = #{name},<if test="active != null">active = #{active},</if></set>
    WHERE id = #{id}
  </update>
</mapper>
package site;

import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assertions.*;

class DynamicSqlTest {
  @Test void executesAllFiveDynamicElements() throws Exception {
    var source = new UnpooledDataSource("org.h2.Driver", "jdbc:h2:mem:phase6", "sa", "");
    var config = new Configuration(new Environment("test", new JdbcTransactionFactory(), source));
    try (InputStream xml = getClass().getResourceAsStream("/EmployeeMapper.xml")) {
      new XMLMapperBuilder(xml, config, "EmployeeMapper.xml", config.getSqlFragments()).parse();
    }
    try (var session = new SqlSessionFactoryBuilder().build(config).openSession()) {
      try (var statement = session.getConnection().createStatement()) {
        statement.execute("CREATE TABLE employee(id INT PRIMARY KEY,name VARCHAR(30),active INT)");
        statement.execute("INSERT INTO employee VALUES(1,'Ada',1),(2,'Lin',0),(3,'Ada',1)");
      }
      assertEquals(List.of(1,2,3), session.selectList("employee.where", Map.of()));
      assertEquals(List.of(1,3), session.selectList("employee.where", Map.of("name","Ada")));
      assertEquals(List.of(1,3), session.selectList("employee.trim", Map.of("name","Ada")));
      assertEquals(List.of(2), session.selectList("employee.choose", Map.of("id",2,"name","Ada")));
      assertEquals(List.of(1,3), session.selectList("employee.choose", Map.of()));
      assertEquals(List.of(1,3), session.selectList("employee.foreach", Map.of("ids",List.of(3,1))));
      assertEquals(List.of(), session.selectList("employee.foreach", Map.of("ids",List.of())));
      assertEquals(1, session.update("employee.set", Map.of("id",2,"name","Lee")));
      assertEquals(List.of(2), session.selectList("employee.where", Map.of("name","Lee")));
      session.rollback();
      System.out.println("where/trim/choose/set/foreach=passed; empty-ids=0; updated-rows=1");
    }
  }
}
mvn -Dtest=DynamicSqlTest test

本次核对的结果

实际输出 where/trim/choose/set/foreach=passed; empty-ids=0; updated-rows=1。断言包含无过滤条件、名字过滤、id 优先的 choose 分支、非空与空集合、动态更新及回读。数据库为本轮 H2 内存实例;没有把这些结果称作 Oracle/MySQL 方言验证。

空 ids 在本例被明确解释为“不匹配任何行”,通过 1=0 实现,避免空 IN 或意外变成全表查询。set 中 name 是必填列,所以不会产生空 SET;业务若允许所有更新字段为空,必须在进入 Mapper 前决定拒绝或跳过。更新始终带 id 条件。

where 没有匹配条件时可以生成无 WHERE 的查询,本例只对三行测试表允许这样做;生产查询还要按需要限制范围。值通过 #{} 绑定,动态表名/排序字段不能直接把不可信输入交给 ${}。

下一步阅读

原资料片段(未重建原环境)

下方保留原资料的上下文、示例与截图;本轮验证只对应上方新增文件,未将旧运行结果记为本轮结果。

本文内容来自山硅谷

详细情况可参考

http://www.gulixueyuan.com/my/course/43

 

下面给出xml配置。请注意该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="com.atguigu.mybatis.dao.EmployeeMapperDynamicSQL">
	<!-- 
• if:判断
	• choose (when, otherwise):分支选择;带了break的swtich-case
	如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
	 -->
	 <!-- 查询员工,要求,携带了哪个字段查询条件就带上这个字段的值 -->
	 <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	 <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!-- where -->
	 	<where>
		 	<!-- test:判断表达式(OGNL)
		 	OGNL参照PPT或者官方文档。
		 	  	 c:if  test
		 	从参数中取值进行判断
		 	
		 	遇见特殊符号应该去写转义字符:
		 	&&:
		 	-->
		 	<if test="id!=null">
		 		id=#{id}
		 	</if>
        <if test="lastName != null and lastName != ''">
		 		and last_name like #{lastName}
		 	</if>
        <if test="email != null and email.trim() != ''">
		 		and email=#{email}
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 	</where>
	 </select>
	 
	 <!--public List<Employee> getEmpsByConditionTrim(Employee employee);  -->
	 <select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!-- 后面多出的and或者or where标签不能解决 
	 	prefix="":前缀:trim标签体中是整个字符串拼串 后的结果。
	 			prefix给拼串后的整个字符串加一个前缀 
	 	prefixOverrides="":
	 			前缀覆盖: 去掉整个字符串前面多余的字符
	 	suffix="":后缀
	 			suffix给拼串后的整个字符串加一个后缀 
	 	suffixOverrides=""
	 			后缀覆盖:去掉整个字符串后面多余的字符
	 			
	 	-->
	 	<!-- 自定义字符串的截取规则 -->
	 	<trim prefix="where" suffixOverrides="and">
	 		<if test="id!=null">
		 		id=#{id} and
		 	</if>
        <if test="lastName != null and lastName != ''">
		 		last_name like #{lastName} and
		 	</if>
        <if test="email != null and email.trim() != ''">
		 		email=#{email} and
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	gender=#{gender}
		 	</if>
		 </trim>
	 </select>
	 
	 <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
	 <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee 
	 	<where>
	 		<!-- 如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个 -->
	 		<choose>
	 			<when test="id!=null">
	 				id=#{id}
	 			</when>
	 			<when test="lastName!=null">
	 				last_name like #{lastName}
	 			</when>
	 			<when test="email!=null">
	 				email = #{email}
	 			</when>
	 			<otherwise>
	 				gender = 0
	 			</otherwise>
	 		</choose>
	 	</where>
	 </select>
	 
	 <!--public void updateEmp(Employee employee);  -->
	 <update id="updateEmp">
	 	<!-- Set标签的使用 -->
	 	update tbl_employee 
		<set>
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				email=#{email},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</set>
		where id=#{id} 
<!-- 		
		Trim:更新拼串
		update tbl_employee 
		<trim prefix="set" suffixOverrides=",">
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				email=#{email},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</trim>
		where id=#{id}  -->
	 </update>
	 
	 <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
	 <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!--
	 		collection:指定要遍历的集合:
	 			list类型的参数会特殊处理封装在map中,map的key就叫list
	 		item:将当前遍历出的元素赋值给指定的变量
	 		separator:每个元素之间的分隔符
	 		open:遍历出所有结果拼接一个开始的字符
	 		close:遍历出所有结果拼接一个结束的字符
	 		index:索引。遍历list的时候是index就是索引,item就是当前值
	 				      遍历map的时候index表示的就是map的key,item就是map的值
	 		
	 		#{变量名}就能取出变量的值也就是当前遍历出的元素
	 	  -->
	 	<foreach collection="ids" item="item_id" separator=","
	 		open="where id in(" close=")">
	 		#{item_id}
	 	</foreach>
	 </select>
	 
</mapper>

版权声明:本文不是「本站」原创文章,版权归原作者所有 | 原文地址: