Mybatis动态sql

动态SQL是Mybatis框架中的一个强大功能，它允许根据不同的条件动态构建SQL语句。

1. if

在Mybatis框架中，<if> 标签是动态SQL的核心组件之一，它允许根据特定的条件来动态地拼接SQL语句。
通过使用 <if> 标签，你可以根据不同的条件动态地构建复杂的SQL语句，从而避免了硬编码和手动拼接SQL字符串，这样可以提高代码的可读性和可维护性。

1.1 案例

<select id="getDeptByStep" resultMap="deptEmpStep">
    <!-- 使用<if>标签来添加额外的过滤条件，例如查询部门名称 -->
    <if test="dname != null and dname.trim() != ''">
        select * from t_dept
        where did = #{did} and dname = #{dname}
    </if>
    <if test="dname == null or dname.trim() == ''">
        <!-- 如果没有提供部门名称，则仅按部门ID查询 -->
        select * from t_dept
        where did = #{did}
    </if>
</select>

在这个例子中，<if> 标签被用来检查是否提供了一个部门名称。如果提供了部门名称并且它不是空字符串，则SQL语句会包含一个额外的条件来过滤部门名称。如果没有提供部门名称或者提供的名称是空字符串，则只按部门ID查询。

2. where

在Mybatis中，<where> 标签与 <if> 标签结合使用可以有效地构建动态的查询条件，确保生成的SQL语句既简洁又正确。

2.1 案例

<select id="getEmpListByMoreTJ" resultType="Emp">
    select * from t_emp
    <where>
        <!-- 使用<if>标签确保只有当条件成立时才添加条件 -->
        <if test="ename != null and ename.trim() != ''">
            ename = #{ename}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="sex != null and sex.trim() != ''">
            and sex = #{sex}
        </if>
        <!-- 如果没有其他条件，自动添加WHERE关键字 -->
        <if test="ename == null and age == null and sex == null">
            where 1=1
        </if>
    </where>
</select>

使用 <where> 标签包裹所有条件，它会自动处理 WHERE 关键字的添加，并且会去除条件前多余的 AND。
移除了原始示例中不必要的前一个 <if> 标签，因为 <where> 标签已经处理了 WHERE 关键字的添加。
添加了一个额外的 <if> 标签来处理没有任何条件的情况。如果 ename、age 和 sex 都为 null，则自动添加 WHERE 1=1，这通常用于添加默认的排序或分页条件。

3. trim

<trim> 标签是Mybatis动态SQL中的一个非常有用的元素，它可以用来添加或删除SQL片段的前缀或后缀。
通过使用 <trim> 标签，我们可以确保生成的SQL语句既灵活又易于维护，同时避免了手动拼接字符串可能带来的错误。

3.1 案例

在这个示例中，<trim> 标签已经正确地使用了 prefix="where" 来添加 WHERE 关键字，并使用 suffixOverrides="and" 来去除末尾多余的 AND。

<select id="getEmpListByMoreTJ" resultType="Emp">
    select * from t_emp
    <trim prefix="where" suffixOverrides="and">
        <if test="ename != '' and ename != null">
            ename = #{ename} and
        </if>
        <if test="age != '' and age != null">
            age = #{age} and
        </if>
        <if test="sex != '' and sex != null">
            sex = #{sex}
        </if>
        <!-- 添加额外的条件，例如按某个字段排序 -->
        <if test="orderBy != null and orderBy.trim() != ''">
            order by ${orderBy}
        </if>
        <!-- 添加分页条件 -->
        <if test="page != null and pageSize != null">
            limit #{pageSize} offset #{page} * #{pageSize}
        </if>
    </trim>
</select>

添加了排序和分页条件。排序条件使用了 order by，而分页条件使用了 LIMIT 和 OFFSET。这里假设 orderBy、page 和 pageSize 是传递给查询的参数。
prefix="where" 确保了只有当至少有一个条件被选中时，WHERE 关键字才会被添加到SQL语句中。
suffixOverrides="and" 确保了每个条件后面不会有多余的 AND 关键字。

4.choose、when、otherwise

在Mybatis的动态SQL中，<choose>、<when> 和 <otherwise> 标签确实与编程语言中的 if...else if...else 语句功能类似。这些标签用于在一个标签块中创建多个条件分支，类似于多重条件判断。

4.1 案例

<select id="selectProduct" resultType="Product">
    select * from products
    <where>
        <choose>
            <when test="name != null">
                and name = #{name}
            </when>
            <when test="price != null">
                and price &lt; #{price}
            </when>
            <otherwise>
                and quantity > 0
            </otherwise>
        </choose>
    </where>
