【MyBatis】七、动态SQL

转载

mb5fd86d5f5874e 2021-07-28 23:41:00

文章标签 MyBatis sql sql语句动态sql ide 文章分类 代码人生

12、动态SQL

动态SQL的本质还是SQL语句，只是根据不同条件拼接子句。

使用动态SQL省去在 JDBC中的以下麻烦：

需要根据不同条件手动拼接 SQL 语句；
拼接时要注意添加空格，去掉最后一个列名的逗号；
......

环境搭建

注意：开启日志功能，查看SQL语句！

1、数据库表

blog

【MyBatis】七、动态SQL_动态sql

2、实体类

Blog

/**
 * ID
 */
private String id;
/**
 * 标题
 */
private String title;
/**
 * 作者
 */
private String author;
/**
 * 创建时间
 */
private Date createTime;
/**
 * 浏览量
 */
private int views;

3、工具类

编写一个IdUtils工具类，用于为博客生成随机的UUID。

public class IdUtils {

    /**
     * 生成随机UUID，作为Blog的ID
     *
     * @return 去掉-的随机UUID
     */
    public static String getUUid() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }
}

4、测试

Mapper

/**
 * 添加博客
 *
 * @param blog 待添加博客
 * @return 受影响行数
 */
int insertBlog(Blog blog);

Mapper.xml

