互联网轻量级框架整合之MyBatis动态SQL

MyBatis的动态SQL是一项强大且实用的功能，它允许开发者在XML映射文件中编写可灵活变化的SQL语句，这些语句能够根据传入参数的条件或值动态地调整其结构和内容。这样，程序可以在运行时生成适应特定业务场景的SQL，避免了手动拼接SQL字符串带来的诸多问题，如语法错误、注入风险、维护困难等等

动态SQL的核心元素

标签，其作用是根据表达式的值决定是否包含某段SQL片段，如下代码所示，test 属性用于指定一个OGNL表达式（Object-Graph Navigation Language），表达式的结果为布尔值。当结果为 true 时，包含该标签内的SQL语句片段；否则，忽略该片段。

<if test="condition">
    SQL语句片段
if>
1
2
3

标签，其作用是自动过滤掉无效的WHERE子句条件，避免出现诸如WHERE 1=1这样的无效条件，如下代码所示

<select id="selectByCondition" parameterType="com.example.ConditionBean" resultType="com.example.ResultBean">
    SELECT * FROM table
    <where>
        
        <if test="id != null">AND id = #{id}if>
        <if test="name != null">AND name LIKE #{name}if>
        
    where>
select>

1
2
3
4
5
6
7
8
9
10

在标签内部可以放置多个或其他动态标签。MyBatis会智能地去掉那些条件表达式为假（false或空值）的子句，并确保最终生成的WHERE子句以正确的关键字（如AND或OR）开始，无需手动处理空格和连接词

标签，其作用是用于去除SQL语句片段首尾的指定字符或字符串，如下代码所示

<trim prefix="PREFIX_" suffix="SUFFIX_" prefixOverrides="PREFIX_TO_REMOVE_" suffixOverrides="SUFFIX_TO_REMOVE_">
    SQL语句片段
trim>
1
2
3

• prefix：在SQL片段开头添加指定字符串。
• suffix：在SQL片段结尾添加指定字符串。
• prefixOverrides：指定需要从SQL片段开头移除的字符或字符串。
• suffixOverrides：指定需要从SQL片段结尾移除的字符或字符串。
• 常用于清理多余的逗号、空格等字符，特别是在动态构建IN列表或SET子句时非常有用

标签，其作用是用于更新操作，动态生成SET子句，自动过滤掉值未改变的字段，避免不必要的更新，如下代码所示

<update id="updateUser" parameterType="com.example.User">
    UPDATE users
    <set>
        <if test="username != null">username = #{username},if>
        <if test="email != null">email = #{email},if>
        
    set>
    WHERE id = #{id}
update>

1
2
3
4
5
6
7
8
9
10

标签内部同样可以放置多个或其他动态标签。MyBatis会在执行更新时，仅保留那些值已改变的字段对应的SET项，同时自动处理好逗号的添加与移除，确保生成的SET子句语法正确

, , 标签其作用是实现类似于编程语言中的switch语句，根据不同的条件分支选择不同的SQL片段，如下代码所示

<choose>
    <when test="condition1">
        SQL语句片段1
    when>
    <when test="condition2">
        SQL语句片段2
    when>
    
    <otherwise>
        默认SQL语句片段
    otherwise>
choose>
1
2
3
4
5
6
7
8
9
10
11
12

每个标签对应一个条件分支，test属性的值为一个OGNL表达式。当匹配到某个条件为真时，执行相应的SQL片段。如果没有标签的条件为真，则执行标签内的SQL

标签其作用是遍历集合或数组，动态生成重复的SQL片段，常用于批量插入、批量更新或IN子句的构造，如下代码所示

<foreach item="item" index="index" collection="collection" open="(" separator="," close=")">
    #{item}
foreach>
1
2
3

• item：循环变量名，用于引用集合中的当前元素。
• index：可选，循环索引变量名，用于获取当前元素的索引。
• collection：OGNL表达式，用于指定要遍历的集合或数组。
• open：遍历开始时添加的字符或字符串。
• separator：元素间的分隔符。
• close：遍历结束时添加的字符或字符串

SELECT * FROM users WHERE id IN
<foreach item="userId" collection="userIds" open="(" separator="," close=")">
    #{userId}
foreach>
1
2
3
4

动态SQL的执行原理：MyBatis在解析XML映射文件时，会将动态SQL标签及其内容转换为一个抽象语法树（AST）。当实际执行SQL时，根据传入的参数对象，动态计算OGNL表达式的值，进而根据表达式的真假状态决定是否包含某段SQL、如何拼接条件等。这个过程是在运行时完成的，因此能够动态生成适应不同业务场景的SQL语句。
通过以上这些动态SQL标签，MyBatis极大地简化了复杂查询和更新逻辑的编写，使得SQL更加简洁、清晰，易于维护，同时也提高了开发效率，减少了手动拼接SQL引发的错误

动态SQL实例

