MyBatis动态SQL

可爱的阿祖

已于 2022-05-31 23:33:31 修改

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MyBatis 文章标签： sql 数据库 mybatis

于 2022-01-25 00:25:03 首次发布

本文链接：https://blog.youkuaiyun.com/qq_36517313/article/details/122677597

MyBatis 专栏收录该内容

8 篇文章

订阅专栏

文章目录

- 动态sql

动态sql

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

sql片段

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

比如：我们将经常查询的列，设置成sql片段，通过include标签引用他就行了

 <sql id="base_column">
       id, username, email, addr, cre_time
 </sql>
  
<select id="findById" parameterType="Integer" resultType="User">
    select <include refid="base_column"/> from t_user where id = #{id}
</select>

有时候，当我们做连接查询，一般都会给各个表取个别名，防止等下列名冲突，那我们sql片段就可以这样设置：通过inclue标签的元素property，为变量alias赋值

<sql id="base_column">
    ${alias}.id,  ${alias}.username,  ${alias}.email,  ${alias}.addr,  ${alias}.mobile,  ${alias}.status
</sql>

<select id="findById" parameterType="Integer" resultType="User">
        select
        <include refid="base_column">
            <property name="alias" value="t"></property>
        </include>
        from t_user t where t.id = #{id}
</select>

查询sql: select t.id, t.username, t.email, t.addr, t.mobile, t.status from t_user t where t.id = ?

最后, 也可以在 include 元素的 refid 属性或内部语句中使用属性值，例如：

<sql id="sometable">
    ${prefix}Table
</sql>

<sql id="someinclude">
    from
    <include refid="${include_target}"/>
</sql>

<select id="select" resultType="map">
    select
    field1, field2, field3
    <include refid="someinclude">
        <property name="prefix" value="Some"/>
        <property name="include_target" value="sometable"/>
    </include>
</select>

if

if语句跟java代码中的if类似，主要用于对参数进行判断，当满足test表达式时，才加入查询条件，一般会配合where进行使用

/**
 * 根据用户名和手机号模糊查询
 *
 * @param username 用户名
 * @param mobile
 * @return list
 */
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile);

<select id="getUserList" resultType="User">
    select * from t_user t
    where 1 = 1
    <if test="username != null and username != ''">
    	and instr(t.username, #{username}) > 0
    </if>
    <if test="mobile != null and mobile != ''">
    	and instr(t.mobile, #{mobile}) > 0
    </if>
</select>

where

上面的if，我们在where后面加了1=1，是为了防止语句错误，当if条件都不满足时，或者只匹配第二个if， sql是这样的:

select * from t_user t where,
select * from t_user t where and instr(t.mobile, #{mobile}) > 0

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

/**
 * 根据用户名和手机号模糊查询
 *
 * @param username 用户名
 * @param mobile
 * @return list
 */
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile);

<select id="getUserList" resultType="User">
    select * from t_user t
    <where>
        <if test="username != null and username != ''">
            instr(t.username, #{username}) > 0
        </if>
        <if test="mobile != null and mobile != ''">
            and instr(t.mobile, #{mobile}) > 0
        </if>           
    </where>
</select>

但是注意一点，删除语句尽量不要使用,比如下面例子: 当条件不满足时，直接删表了

<delete id="deleteById" parameterType="Integer">
    delete from t_user
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
    </where>
</delete>

正常语句就行了: 当id为空时， delete from t_user where id = null，一般我们数据库也不会让主键为空的

<delete id="deleteById" parameterType="Integer">
	delete from t_user where id = #{id}
</delete

trim

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

prefix: 指定插入的内容
prefixOverrides 属性会忽略通过管道符分隔的文本序列，比如前缀多余的and或者or
suffixOverrides属性会过滤多余的后缀，比如更新语句时多余的逗号

//等价于where元素
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

//等价于set元素
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

<select id="getUserList" resultType="User">
    select * from t_user t
   <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="username != null and username != ''">
            instr(t.username, #{username}) > 0
        </if>
        <if test="mobile != null and mobile != ''">
            and instr(t.mobile, #{mobile}) > 0
        </if>           
    </trim>
</select>

<update id="updateById" parameterType="User">
    UPDATE t_user
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">
            username = #{username},
        </if>
        <if test="mobile != null">
            mobile = #{mobile}, 
        </if>
    </trim>
    WHERE id = #{id}
</update>

choose、when、otherwise

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


/**
 * 获取用户集合
 *
 * @param username 用户名
 * @param mobile
 * @param userType 用户类型
 * @return list
 */
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile,
                       @Param("userType") String userType);

<select id="getUserList" resultType="User">
    select * from t_user t
    <where>
        <if test="username != null and username != ''">
            instr(t.username, #{username}) > 0
        </if>
        <if test="mobile != null and mobile != ''">
            and instr(t.mobile, #{mobile}) > 0
        </if>
        <choose>
            <when test="userType != null and userType == 'superAdmin'">
                and t.status = 1
            </when>
            <when test="userType != null and userType == 'admin'">
                and t.status = 2
            </when>
            <otherwise>
                and t.status = 3
            </otherwise>
        </choose>
    </where>
</select>

set

用于更新语句，主要是用于去除后缀的逗号

int updateById(User user);

<update id="updateById">
  update t_user
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="email != null">email=#{email},</if>
      <if test="addr != null">addr=#{addr},</if>
      <if test="mobile != null">mobile=#{mobile}</if>
    </set>
  where id = #{id}
</update>

foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候), 可以支持数组、List、Set接口。

collection: 参数名称，这里注意一下，要用@Param定义一下，不然会报错：Parameter 'ids' not found. Available parameters are [collection, list]
item: 循环中当前的元素
index: 当前元素在集合的位置下标。
open和 close配置的是以什么符号将这些集合元素包装起来
separator是各个元素的间隔符。

批量查询

/**
 * 根据主键批量查询
 * @param ids 主键集合
 * @return list
 */
List<User> getUserListByIds(@Param("ids") List<Integer> ids);

<sql id="base_column">
    id, username, email, addr, mobile, status
</sql>

<select id="getUserListByIds" resultType="User">
    select <include refid="base_column" /> from t_user
    where id in
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

对应的查询sql：

select id, username, email, addr, mobile, status from t_user where id in ( ? , ? )

批量插入

数据库id设置自增了

/**
 * 批量插入
 * @param users 集合
 * @return 影响条数
 */
int batchInsert(@Param("users") List<User> users);

<insert id="batchInsert">
    insert into t_user(username, email, addr, mobile, status)
    values
    <foreach collection="users" item="user" separator=",">
        (#{user.username}, #{user.email}, #{user.addr}, #{user.mobile}, #{user.status})
    </foreach>
</insert>

对应sql: 这里插入2条数据

insert into t_user(username, email, addr, mobile, status) values (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?)

这里推荐一篇文章：MyBatis批量插入几千条数据慎用foreach

script

简单了解就行，要在带注解的映射器接口类中使用动态 SQL，可以使用 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);

bind

简单了解就行，bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

多数据库支持

简单了解就行，如果配置了 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>