阅读 85

MyBatis 学习笔记(4) 动态SQL

1. 背景

本节讲 mybatis 应对动态SQL的场景。

2.知识

动态SQL是指 “条件" 不固定的SQL,对比于一般的SQL,动态SQL会有一个或者多个条件/参数。

比如:场景:查询的时候,如果用户输入了姓名,就按姓名模糊查询;同时又输入了邮箱,就再增加一个条件按姓名+邮箱两个条件查询。

传统的拼接SQL很费力,拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。

借助功能强大的基于 OGNL 的表达式,MyBatis 3 大大精简了元素种类,有下列这些:

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

3. 示例

3.1 动态查询条件 ( where 后多个 and 条件 )

场景:查询的时候,如果用户输入了姓名,就按姓名模糊查询;同时又输入了邮箱,就再增加一个条件按姓名+邮箱两个条件查询。

    <select id="selectDynamic" resultType="cn.zyfvir.SysUser">
        select * from sys_user
        <where>
            <if test="userName != null">
                user_name like #{userName}"%"
            </if>
            <if test="userEmail != null">
                and user_email like #{userEmail}"%"
            </if>
        </where>
    </select>
复制代码
  • 用了 like 语句的模糊查询 :user_name like #{userName}"%"
  • 第二个条件要写上 and 开头。
  • 如果没有第一个条件,mybatis 会自己处理 去掉 and 开头的字符。

对应的java代码示例:

    private static void testSelect(SqlSessionFactory sqlSessionFactory) {
        try (SqlSession session = sqlSessionFactory.openSession(true)) {
            SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class);
            // 仅一个参数
            SysUser para = new SysUser();
            para.userName = "jack";
            List<SysUser> list1 = sysUserMapper.selectDynamic(para);
            printf("list=%s", list1.size());
            
            //两个参数都有
            SysUser para2 = new SysUser();
            para2.userName = "jack";
            para2.userEmail = "jack";
            List<SysUser> list2 = sysUserMapper.selectDynamic(para2);
            printf("list=%s", list2.size());

            // 只有 email
            SysUser para3 = new SysUser();
            para3.userEmail = "jack";
            List<SysUser> list3 = sysUserMapper.selectDynamic(para3);
            printf("list=%s", list3.size());
        }
    }
复制代码

3.2 动态的 update

场景:当 sys_user 的 用户名有值(不是 Null )时,就把用户名改了。如果 email 有值,就同时修改 email。示例:

    <update id="updateUserDynamic">
        UPDATE sys_user
        <set>
            <if test="userName != null">
                user_name= #{userName},
            </if>
            <if test="userPassword != null">
                user_password= #{userPassword},
            </if>
            <if test="userEmail != null">
                user_email=#{userEmail},
            </if>
            <if test="userInfo != null">
                user_info= #{userInfo}
            </if>
        </set>
        WHERE id=#{id}
    </update>

复制代码
  • 使用了 set 标签配合 if 标签使用。
  • 注意 user_email=#{userEmail}, 它的末尾有 逗号“ , ” 当它是最后一个时也不会出错,mybatis 会自己处理掉这些逗号。

3.3 动态的 insert

场景:如果 sys_user 对象的 名称有值,就插入,如果 邮箱地址有值,也插入。示例是这样的:

  <!-- 插入后获得自增的主键 -->
    <insert id="insertDynamic" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="userName != null">
                    user_name,
                </if>
                <if test="userPassword != null">
                    user_password,
                </if>
                <if test="userEmail != null">
                    user_email,
                </if>
                <if test="userInfo != null">
                    user_info,
                </if>
            </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null">
                #{userName},
            </if>
            <if test="userPassword != null">
                #{userPassword},
            </if>
            <if test="userEmail != null">
                #{userEmail},
            </if>
            <if test="userInfo != null">
                #{userInfo}),
            </if>
        </trim>
    </insert>
复制代码
  • 使用了 trim 标签, 它的意思是 以 ( 开头,以 ) 结束,尾部结尾的 逗号 “,”要处理。
  • 如果尾部有了逗号,mybaits 会自动处理(删除掉)确保sql语法合规。

3.4 批量插入

场景:假设有一个 list 集合,里面有很多元素,选择一个快速的方式插入到数据库。 我们可以使用 foreach 标签来生成一个 动态插入的SQL,示例:

    <insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user (user_name,user_password) values
        <foreach collection="list" item="item" separator=",">
            ( #{item.userName}, #{item.userPassword} )
        </foreach>
    </insert>
复制代码
  • 这个示例使用了 foreach 遍历了集合,每个子元素用 item 名称代替,每个段有 逗号 分割。
  • 注意 集合的 子元素数量不能为空,为空则语句不完整,会报错。

3.5 使用 foreach 拼装一个 in 的嵌套子查询。

场景:一个常见使用场景是对集合进行遍历(尤其是在构建 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>
复制代码
  • foreach 遍历集合,生成了一个 ( 开头,)结束,逗号分隔的字符串。

4. 扩展

4.1 IF 标签的使用示例

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>
复制代码

和 where 配合使用的示例:

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>
复制代码

4.2 choose 标签的示例

示例展示了:传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured=1 的 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>
复制代码

4.3 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>
复制代码

4.4 foreach 标签的示例

动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:

SELECT * FROM POST P WHERE ID in #{item}

5.参考:

我的代码示例:github.com/vir56k/java…

mybatis.org/mybatis-3/z…

文章分类
后端
文章标签