MyBatis 学习笔记(4) 动态SQL

最新推荐文章于 2024-05-19 17:03:37 发布

张云飞VIR

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量171

点赞数

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

本文链接：https://blog.csdn.net/vir56k/article/details/118356548

版权

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 标签，<trim prefix="(" suffix=")" suffixOverrides=","> 它的意思是以 ( 开头，以 ) 结束，尾部结尾的逗号 “，”要处理。
如果尾部有了逗号，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 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>

5.参考：

我的代码示例：https://github.com/vir56k/java_demo/tree/master/mybatisdemo5_dynamic_sql

https://mybatis.org/mybatis-3/zh/dynamic-sql.html

张云飞VIR

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis 学习笔记(4) 动态SQL

1. 背景本节讲 mybatis 应对动态SQL的场景。2.知识动态SQL是指 “条件" 不固定的SQL，对比于一般的SQL，动态SQL会有一个或者多个条件/参数。比如：场景：查询的时候，如果用户输入了姓名，就按姓名模糊查询；同时又输入了邮箱，就再增加一个条件按姓名+邮箱两个条件查询。传统的拼接SQL很费力，拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。借...
复制链接

扫一扫