<select id="findRoles" parameterType="string" resultType="role">
	
	select id, role_name, note from t_role where 1=1
	
	<if test="roleName != null and roleName !=''">
		and role_name like concat('%', #{roleName}, '%')
	if>
select>

1
2
3
4
5
6
7
8
9
10

当参数roleName被传递给映射器时，如果参数不为空，则构造对roleName的模糊查询，否则不构造这个条件

    
    
    
    <select id="findRoles2" parameterType="role" resultType="role">
        select id, role_name, note from t_role
        where 1=1
        
        <choose>
            
            <when test="id != null and id !=''">
                AND id = #{id}
            when>
            
            <when test="roleName != null and roleName !=''">
                AND role_name like concat('%', #{roleName}, '%')
            when>
            
            <otherwise>
                AND note is not null
            otherwise>
        choose>
    select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

代码中使用了choose、when、otherwise元素，MyBatis会根据参数进行判断来动态组装SQL，以满足不同的也无需求

在之前的SQL中，都加入了一个条件1=1，其作用主要是为了不报错，顺带解释一下这个条件的几种作用：

• 简化动态查询条件的拼接：当应用程序需要根据用户的选择或输入动态构建 SQL 查询时，WHERE 1=1 可作为初始条件，确保查询语句的语法完整性。这样，无论用户是否提供额外的筛选条件，查询都能正常执行。
  后续根据实际需要，可以在 WHERE 1=1 后面通过 AND 连接更多的具体条件。例如，如果用户指定了角色名称，可以追加 AND role_name = ‘用户指定的角色名’。由于 WHERE 子句已经存在且始终为真，添加其他条件时无需考虑是否存在初始条件。
• 便于条件的增删与调试：
  开发过程中，程序员可能需要临时注释掉某些查询条件进行测试。如果查询开始于 WHERE 1=1，那么只需注释相应的 AND 后面的条件即可，无需调整 WHERE 子句本身。这使得代码更易于维护和调整。
• 作为占位符或模板：
  在编写通用的 SQL 模板或框架代码时，WHERE 1=1 可作为一个标准起始点，待具体使用时再填充实际条件。这种做法有利于保持查询结构的一致性，尤其是在使用代码生成工具或预编译 SQL 语句的场景下。
• 在特定上下文中表示“全选”：
  尽管直接省略 WHERE 子句也能达到查询全部记录的效果，但在一些特定情况下（比如需要动态添加条件的代码逻辑），保留 WHERE 1=1 可能有助于清晰表达查询意图为获取所有满足可能附加条件的数据。
• 特殊情况下的特殊用途：
  在极少数情况下，WHERE 1=1 或类似的永真条件可能会用于特定的数据库操作，如某些数据库迁移脚本或数据复制任务中，用来仅复制表结构而不获取任何数据（如使用 CREATE TABLE AS SELECT … WHERE 1=0 创建空表结构）。

WHERE 1=1 主要作为一种编程技巧，用于简化动态查询条件的构造、维护和调试，以及在特定场景下作为查询条件的占位符或全选标志。尽管它本身对查询结果并无实际影响（因为 1=1 永远为真），但其在实际编程实践中具有一定的实用价值。

<select id="findRoles3" parameterType="role" resultType="role">
    select id, role_name, note from t_role
    <where>
        
        <if test="roleName != null and roleName !=''">
            and role_name like concat('%', #{roleName}, '%')
        if>
        
        <if test="note != null and note !=''">
            and note like concat('%', #{note}, '%')
        if>
    where>
select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

只有当where条件成立时，才会加入“and”这个SQL关键字到组装的SQL执行，否则不加入，就相当于where 1=1的作用

有时候要去掉的是一些特殊的SQL语法，比如常见的and、or，使用trim元素也可以达到预期效果

<select id="findRoles4" parameterType="string" resultType="role">
    
    select id, role_name, note from t_role
    
    <trim prefix="where" prefixOverrides="and">
        
        <if test="roleName != null and roleName !=''">
            and role_name like concat('%', #{roleName}, '%')
        if>
    trim>
select>

1
2
3
4
5
6
7
8
9
10
11
12
13

trim元素意味着要去掉一些特殊的字符串，prefix代表语句的前缀，prefixOverdridesd代表需要去掉的字符串

在Hibernate中，常常因为更新某一个对象，而发送全部字段给持久对象，但现实场景中可能只需要更新某一个字段，这个情况下如果发送所有字段那很可能造成过大的网络开销而降低了性能，最佳的办法是只对主键和要更新的字段进行操作

如果一个表需要更新两个字段，那写两条SQL又会降低可读性和增加代码冗余,Hibernate就是把全部字段发送给SQL，而避免多条SQL的情况

<update id="updateRole" parameterType="role">
    update t_role
    <set>
        
        <if test="roleName != null and roleName !=''">
            role_name = #{roleName},
        if>
        
        <if test="note != null and note != ''">
            note = #{note}
        if>
    set>
    
    where id = #{id}
update>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MyBatis中，可以使用set元素，来避免这个问题，而且当set元素遇到了多余的逗号，会把对应的逗号去掉

foreach元素是一个循环语句，它的作用是遍历集合，它能够很好地支持数组和List、Set接口的集合，对此进行遍历功能，往往用于SQL中的in关键字场景

<select id="findRoleByIds" resultType="role">
    
    select id, role_name, note from t_role where id in
    <foreach item="id" index="index" collection="idList" open="("
        separator="," close=")">
        #{id}
    foreach>
select>

1
2
3
4
5
6
7
8
9
10

• item属性：属性值id作用：定义在循环体中用于引用当前迭代元素的变量名。在这个例子中，item=“id” 表示在循环内部，可以通过${id}或 #{id}引用来访问集合中当前迭代到的单个角色ID。
• index属性：属性值index作用：定义在循环体中用于引用当前迭代索引（下标）的变量名。这里没有直接使用到index变量，但如果有需要，可以通过${index}在 SQL 中引用当前元素在 idList 集合中的位置。
• collection属性：属性值idList作用：指定要遍历的集合或数组的名称。这是 标签必须提供的属性，它指定了传入 SQL 语句的方法参数中所包含的集合或数组的变量名。在本例中，方法调用时应传入一个名为 idList 的 List 或数组，其中包含了要查询的角色ID列表。
• open属性：属性值(作用：定义在循环开始时输出的文本内容。此处设置为(，表示在生成的 SQL 中，在 IN 子句的起始处插入一个左括号。
• separator属性：属性值,作用：定义在每次迭代之间输出的分隔文本。这里设定为 ,，意味着在相邻两个角色ID之间插入逗号，以符合 SQL IN 子句中列举多个值时的语法要求。
• close属性：属性值)作用：定义在循环结束时输出的文本内容。这里设置为)，表示在所有角色ID循环完成后，在 IN 子句的末尾插入一个右括号，完成整个条件表达式的闭合。
• #{id}：在循环体内，使用 #{id}表达式来引用当前迭代元素（即item属性指定的变量）。MyBatis 将会将其替换为实际的值，并且会对该值进行预编译参数化，提供 SQL 注入攻击防护。

<select id="getRoleTest" parameterType="string" resultType="role">"
	
	 select id, role_name, note from t_role
	 
	 <if test="type=='Y'.toString()">
		 where 1=1
	 if>
select>
1
2
3
4
5
6
7
8
9

<select id="findRoles5" parameterType="string" resultType="role">
    <bind name="pattern" value="'%' + _parameter + '%'" /> 
    select id, role_name as roleName, note FROM t_role
    where role_name like
    #{pattern}
select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

bind 元素是 MyBatis 框架中的一个动态 SQL 标签，允许开发者定义一个变量并赋予其特定值，以便后续在 SQL 语句中作为参数值使用，在这个例子中，定义了一个名为 pattern 的变量，其值由表达式 ‘%’ + _parameter + ‘%’ 计算得出。其中 _parameter 是 MyBatis 中预定义的变量，代表传入方法的参数。这样，通过 bind 标签，我们创建了一个用于模糊查询的 SQL 模式

<select id="findRoles6" resultType="role">
    <bind name="pattern_roleName" value="'%' + roleName + '%'" /> 
    <bind name="pattern_note" value="'%' + note + '%'" /> 
    SELECT id, role_name as roleName, note FROM t_role
    where role_name like #{pattern_roleName}
    and note like #{pattern_note}
select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14

因为MyBatis支持多个参数，因此，可是使用bind元素定义两个新的变量

如此看来，bind元素还带来了如下几个优点：

• 简化复杂逻辑处理： 有时 SQL 查询可能涉及复杂的逻辑判断或字符串拼接，直接在 SQL 语句中编写可能会导致代码难以阅读和维护。使用 bind 标签可以在 XML 映射文件中以编程方式处理这些逻辑，将计算结果封装成变量，使得 SQL 语句保持简洁清晰。例如，根据不同的业务规则动态生成 WHERE 子句的条件部分。
• 提高 SQL 可移植性： 不同数据库系统对于某些函数或操作可能存在语法差异。bind 标签可以通过在 Java 层处理这些差异，确保生成的 SQL 语句适应目标数据库。比如，使用 bind 来拼接字符串进行模糊查询，可以避免直接在 SQL 中使用特定数据库的字符串连接函数（如 MySQL 的 CONCAT() 或 Oracle 的 ||），从而增强 SQL 语句在跨数据库环境下的可移植性。
• 防范 SQL 注入： MyBatis 使用预编译的 PreparedStatement 执行 SQL，其参数替换机制能够有效防止 SQL 注入攻击。当使用 bind 标签构造参数值时，即使表达式中包含了用户输入的部分（如 _parameter），生成的最终值也会被安全地传递给预编译的 SQL 语句，避免了原始数据直接拼接到 SQL 字符串中可能导致的安全风险。
• 复用与组织： 对于需要在多个地方使用的相同逻辑或值，可以使用 bind 标签预先计算并绑定，避免重复书写。这有助于减少代码冗余，提高映射文件的结构化程度和可读性