【Mybatis】动态sql标签

最新推荐文章于 2023-12-03 16:24:29 发布

Java知识日历

最新推荐文章于 2023-12-03 16:24:29 发布

阅读量163

点赞数

文章标签：数据库 mysql java mybatis sql

1.Mybatis 动态 sql

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

虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

2. 常见的动态 sql 标签

2.1 if

在现实的工作场景中，我们通常需要按照不同的维度对数据进行查询。比如我们通过员工管理系统要查询一个 name 为”Tom” 的人，在大一点的公司可能有几个 name 都为”Tom” 的同事并且他们有可能分部在不同的部门，而在小点的公司可能只有一个人根本就不用按部门来过滤，这个时候我们可以通过传参来控制我们的过滤条件如下：

/**
 * @Description employee的dao层代码
 * @Author xiaoqx <Javxuan@163.com>
 * @Version V1.0.0
 * @Since 2017/11/26
 */
public interface EmployeeMapper {

    List<Employee> selectEmployeeList(Employee employee);
}
<select id="selectEmployeeList" resultType="com.worldly.config.entity.Employee" databaseId="mysql">
        select
        *
        from t_emp e
        where
            <if test="name!=null and name!=''">
                e.emp_name=#{name,jdbcType=VARCHAR}
            </if>
            <if test="dep!=null">
                and e.emp_dep=#{dep.id,jdbcType=INTEGER}
            </if>
    </select>

配合一个 “_databaseId” 变量的 databaseIdProvider 可用于动态代码中，这样就可以根据不同的数据库厂商构建特定的语句。比如下面的例子：

<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>

2.2 where

我们可以想象一下如果我们只要按部门编号查询某个部门的同事时，生成的 sql 语句会是怎么样的? 很容易得出结论，最终生成的 sql 就会如下：

执行后将会报 sql 语法错误。我们可以用另外一个动态标签来解决这个问题：

 <select id="selectEmployeeList" resultType="com.worldly.config.entity.Employee" databaseId="mysql">
        select
        *
        from t_emp e
        <where>
            <if test="name!=null and name!=''">
                and  e.emp_name=#{name,jdbcType=VARCHAR}
            </if>
            <if test="dep!=null">
                and e.emp_dep=#{dep.id,jdbcType=INTEGER}
            </if>
        </where>
    </select>

只要将 sql 放入 where 动态标签内，至少有一个条件符合的时候，才会插入 where 语句并且会将条件语句前的 and 去掉。

2.3 trim

常用的属性：prefix=”where”// 给第一符合条件的语句加上前缀 where prefixOverrides=”and” // 将最后一条语句的前缀 and 覆盖 suffix=”and” // 给第一符合条件的语句加上后缀 and suffixOverrides=”and”// 将最后一条语句的后缀 and 覆盖当我们把条件语句重新排版一下如下：

<select id="selectEmployeeList" resultType="com.worldly.config.entity.Employee" databaseId="mysql">
        select
        *
        from t_emp e
        <where>
            <if test="name!=null and name!=''">
                  e.emp_name=#{name,jdbcType=VARCHAR} and
            </if>
            <if test="dep!=null">
                and  e.emp_dep=#{dep.id,jdbcType=INTEGER} and
            </if>
        </where>
    </select>

然后运行，结果如下：发现动态 where 标签只会去除条件语句的第一个 and ，这时候动态 where 就解决不了这个问题了，就有了一个新的动态标签 trim动态 xml 代码

  <select id="selectEmployeeList" resultType="com.worldly.config.entity.Employee" databaseId="mysql">
        select
        *
        from t_emp e
        //表示给第一个符合条件的语句前加 where，把最后一个语句的suffixOverrides="and" 指定的and 覆盖掉
          <trim  prefix="where" suffixOverrides="and">
            <if test="name!=null and name!=''">
                  e.emp_name=#{name,jdbcType=VARCHAR} and
            </if>
            <if test="dep!=null">
                  e.emp_dep=#{dep.id,jdbcType=INTEGER} and
            </if>
          </trim>
    </select>

2.4 set

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

<update id="updateAuthorIfNecessary">
  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}
</update>

这里，set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。（译者注：因为用的是 “if” 元素，若最后一个 “if” 没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留）

2.5 choose

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。还是上面的例子，但是这次变为提供了 “title” 就按 “title” 查找，提供了 “author” 就按 “author” 查找的情形，若两者都没有提供，就返回所有符合条件的 BLOG（实际情况可能是由管理员按一定策略选出 BLOG 列表，而不是返回大量无意义的随机结果）。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

2.6 foreach

常用的属性：collection 要遍历的集合；item 要遍历的元素；index 元素在集合中的索引；open 遍历以什么开头比如 open=”and id in (“; seprator 遍历出来的元素以什么分隔；end 遍历以什么结束 end=”)” 动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

2.7 bind

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。这个动态标签可以完美解决 #{}在某些时候不适用，而用美元 {} 又有 sql 注入的风险的情况（ ${} 与 #{} 的区别）比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind  />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

2.8 insert

批量插入 mysql 与 oracle 的区别：

2.8.1 mysql 批量插入

插入语句

<insert id="insertEmp">
        insert into t_emp (id,username)
        values 
        <foreach collection="userList" item="u" separator="," open="(" close=")">
          #{u.id},#{u.username}
        </foreach>
</insert>

预编译结果

insert into t_emp (id,username)
 values(？，？),(？，？),(？，？)

你可能会想把整个插入语句进行循环如下：用；来分隔每一条插入语句

   <insert id="insertEmp">
        <foreach collection="userList" item="u" separator=";">
        insert into t_emp (id,username)
        values (#{u.id},#{u.username} )
        </foreach>
    </insert>

预编译结结果

insert into t_emp (id,username) values (?,?);
insert into t_emp (id,username) values (?,?);
insert into t_emp (id,username) values (?,?);

mysql 默认是不支持这种语法，需要在 url 后面的连接属性增加一个 allowMultiQueries=true; 该属性默认是关闭的。

2.8.2 oracle 批量插入

oracle 并不支持 mysql 这种语法

insert into t_emp (id,username) values(？，？),(？，？),(？，？)

他只能通过如下来完成插入

begin 
insert into t_emp (id,username) values(？，？); 
insert into t_emp (id,username) values(？，？); 
insert into t_emp (id,username) values(？，？); 
end;

2.9 sql

这个元素可以被用来定义可重用的 SQL 代码段，可以包含在其他语句中。它可以被静态地 (在加载参数) 参数化. 不同的属性值通过包含的实例变化. 比如：

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property /></include>,
    <include refid="userColumns"><property /></include>
  from some_table t1
    cross join some_table t2
</select>

作者：Javxuan

来源链接：

https://blog.csdn.net/u014297148/article/details/79678563