动态 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>
版权声明:本文不是「本站」原创文章,版权归原作者所有 | 原文地址: