MyBatis-06-动态SQL

十、动态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多表查询