🪴 Obsidian Publish

MyBatis-7 动态SQL

8分钟阅读

1 动态 SQL 介绍

动态 SQL 有效解决了字符串拼接问题。

主要分为四大类

  • If 条件判断
  • Trim (where, set) 拼接时处理
  • Choose (when, otherwise) 选择作用
  • Foreach 遍历作用
  • Bind 绑定变量

1.1 if 标签

根据条件拼接字符串。

<mapper namespace="com.thr.mapper.UserMapper">
    <select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
        select * from t_user where
        <if test = "userName!=null and userName!=''">
            username = #{userName}
        </if>
        <if test = "userAddress!=null and userAddress!=''">
            and address = #{userAddress}
        </if>
    </select>
</mapper>

问题：对于 select 来说，如果两个判断都错误，那么语句出现错误，因此需要补充 where 标签。

1.2 trim、where、set 标签

1.2.1 where 标签

<where> 标签相当于 SQL 语句中的 where 关键字，而且 where 标签还有特殊的作用。作用如下：

  • 自动向 sql 语句中添加 where 关键字
  • 去掉第一个条件的 and 或 or 关键字

上面的示例用 where 标签改写后示例如下：

<!--根据用户名和地址查询用户信息-->
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
    select * from t_user
    <where>
        <if test="userName!=null and userName!=''" >
            and username = #{userName}
        </if>
        <if test="userAddress!=null and userAddress!=''">
            and address = #{userAddress}
        </if>
    </where>
</select>

1.2.2 trim 标签

<trim> 标签功能：

  • 包含内容前加上前缀 (prefix 属性) 或者后缀 (suffix 属性)
  • 包含内容首部去除 (prefixOverrides 属性) 或者尾部去除 (suffixOverrides 属性)

上述内容用 <trim> 标签实现（需要注意 prefixOverrides 属性中的“空格问题”）

<!--根据用户名和地址查询用户信息-->
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
    select * from t_user
    <trim prefix="where" prefixOverrides="and | or ">
        <if test="userName!=null and userName!=''" >
            and username = #{userName}
        </if>
        <if test="userAddress!=null and userAddress!=''">
            and address = #{userAddress}
        </if>
    </trim>
</select>

1.2.3 set 标签

<set> 标签作用如下：

  • 自动向修改 sql 语句中添加 set 关键字
  • 去掉最后一个条件结尾的逗号

使用 set 标签示例代码如下：

<!--修改用户名、年龄和地址-->
<update id="updateUser" parameterType="user">
    update t_user
    <set>
        <if test="userName!=null and userName!=''">
            username = #{userName},
        </if>
        <if test="userAge!=null and userAge!=''">
            age = #{userAge},
        </if>
        <if test="userAddress!=null and userAddress!=''">
            address = #{userAddress},
        </if>
    </set>
    where id = #{userId}
</update>

与 <trim> 等价

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

1.3 choose、when、otherwise 标签

<choose> 、 <when> 、 <otherwise> 标签，相当于 Java 中 switch-case-default 语句。

<select id="selectUserByChoose" resultType="user" parameterMap="userMap">
    select * from t_user
    <where>
        <choose>
            <when test="userName!= null and userName!=''">
                username=#{userName}
            </when>
            <when test="userAddress!= null and userAddress!=''">
                and address=#{userAddress}
            </when>
            <otherwise>
                and age=#{userAge}
            </otherwise>
        </choose>
    </where>
</select>

1.4 foreach 标签

<foreach> 标签主要用于遍历集合。通常是用来构建 IN 条件语句，也可用于其他情况下动态拼接 sql 语句。

<foreach> 标签有以下几个属性：

  • collection ：表示要遍历的集合元素，注意不要写#{}。
  • item ：表示每次遍历时生成的对象名 (注：当传入 Map 对象或 Map. Entry 对象的集合时，index 是键，item 是值)。
  • index ：表示在迭代过程中，每次迭代到的位置。
  • open ：表示开始遍历时要拼接的字符串。
  • close ：表示结束遍历时要拼接的字符串。
  • sperator ：表示在每次遍历时两个对象直接的连接字符串。