<insert id="insertBlog">
    insert into mybatis.blog(id, title, author, create_time, views)
    values (#{id}, #{title}, #{author}, #{createTime}, #{views})
</insert>

JUnit

@Test
public void insertBlog() {
    // 获取SqlSession
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    // 获取Mapper
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog(IdUtils.getUUid(), "我的博客", "Jaywee", new Date(System.currentTimeMillis()), 7);

    // 执行方法
    int i = mapper.insertBlog(blog);

    if (i > 0) {
        System.out.println("插入" + i + "行数据");
    }

    // 提交事务！
    sqlSession.commit();

    // 关闭连接
    sqlSession.close();
}

if

通过if元素判断条件：

匹配到任意一个条件：追加相应子句；
匹配到多个条件：继续追加；
一个条件都没有匹配到：不会追加子句。

需求：查询所有BLOG。

如果有传入title，则在此基础上对title进行模糊查找；
如果有传入author，则在此基础上对author进行模糊查找。

注意：传入null，即视作没有传入参数；

1、Mapper

/**
 * 查询所有博客
 *
 * @param title  标题
 * @param author 作者
 * @return 博客列表
 */
List<Blog> listBlogsLike(@Param("title") String title, @Param("author") String author);

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where 1=1
    <if test="title != null">
        and title like concat('%',#{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%',#{author},'%')
    </if>
</select>

2、测试

@Test
public void testListBlogsLike() {
    // 获取SqlSession
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    // 获取Mapper
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    String str = ?;
    // 执行方法
    List<Blog> blogs = mapper.listBlogsLike(str);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    // 关闭连接
    sqlSession.close();
}

测试不同传参的SQL语句：

没传参（两个参数都为null）：不会追加任何条件。
传入参数（即使是空串、值为null的引用类型）：追加匹配到的子句
- 传入title
- 传入author
- 都有传参

choose (when、otherwise)

使用if元素，只要符合条件的所有语句都会被追加；
如果只是想使用其中的某一个条件，而不是所有，则选择choose元素，类似Java中的switch-case语句；
通过when元素判断条件：
- 匹配到任意一个条件：追加对应子句；
- 匹配到多个条件：看Mapper中的条件先后顺序，追加第一个匹配到的。
- 一个条件都没有匹配到：
  - 有otherwise元素：追加otherwise元素中的子句；
  - 没有otherwise元素：不追加任何子句。

需求：查询所有BLOG。

如果有传入title和author中任意一个参数，根据参数匹配条件并追加子句。

1、Mapper

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where
    <choose>
        <!-- author写在title前面，比title优先接收参数 -->
        <when test="author != null">
            author like concat('%',#{author},'%')
        </when>
        <when test="title != null">
            title like concat('%',#{title},'%')
        </when>
        <otherwise>
            author = 'Jaywee'
        </otherwise>
    </choose>
</select>

2、测试

测试不同传参的SQL语句：

没传参（两个参数都为null）：因为有otherwise元素，追加其子句。
传入参数（即使是空串、值为null的引用类型）：追加匹配到的子句
- 传入title
- 传入author
- 都有传参：实际只传递了第一个匹配到的条件所接收的元素。Mapper中author写在title的前面，所以author接收到了参数。

trim(where、set)

1、问题引入

在以上例子中，我们在原本的SQL语句中事先加入了where 1=1的条件，方便匹配条件后追加and语句。但如果不添加这个条件，会出现以下情况：

去掉where 1=1

如果只匹配了第二个语句，SQL语句为select * from blog and author like concat('%',#{author},'%') ，显然不合法。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    <if test="title != null">
        where title like concat('%',#{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%',#{author},'%')
    </if>
</select>

去掉1=1，保留where

如果没有匹配的条件，SQL语句为select * from blog where；
如果只匹配了第二条，SQL语句为select * from blog where and author like concat('%',#{author},'%')，显然不合法。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where 
    <if test="title != null">
        title like concat('%',#{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%',#{author},'%')
    </if>
</select>

2、where

使用where元素，解决以上问题。

匹配到至少一个条件时，才会在添加where子句；
没有匹配到任何条件时，不会添加where子句；
会将子句的开头的 “AND” 或 “OR”去除，不会出现where and ...的情况；

注意：不会为子句开头的添加 “AND” 或 “OR”，需要自己加上。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    <where>
        <if test="title != null">
            and title like concat('%',#{title},'%')
        </if>
        <if test="author != null">
            and author like concat('%',#{author},'%')
        </if>
    </where>
</select>

3、set

使用set元素：

自动在行首添加SET关键字；
删除列赋值时的多余逗号。

/**
 * 更新博客
 *
 * @param map 参数列表
 * @return 受影响行数
 */
int updateBlog(Map<String, Object> map);

<update id="updateBlog">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
    </set>
    where id =#{id}
</update>

4、自定义trim

trim元素有4个属性：

prefix（前缀）：在行首自动添加prefix中的文本值；
prefixOverrides（前缀覆盖）：如果行首为prefixOverrides中的文本值，自动去除；
suffix（后缀）：在行尾自动添加suffix中的文本值；
suffixOverrides（后缀覆盖）：如果行尾为suffixOverrides中的文本值，自动去除。

例：自定义trim元素，实现与set元素定价的功能。

在行首自动添加SET；
自动去除行尾的逗号。

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

foreach

foreach用于对集合进行遍历（尤其是在构建 IN 条件语句的时候）

包含以下属性：

集合（collection）：使用不同集合对象，有以下不同
- 可迭代对象或数组：index 是当前迭代的序号，item 是本次迭代获取到的元素；
- Map 对象：index 是键，item 是值。
集合项（item）
索引（index）
开头（open）字符串
结束（close）字符串
集合项迭代之间的分隔符（separator）。

过程描述：

遍历collection集合，遍历到的每一项作为item。自动添加开头和结束字符串，以及为每个item之间添加分隔符。

1、Mapper

/**
 * 查询特定ID的博客
 *
 * @param map 参数
 * @return 博客列表
 */
List<Blog> listBlogsIdIn(Map<String, Object> map);

<select id="listBlogsIdIn" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="idList" item="id"
                 open="id in(" separator="," close=")">
            #{id}
        </foreach>
    </where>
</select>

2、测试

通过Map传递方法参数，传递一个ArrayList作为要遍历的集合，通过Key=idList标识；

（为了便于理解，可以将接口方法的参数修改为ArrayList，直接传递ArrayList作为集合）

@Test
public void testListBlogsIdIn() {
    // 获取SqlSession
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    // 获取Mapper
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    // 执行方法
    ArrayList<Integer> idList = new ArrayList<>();
    // 测试：idList.add(....);

    HashMap<String, Object> map = new HashMap<>();
    map.put("idList",idList);

    List<Blog> blogs = mapper.listBlogsIdIn(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    // 关闭连接
    sqlSession.close();
}

测试不同传参的SQL语句：

ArrayList中没有值：不会追加任何条件。
ArrayList中添加值：追加匹配到的子句。
- 传入一个值：
- 传入多个值：

SQL

使用SQL标签抽取出公共的SQL片段；
在需要引用的地方通过include引用；

注意：

由于多表查询具有动态性和不确定性，最好基于单表查询编写SQL；
不要带有where子句。

例如：

<sql id="if_title_and_author_not_null">
    <if test="title != null">
        and title like concat('%',#{title},'%')
    </if>
    <if test="author != null">
        and author like concat('%',#{author},'%')
    </if>
</sql>

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    <where>
        <include refid="if_title_and_author_not_null"/>
    </where>
</select>

动态SQL总结：

本质还是SQL语句，只是可以在SQL层面执行逻辑代码；
建议现在SQLYog等可视化工具中编写SQL语句，再根据语法修改成动态SQL。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：【Java Web】Maven环境搭建

下一篇：Sping：五、自动装配Bean和使用注解开发

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