</select>

<choose> 标签是整个条件分支的起点。
<when> 标签定义了第一个条件，类似于 if 语句。如果这个条件为真，则执行 <when> 标签内的SQL片段。
<otherwise> 标签是条件分支的最后选择，类似于 else 语句。如果所有 <when> 条件都不满足，则执行 <otherwise> 标签内的SQL片段。

4.2 与 `if...else if...else` 的比较

<choose>、<when> 和 <otherwise> 提供了一种更清晰和结构化的方式来处理多个条件分支，尤其是在条件之间没有严格的顺序时。
在某些情况下，使用 <choose> 可能比一系列嵌套的 <if> 标签更易于阅读和维护。
if...else if...else 语句通常用于当只有一个条件为真时，而 <choose> 标签允许多个条件同时为真。

4.3 注意

<choose>、<when> 和 <otherwise> 只能按顺序评估，所以 <when> 标签必须按可能的真值顺序排列。
<otherwise> 标签是可选的；如果所有的 <when> 条件都不满足，那么如果没有 <otherwise>，则不会执行任何操作。

5. foreach

<foreach> 标签是Mybatis动态SQL中的另一个强大功能，它允许你在SQL语句中迭代集合或数组。

5.1 案例：批量插入

<insert id="insertMoreEmp">
    insert into t_emp (ename, age, sex, email, entrydate)
    <foreach collection="emps" item="emp" separator="," open="(" close=")">
        (null, #{emp.ename}, #{emp.age}, #{emp.sex}, #{emp.email})
    </foreach>
</insert>

在这个例子中，我们正在插入一个名为 emps 的列表到 t_emp 表中。<foreach> 标签会遍历这个列表，并且为列表中的每个 Emp 对象生成一个插入语句。separator="," 指定了每个插入语句之间的分隔符，open="(" 和 close=")" 定义了插入语句的开始和结束。

5.2 案例：批量删除

<delete id="deleteMoreByArray">
    delete from t_emp where
    <foreach collection="eids" item="eid" separator="or">
        eid = #{eid}
    </foreach>
</delete>

在这个例子中，我们正在根据一个名为 eids 的数组中的每个元素删除 t_emp 表中的记录。<foreach> 标签遍历数组，并为数组中的每个元素生成一个条件语句。separator="or" 指定了条件语句之间的分隔符。

5.3 属性解释

collection：这是必须的属性，指定要迭代的集合或数组。它可以是一个列表、数组、映射或其他任何类型的对象。
item：这是必须的属性，指定迭代过程中对象的别名。在 <foreach> 标签内部，你可以使用这个别名来引用集合或数组中的元素。
separator：指定了在遍历集合或数组时每个元素之间的分隔符。默认情况下，分隔符是逗号。
open：指定了要添加到SQL语句开始处的字符串。这通常用于创建子查询的开始部分。
close：指定了要添加到SQL语句结束处的字符串。这通常用于创建子查询的结束部分。

6. Sql片段

在Mybatis中，SQL片段是一种非常有用的功能，它允许你将常用的SQL代码段提取出来并重用。这样可以减少代码重复，提高SQL语句的可维护性。使用 <sql> 标签可以定义一个SQL片段，然后通过 <include> 标签将其包含到其他的SQL语句中。

6.1 定义SQL片段

1
2
3

<sql id="empColumns">
    eid, ename, age, sex, did
</sql>

在这段代码中，<sql> 标签定义了一个名为 empColumns 的SQL片段，它包含了从 t_emp 表中选择某些列的SQL代码。

6.2 使用SQL片段

1
2
3

<select id="selectEmpById" resultType="Emp">
    select <include refid="empColumns"></include> from t_emp where eid = #{eid}
</select>

在这个例子中，<include refid="empColumns"></include> 标签用来将之前定义的 empColumns SQL片段包含到 select 语句中。这样，当执行 selectEmpById 查询时，会从 t_emp 表中选择 eid、ename、age、sex 和 did 这五列。

6.3 属性解释

id：这是必须的属性，用来唯一标识这个SQL片段。
sql：这是一个可选的属性，用来定义实际的SQL片段内容。如果使用了这个属性，那么就不需要 <include> 标签来引用它。

6.4 使用场景

重复的SQL语句：如果你有一个或多个查询经常包含相同的SQL代码，你可以将其定义为片段来避免重复。
复杂的联合查询：在复杂的联合查询中，你可以将子查询或者复杂的连接条件定义为片段。
存储过程：如果某些SQL代码经常被存储过程使用，你可以将其定义为片段，并在需要的时候包含到存储过程调用中。

kyang's blog