select * from t_user where (id=1) or (id=2) or (id=4) or (id=5);

<select id="selectUserByListId" parameterType="userVo" resultMap="userMap">
  select * from t_user
    <where>
        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

select * from t_user where id in (1, 2, 4, 5);

<select id="selectUserByListId" parameterType="userVo" resultMap="userMap">
  select * from t_user
    <where>
        <foreach collection="ids" item="id" open="id in (" close=")" separator=",">
            #{id}
        </foreach>
    </where>
</select>

批量添加数据

<!--int insertMoreByList(@Param("emps") List<Emp> emps);-->
<insert id="insertMoreByList">
	insert into t_emp values
	<foreach collection="emps" item="emp" separator=",">
		(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
	</foreach>
</insert>

1.5 bind 标签

<bind> 标签允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文（可定义多个）。示例代码如下：

<!-- 模糊查询，根据username字段查询用户-->
<select id="selectUserByName" parameterType="string" resultMap="userMap">
    <bind name="pattern" value="'%'+_parameter+'%'"/>
    select * from t_user where username like #{pattern}
</select>

这种方式无论是 Mysql 还是 Oracle 都可以使用这样的语句，提高了代码的可移植性。如果传递了多个参数，则可以定义多个 bind 标签。

<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
    <bind name="pattern_username" value="'%'+userName+'%'"/>
    <bind name="pattern_address" value="'%'+userAddress+'%'"/>
    select * from t_user where username like #{pattern_username} and address like #{pattern_address}
</select>

1.6 SQL 片段

sql 片段，可以记录一段公共 sql 片段，在使用的地方通过 include 标签进行引入

声明 sql 片段：<sql> 标签

<sql id="empColumns">eid,emp_name,age,sex,email</sql>

引用 sql 片段：<include> 标签

<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
	select <include refid="empColumns"></include> from t_emp
</select>

2 实体符号表

在 mapper 的动态 SQL 中若出现大于号、小于号、大于等于号，小于等于号等符号，最好将其转换为实体符号。否则， XML 可能会出现解析出错问题。
实体符号表：

符号意义实体符号
<小于&lt;
>大于&gt;
>=大于等于&gt;=
⇐小于等于&lt;=

3 注解中使用动态 SQL

3.1 script 标签

@Update({"<script>",
         "update Author",
         "  <set>",
         "    <if test='username != null'>username=#{username},</if>",
         "    <if test='password != null'>password=#{password},</if>",
         "    <if test='email != null'>email=#{email},</if>",
         "    <if test='bio != null'>bio=#{bio}</if>",
         "  </set>",
         "where id=#{id}",
         "</script>"})
void updateAuthorValues(Author author);

4 多数据库支持

如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 _databaseId 的变量来为不同的数据库构建特定的语句。比如下面的例子：

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

5 动态 SQL 中的插入脚本语言

MyBatis 从 3.2 版本开始支持插入脚本语言，这允许你插入一种语言驱动，并基于这种语言来编写动态 SQL 查询语句。

可以通过实现以下接口来插入一种语言：

public interface LanguageDriver {
  ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
  SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
  SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
}

实现自定义语言驱动后，你就可以在 mybatis-config.xml 文件中将它设置为默认语言：

<typeAliases>
  <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
</typeAliases>
<settings>
  <setting name="defaultScriptingLanguage" value="myLanguage"/>
</settings>

或者，你也可以使用 lang 属性为特定的语句指定语言：

<select id="selectBlog" lang="myLanguage">
  SELECT * FROM BLOG
</select>

或者，在你的 mapper 接口上添加 @Lang 注解：

public interface Mapper {
  @Lang(MyLanguageDriver.class)
  @Select("SELECT * FROM BLOG")
  List<Blog> selectBlog();
}

提示 可以使用 Apache Velocity 作为动态语言，更多细节请参考 MyBatis-Velocity 项目。

你前面看到的所有 xml 标签都由默认 MyBatis 语言提供，而它由语言驱动 org.apache.ibatis.scripting.xmltags.XmlLanguageDriver （别名为 xml ）所提供。

反向链接