Mybatis——动态SQL

Mybatis——动态SQL

动态SQL本质上还是SQL，只不过加了些逻辑判断，可以根据不同条件拼接 SQL 语句.

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

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(30) NOT NULL COMMENT '浏览量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

搭建环境：

• 导包
• 编写配置文件
• 编写实体类
• 编写实体类对应Mapper接口和Mapper.XML文件

1 添加数据

（1）接口：

package com.glp.dao;

public interface BlogMapper {
    int addBlog(Blog bolg);
}

（2） Mapper.xml

  <insert id="addBlog" parameterType="blog">
        insert into blog (id, title, author, create_time, views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>

(3) UUID生成Id

package com.glp.utils;

public class IDUtil {
    public static String genId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

（4） 测试

    @Test
    public void getTeacher2(){
        SqlSession sqlSession = MyUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IDUtil.genId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("ROCK");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        int ret = mapper.addBlog(blog);
        System.out.println(ret);

        sqlSession.close();
    }

2 IF

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分.

（1） 接口

    List<Blog> queryBlogIf(Map map);

（2）Mapper.xml

 <select id="queryBlogIf" parameterType="map" resultType="blog">
        select * from blog where
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>

当没有传参时查询全部内容，当传title或者author时，再增加条件查询，达到了一条select复用的效果。

（3） 测试

@Test
    public void testQueryBlogIf(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","Mybatis如此简单");
        map.put("author","Rock");
        List<Blog> blogs = mapper.queryBlogIf(map);

        System.out.println(blogs);

        session.close();
    }

如果title为空，那么查询语句为 select * from user where and author=#{author}，这是错误的 SQL 语句，需要用下面的<where>标签来解决。

3 Where

（1）接口

   List<Blog> queryBlogWhere(Map map);

(2) Mypper.xml

  <select id="queryBlogWhere" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>

where 元素只会在子元素返回内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将“AND” 或 “OR”去除。

（3）测试

  @Test
    public void testQueryBlogWhere(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","Mybatis如此简单");
        map.put("author","Rock");
        List<Blog> blogs = mapper.queryBlogWhere(map);

        System.out.println(blogs);

        session.close();
    }

查询结果：
当传入两个参数时也会自动拼接。

4 choose

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。
传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找, 即只选择其中一个条件进行查找。 类似于switch语句。
(1) 接口

List<Blog> queryBlogChoose(Map map);

（2）Mapper.xml

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

(3) 测试

  @Test
    public void testQueryBlogChoose(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, Object> map = new HashMap<String, Object>();
        //map.put("title","Java如此简单");
        map.put("author","hah");
        map.put("views",9999);
        List<Blog> blogs = mapper.queryBlogChoose(map);

        System.out.println(blogs);

        session.close();
    }

查询结果，按传入map的第一个字段进行查找。

5 set

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号。所以当set后面的需更新字段缺省时，会报错。
(1) 接口

 int updateBlog(Map map);

（2）Mapper.xml

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

(3) 测试

  @Test
    public void testUpdateBlog(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","动态SQL");
        map.put("author","秦疆");
        map.put("id","9d6a763f5e1347cebda43e2a32687a77");

        mapper.updateBlog(map);

        session.close();
    }

6 trim（了解）

<trim> 元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是 prefix 和 suffix。

可以把包含 内容的首部 某些内容覆盖，即忽略，也可以把 尾部的某些内容 覆盖，对应的属性是 prefixOverrides 和 suffixOverrides。正因为 <trim> 元素有这样的功能，所以也可以非常简单地利用 <trim> 来代替 <where> 元素的功能。

可以通过自定义 trim 元素来定制 where 、set元素的功能.

where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

与 set 元素等价的自定义 trim 元素为：

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

7 SQL片段提取

将Mapper.xml中公共的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="queryBlogWhere" parameterType="map" resultType="blog">
        select * from blog
        <where>
           <include refid="if-title-author"/>
        </where>
    </select>

测试：

    @Test
    public void testQueryBlogWhere(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","Mybatis如此简单");
        List<Blog> blogs = mapper.queryBlogWhere(map);

        System.out.println(blogs);

        session.close();
    }

注意：

• SQL片段中最好不要包含Where标签
• 不要掺杂多个表的sqL

8 foreach

将数据库中前三个数据的id修改为1,2,3；我们需要查询 blog 表中 id 分别为1,2,3的博客信息。
(1) 接口

  List<Blog> queryBlogForeach(Map map);

（2）Mapper.xml

<select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

我们的目的是拼接
：select * from blog where 1=1 and (id=1 or id=2 or id=3)

        <where>
            <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>

比较智能所以我们可以不用写 where 1=1 ；而后面的集合，我们可以进行拼接。

collection: 指定输入对象中的集合属性,即我们定义的一个集合 List<Integer> ids = new ArrayList<Integer>();

item: 每次遍历集合时生成的一个对象

open: 拼接起始的字符串

close: 拼接结束时的字符串

separator: 遍历对象之间需要插入的分隔符

(3) 测试

 @Test
    public void testQueryBlogForeach(){
        SqlSession session = MyUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);

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

        System.out.println(blogs);

        session.close();
    }

注意：
使用动态SQL最好先将原生的SQL写出来，然后再用动态SQL进行拼接。