第三章 动态SQL
3.1 动态SQL中的元素
动态SQL可以根据不同的条件动态生成SQL语句,使得SQL语句具有更高的灵活性和可重用性。在实际开发中,常常需要根据不同的业务场景生成不同的SQL语句,这时候就可以使用动态SQL来实现。比如,在查询时,可能只需要查询某些条件下的数据,此时可以根据不同的条件动态生成查询语句;在更新时,有些字段可能需要根据不同情况来更新,也可以使用动态SQL来生成不同的更新语句。通过使用动态SQL,可以大大提高代码的可维护性和可读性。
下表是常用的动态sql元素:
元素 | 描述 |
if | 用于判断是否执行某段 SQL 代码,类似于 Java 中的 if 语句。可以通过 test 属性指定判断条件。 |
choose | 用于在多个条件中选择一个进行执行,类似于 Java 中的 switch 语句。需要和 when 和 otherwise 元素一起使用。 |
when | 用于在 choose 元素中指定条件分支,类似于 Java 中的 case 语句。可以通过 test 属性指定条件。 |
otherwise | 用于在 choose 元素中指定默认分支,类似于 Java 中的 default 语句。 |
trim | 用于对 SQL 代码进行修剪(trim)操作,可以指定前缀、后缀和要修剪的内容。 |
where | 用于在 SQL 语句中添加 WHERE 子句,同时可以自动去除多余的 AND 或 OR。 |
set | 用于在 SQL 语句中添加 SET 子句,用于更新操作。 |
foreach | 用于在 SQL 语句中执行循环操作,可以遍历一个集合或数组,并生成多个 SQL 语句。需要指定集合或数组的属性名称和 SQL 代码块。 |
3.2 条件查询操作
3.2.1 <if>元素
<if> 元素是 MyBatis 中用于动态生成 SQL 语句的元素之一。它可以根据给定的条件决定是否包含对应的 SQL 片段。
<if> 元素接受一个测试表达式,如果该表达式的值为 true,则包含该元素内部的 SQL 片段,否则不包含。在生成 SQL 语句时,MyBatis 会自动将所有包含了 <if> 元素的 SQL 片段连接起来。
下面是一个简单的使用例子,假设我们有一个查询用户列表的 SQL,但是我们可能想要在查询时加入一些条件,例如根据用户名称模糊查询,或者只查询某些特定状态的用户,这时就可以使用 <if> 元素:
<select id="getUserList" resultType="User">
SELECT * FROM user
<where>
<if test="name != null">
AND name like CONCAT('%', #{name}, '%')
</if>
<if test="status != null">
AND status = #{status}
</if>
</where>
</select>
在这个例子中,我们使用了 <where> 元素包含了所有的查询条件,然后使用了两个 <if> 元素分别检查了 name 和 status 参数是否存在。如果这些参数存在,就会在 SQL 语句中加入对应的条件语句,否则不会加入。注意,<if> 元素的 test 属性指定了一个 OGNL 表达式,用于判断该元素内部的 SQL 片段是否应该包含在最终生成的 SQL 中。
3.2.2 <choose>、<when>、<otherwise>元素
<choose>、<when>、<otherwise> 元素是 MyBatis 动态 SQL 中用于实现多重判断的元素。
<choose> 元素用于包裹多个 <when> 和一个 <otherwise>,相当于 Java 中的 switch 语句。
<when> 元素用于指定当条件成立时执行的 SQL 语句。
<otherwise> 元素用于指定所有 <when> 都不成立时执行的 SQL 语句。
<select id="selectByCondition" parameterType="map" resultMap="userResultMap">
SELECT * FROM users
<where>
<choose>
<when test="id != null">
AND id = #{id}
</when>
<when test="username != null">
AND username = #{username}
</when>
<when test="email != null">
AND email = #{email}
</when>
<otherwise>
AND 1=0
</otherwise>
</choose>
</where>
</select>
在上面的例子中,如果参数 id 不为空,则会执行 AND id = #{id};如果 username 不为空,则会执行 AND username = #{username};如果 email 不为空,则会执行 AND email = #{email}。如果三个参数都为空,则会执行 AND 1=0,即不返回任何结果。
3.2.3 <where>、<trim>元素
<where> 和 <trim> 元素是 MyBatis 动态 SQL 中常用的元素,用于在 SQL 语句中根据条件动态拼接 SQL 片段,从而灵活生成 SQL 语句。
<where> 元素用于在 SQL 语句的 WHERE 子句中动态拼接条件表达式。该元素会自动移除开头多余的 AND 或 OR 子句,以及结尾多余的 AND 或 OR 子句。示例代码如下:
<select id="getUserList" parameterType="map" resultType="User">
SELECT * FROM user
<where>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="age != null">
AND age = #{age}
</if>
</where>
</select>
上面的示例代码中,如果传入的参数中包含了 username 和 age 属性,则会根据这两个属性的值动态拼接 WHERE 子句。
<trim> 元素用于在 SQL 语句的任何部分动态拼接字符串,可以用于除了 SELECT 语句外的任何 SQL 语句中。该元素可以用于去除拼接后 SQL 语句的开头或结尾的一些字符串,或者将几个字符串连接在一起形成一个大的字符串。示例代码如下:
<update id="updateUser" parameterType="User">
UPDATE user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null">
username = #{username},
</if>
<if test="password != null">
password = #{password},
</if>
</trim>
WHERE id = #{id}
</update>
上面的示例代码中,<trim> 元素用于动态拼接 UPDATE 语句中的 SET 子句。prefix 属性用于指定 SET 子句的前缀,suffixOverrides 属性用于指定去除 SET 子句的结尾多余的逗号。如果传入的参数中包含了 username 和 password 属性,则会根据这两个属性的值动态拼接 SET 子句。
3.3 更新操作
<set>元素是MyBatis中用于在UPDATE语句中动态生成SET子句的元素之一。它的作用是在UPDATE语句中指定需要更新的列和相应的值。
<set>元素通常与<if>元素一起使用,以便根据实际情况生成需要更新的列。例如,如果只需要更新某些列,而其他列不需要更新,则可以使用<set>元素与<if>元素来判断是否需要更新某个列。
下面是一个简单的使用<set>元素的例子:
<update id="updateUser" parameterType="User">
update user
<set>
<if test="name != null">name = #{name},</if>
<if test="age != null">age = #{age},</if>
<if test="gender != null">gender = #{gender},</if>
</set>
where id = #{id}
</update>
在这个例子中,<update>元素用于定义一个UPDATE语句,其中<set>元素用于生成SET子句。<if>元素用于根据实际情况判断是否需要更新某个列。例如,如果name属性不为空,则使用name = #{name}来更新name列。如果age属性不为空,则使用age = #{age}来更新age列,以此类推。
需要注意的是,<set>元素会自动去掉生成的SET子句中最后一个逗号(,)。因此,我们不需要手动处理最后一个逗号导致的语法错误。
还可以使用<trim>元素实现更新操作,它可以将SQL语句的一部分拼接起来,并自动去除不必要的SQL关键字。
下面是一个使用<trim>元素实现更新操作的例子:
<update id="updateEmployee">
update employee
<trim prefix="set" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="age != null">age=#{age},</if>
<if test="position != null">position=#{position},</if>
</trim>
where id=#{id}
</update>
在上面的例子中,<trim>元素用于拼接 SET 子句,它的属性 prefix 表示SET关键字,suffixOverrides 属性用于去除最后一个逗号。如果name、age或position属性不为null,就会拼接对应的 SET 子句,最终得到完整的更新语句。
3.4 复杂查询操作
在实际开发中,有时可能会遇到这种情况:假设在一个客户表中有 1000 条数据,现在需要将d值小于100 的客户信息全部查询出来,这种情况下,如果每条记录都逐一查询,显然是不可取的。有的人会想到可以在 Java 方法中使用循环语句,将查询方法放在循环语句中,通过条件循环的方式查询出所需的数据。这种查询方式虽然可行,但每执行一次循环语句,都需要向数据库中发送一条查询 SOL,其查询效率是非常低的。为了解决上述问题,MyBatis 提供了用于数组和集合循环遍历的<foreach>元素。下面将对<foreach>元素进行详细讲解。
3.4.1 <foreach>元素的属性
<foreach> 元素用于在 SQL 语句中迭代集合、数组等元素,生成动态 SQL。它可以帮助我们避免手动拼接 SQL 语句的繁琐操作。
下表是 <foreach> 元素的属性及其说明:
属性名 | 描述 |
collection | 指定要迭代的集合或数组的名称 |
item | 指定在迭代过程中的每个元素的名称 |
index | 指定在迭代过程中的每个元素的索引 |
open | 指定在所有迭代元素前添加的字符串 |
close | 指定在所有迭代元素后添加的字符串 |
separator | 指定在每个迭代元素之间添加的字符串 |
javaType | 指定迭代元素的 Java 类型 |
jdbcType | 指定迭代元素的 JDBC 类型 |
include | 指定包含的 SQL 片段 |
indexProperty | 指定索引属性的名称 |
collectionProperty | 指定集合属性的名称 |
以下是一个简单的 <foreach> 元素的使用示例,它用于查询给定一组员工编号的员工信息:
<select id="selectEmployees" resultType="Employee">
SELECT * FROM employee
WHERE id IN
<foreach item="id" collection="employeeIds" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
在这个例子中,我们使用了 collection 属性指定了要迭代的集合 employeeIds,使用 item 属性指定了每个迭代元素的名称 id。然后我们使用了 open 属性指定了迭代元素前要添加的字符串 (,使用 close 属性指定了迭代元素后要添加的字符串 ),使用 separator 属性指定了每个迭代元素之间要添加的字符串。在 SQL 语句中,我们使用了 IN 关键字将查询条件与迭代元素拼接起来,最终生成的 SQL 语句为:
SELECT * FROM employee
WHERE id IN (1, 2, 3, 4)
这个 SQL 语句将查询员工编号分别为 1、2、3、4 的员工信息。
3.4.2 <foreach>迭代数组
下面是一个迭代数组的简单例子,假设有一个用户列表,其中每个用户包含一个名字和一个年龄:
<select id="selectUsersByIds" parameterType="int[]" resultType="User">
SELECT * FROM user
WHERE id IN
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
在这个例子中,<foreach>元素将被展开成一个用逗号分隔的IN子句,其中#{item}将被替换为数组中的元素值。在这个例子中,collection属性指定了要迭代的数组,item属性指定了在迭代过程中当前元素的别名,separator属性指定了分隔符。
3.4.3 <foreach>迭代List
当使用List类型参数时,可以使用foreach元素来遍历列表中的元素,然后将它们传递给SQL语句中的IN表达式。
下面是一个简单的例子,使用foreach遍历一个List类型参数:
<select id="getUsersByIds" resultType="User">
SELECT * FROM user WHERE id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</select>
在上面的例子中,ids是一个List类型的参数,foreach元素用于遍历ids列表中的所有元素,将它们放置在IN表达式中,从而查询id在该列表中的用户信息。
3.4.4 <foreach>迭代Map
当使用Map类型参数时,可以使用foreach元素来遍历Map中的键值对,然后将它们传递给SQL语句中的WHERE条件中。
下面是一个简单的例子,使用foreach遍历一个Map类型参数:
<select id="getUsersByConditions" resultType="User">
SELECT * FROM user WHERE 1=1
<foreach item="value" index="key" collection="conditions" separator="AND">
<if test="key == 'name'">
AND name = #{value}
</if>
<if test="key == 'age'">
AND age = #{value}
</if>
</foreach>
</select>
在上面的例子中,conditions是一个Map类型的参数,foreach元素用于遍历conditions中的所有键值对,将它们放置在WHERE条件中,从而查询符合条件的用户信息。在foreach元素中,item表示Map中的value,index表示Map中的key。foreach元素中还包含了两个if元素,用于根据Map中的key不同,设置不同的查询条件。