3.动态sql

本章目标

动态Sql

本章内容

一、动态sql

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。常见动态标签：

if
choose (when, otherwise)
trim (where, set)
foreach

1、if条件判断

if标签的test属性判断成立，就会将标签对之间的sql语句拼接到主sql语句上

要查询员工状态为0，如果不传入 empName，那么所有处于 0 状态的 Employee都会返回；如果传入了 empName 参数,则根据empName做模糊查询，这该怎么实现？

 <select id="queryByNameAndTel" resultType="employee">
     select emp_name, phone, address, salary
     from employee
     where status = 0
     <if test="empName != null">
         AND emp_name = #{empName}
     </if>
     <if test="phone != null">
           and phone = #{phone}
     </if>
 </select>

empName是传过来参数的名称

2、choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<select id="queryByNameAndTel" resultType="employee">
     select emp_name, phone, address, salary
     from employee
     where status = 0
     <choose>
         <when test="empName != null">
             and emp_name = #{empName}
         </when>
         <when test="phone != null">
             and phone = #{phone}
         </when>
         <otherwise>
             and dept_id = 1
         </otherwise>
     </choose>
 </select>

实际这种应用场景并不多，我们很少只根据一个条件来筛选，更多是并的关系

3、where标签

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

示例一中，如果去掉 where status = 0 ，而只有phone有值，sql语句会变成下面的样式，在执行时会报错

 select emp_name, phone, address, salary
         from employee
         where
             and phone = #{phone}

通过where标签处理

<select id="queryByNameAndTel" resultType="employee">
     select emp_name, phone, address, salary
     from employee
     <where>
         <if test="empName != null">
             and emp_name = #{empName}
         </if>
         <if test="phone != null">
             and phone = #{phone}
         </if>
     </where>
</select>

4、set标签

用于动态更新语句的类似解决方案叫做 set。set元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

 <update id="updateByPrimaryKeySelective">
     update employee
     <set>
         <if test="empName != null">
             emp_name = #{empName},
         </if>
         <if test="phone != null">
             phone = #{phone},
         </if>
         <if test="address != null">
             address = #{address},
         </if>
         <if test="salary != null">
             salary = #{salary}
         </if>
     </set>
     where id = #{id}
 </update>

set即update语句中的关键字

5、trim

给sql语句添加前后缀，将前后关键字去掉,通过trim可以生成where和set的效果，属性说明：

5.1、属性

属性	说明
prefix	添加前面的关键字（在标签开始位置，添加属性中的内容）
suffix	添加后面的关键字（在标签结束位置，添加属性中的内容）
prefixoverrides	去掉第一个关键字（所有子标签中第一子标签中的前缀关键字）
suffixoverrides	去掉最后一个关键字（所有子标签中最后一个子标签后缀的关键字）

5.2、trim可以生成where效果

会在employee后面添加where 关键字，并去掉第一个AND|OR的关键字

  <select id="queryByNameAndTel" resultType="employee">
         select emp_name, phone, address, salary
         from employee
         <trim prefix="where" prefixOverrides="AND|OR">
             <if test="empName != null">
                 and emp_name = #{empName}
             </if>
             <if test="phone != null">
                 and phone = #{phone}
             </if>
         </trim>
     </select>

5.3、trim可以生成set效果

会在employee后面添加set关键字，并去掉最后一个逗号

 <update id="updateByPrimaryKeySelective">
        update employee
        <trim prefix="set" suffixOverrides=",">
            <if test="empName != null">
                emp_name = #{empName},
            </if>
            <if test="phone != null">
                phone = #{phone},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="salary != null">
                salary = #{salary}
            </if>
        </trim>
        where id = #{id}
    </update>

6、foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

6.1、属性说明

属性	说明
collection	集合的名字默认为collection或者list ，可以通过@Param(“listName”)
item	循环出的每个对象在访问对象属性时，需要加前缀employee.id
open	前缀
close	后缀
separator	以值进行分隔
index	索引

可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。

当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。

当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值

6.2、批量插入

     <insert id="insertBatch">
         insert into employee_train values
             <foreach collection="list" item="employeeTrain" open="(" close=")" separator="),(">
                 0,#{employeeTrain.empId},now(),#{employeeTrain.content},#{employeeTrain.remark}
             </foreach>
     </insert>

属性说明：

简化版：

    <insert id="insertBatch">
        insert into employee_train values
            <foreach collection="list" item="employeeTrain" separator=",">
                (0,#{employeeTrain.empId},now(),#{employeeTrain.content},#{employeeTrain.remark})
            </foreach>
    </insert>

6.3、批量删除

    <delete id="deleteBatch">
        delete from employee_train where id in
        <foreach collection="trainIds" item="trainId" open="(" close=")" separator=",">
            #{trainId}
        </foreach>
    </delete>

生成sql语句如下：

delete from employee_train where id in ( ? , ? , ? , ? , ? , ? )

7、`SQL`标签-提取重用的SQL代码片段

我们发现queryAll、queryById、queryByNameLike中都用到了所有字段，那么我们能否把这些字段提取到一个标签中

这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值。

<sql id="BASE_COLUMN_LIST">
    id,dept_name as deptName,remark
</sql>
<select id="queryAll" resultType="Dept">
    select
    <include refid="BASE_COLUMN_LIST"/>
    from dept
</select>

8、bind标签

拼接sql内容

mysql中本身有concat方法可以进行拼接操作, 但是数据库有很多种，有些数据库中不支持concat方法，导致数据库迁移后，代码出现问题,可以使用bind来进行拼接

<select id="queryByNameLike" parameterType="string" resultType="dept">
    <bind name="dn" value="'%'+deptName+'%'"/>
    select id, dept_name as deptName, remark
    from dept
    where dept_name like #{dn}
</select>

思维导图

posted @ 2025-04-18 09:41 icui4cu 阅读(24) 评论(0) 收藏举报

刷新页面返回顶部

icui4cu