MyBatis-06-动态SQL

2023-12-13 15:39:28

十、动态SQL

  • 什么是动态SQL？
    • 根据给出参数的不同，通过拼接等操作，生成不同的SQL语句

10.1 构造实验环境

  • 数据库设计

    CREATE TABLE `blog` (
  `id` varchar(50) NOT NULL COMMENT ‘博客id‘,
  `title` varchar(100) NOT NULL COMMENT ‘博客标题‘,
  `author` varchar(30) NOT NULL COMMENT ‘博客作者‘,
  `create_time` datetime NOT NULL COMMENT ‘创建时间‘,
  `views` int NOT NULL COMMENT ‘浏览量‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8

  • mybatis-config.xml

    <settings>
    <!--开启日志以及自动转换驼峰命名-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

  • 实体类

    package com.pbx.pojo;

import java.util.Date;

/**
 * @author BruceXu
 * @date 2020/11/8
 */
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

  • id工具类

    package com.pbx.utils;

import java.util.UUID;

/**
 * @author BruceXu
 * @date 2020/11/8
 */
public class IDUtils {
    public static String getId() {
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

  • BlogMapper接口

    package com.pbx.mapper;

import com.pbx.pojo.Blog;
import org.apache.ibatis.annotations.Insert;

/**
 * @author BruceXu
 * @date 2020/11/8
 */
public interface BlogMapper {
    @Insert("insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views});")
    int addBlog(Blog blog);
}

  • 插入数据

    @Test
public void addBlog() {
    SqlSession session = MyBatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setId(IDUtils.getId());
    blog.setTitle("Mybatis如此简单");
    blog.setAuthor("BruceXu");
    blog.setCreateTime(new Date());
    blog.setViews(9999);

    mapper.addBlog(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("Java如此简单");
    mapper.addBlog(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("Spring如此简单");
    mapper.addBlog(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("微服务如此简单");
    mapper.addBlog(blog);

    blog.setId(IDUtils.getId());
    blog.setTitle("狂神说Java真的好");
    mapper.addBlog(blog);
    session.commit();
    session.close();
}

10.2 if

  • 这个if和高级语言里面的判断语句if是一样的功能

  • 需求

    • 根据author和title来查询博客，如果一个为空，则就按照另外一个进行查询

  • BlogMapper接口

    List<Blog> getBlog(Map map);

  • BlogMapper.xml

    <mapper namespace="com.pbx.mapper.BlogMapper">
    <select id="getBlog" parameterType="map" resultType="Blog">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>
</mapper>

  • 测试

    @Test
public void getBlog() {
    SqlSession session = MyBatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Map<String, String> map = new HashMap<>();
//        map.put("title", "Mybatis如此简单");
//        map.put("author", "BruceXu");

    List<Blog> blogList = mapper.getBlog(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    session.close();
}

    MyBatis-06-动态SQL

    MyBatis-06-动态SQL

    MyBatis-06-动态SQL

  • 如果说if中的条件有满足的话，则拼接上SQL语句如果没有满足的话，则不进行拼接

10.3 choose (when, otherwise)

  • choose的作用类似于高级语言中的switch~case语句块

  • 需求：根据author或title来查询博客，有谁查谁，如果都没有就按照views查询

  • BlogMapper接口

    List<Blog> getBlog2(Map map);

  • BlogMapper.xml

    <mapper>
    <select id="getBlog2" parameterType="map" resultType="Blog">
        select  * from blog where 1=1
        <choose>
            <when test="title != null">
                and title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = 9999
            </otherwise>
        </choose>
    </select>
</mapper>

  • 测试

    @Test
public void getBlog2() {
    SqlSession session = MyBatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Map<String, String> map = new HashMap<>();

//        map.put("title", "Java如此简单");
//        map.put("author", "BruceXu");
    List<Blog> blogList = mapper.getBlog2(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    session.close();
}

    MyBatis-06-动态SQL

    MyBatis-06-动态SQL

    MyBatis-06-动态SQL

  • 可以看到，如果when中的条件没有一个匹配上，那么拼接上otherwise中的内容。如果when中有多个匹配，那么则只拼接第一个匹配的

10.4 trim (where, set)

  • 如果仔细看上面两个测试环境中使用的SQL语句，都会发现存在这么一点 where 1=1，这个是为了保证在进行拼接的时候不出现这样的错误：select * from blog where and author = ...

  • 那么有没有这样的一种方法，能够自动判断是不是要加上and或者空格等符号以保证SQL语句的正确性？

  • trim标签，当然，MyBatis也为我们提前封装好了where和set标签

    <trim prefix="前缀匹配" prefixOverrides="前缀替换" suffix="后缀匹配" suffixOverrides="后缀替换">...</trim>

  • where和set示例

    • BlogMapper接口
    List<Blog> getBlog3(Map map);
int updateBlog(Map map);
    • BlohMapper.xml
    <mapper>
    <select id="getBlog3" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>
    
    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author},
            </if>
            <if test="view != null">
                views = #{view},
            </if>
        </set>
    </update>
</mapper>
    • 测试
    @Test
public void getBlog3() {
    SqlSession session = MyBatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Map<String, String> map = new HashMap<>();
//        map.put("title", "Mybatis如此简单");
    map.put("author", "BruceXu");
    List<Blog> blog3 = mapper.getBlog3(map);
    for (Blog blog : blog3) {
        System.out.println(blog);
    }
    session.close();
}

@Test
public void updateBlog() {
    SqlSession session = MyBatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Map<String, String> map = new HashMap<>();
    map.put("view", "6666");
    map.put("id", "ccfb770fc1b64dbf8fd7379a564dcd9f");
    map.put("title", "狂神Java说的是真的好");
    mapper.updateBlog(map);

    List<Blog> blog3 = mapper.getBlog3(map);
    for (Blog blog : blog3) {
        System.out.println(blog);
    }
    session.commit();
    session.close();
}

    MyBatis-06-动态SQL

    • 可以看到，MyBatis都帮助我们成功的完成了SQL拼接，并没有出现任何错误

10.5 SQL片段

  • 有些时候，在业务的处理过程中，可能某一部分SQL语句使用比较频繁，所以我们可以将其抽取出来，单独成为一个片段。然后在需要的使用通过 <include>标签导入即可

  • 提取SQL片段

    <sql id="if-title-author">
	<if test="title != null">
		title = #{title}
	</if>
	<if test="author != null">
		and author = #{author}
	</if>
</sql>

  • 引用SQL片段

    <select id="queryBlogIf" parameterType="map" resultType="blog">
	select * from blog
	<where>
		<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace-->
		<include refid="if-title-author" />
		<!-- 在这里还可以引用其他的 sql 片段 -->
	</where>
</select>

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

  • foreach 元素的功能非常强大，

    • 它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。
    • 它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，

  • 提示

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

MyBatis-06-动态SQL

上一篇：记一次postgresql数据导出


下一篇：MySQL多表查询