Mybatis_08 动态SQL

介绍

我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。

• 动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句。

1 . 环境搭建

1.1 数据库搭建

CREATE TABLE `blog`(
	`id` VARCHAR(50) NOT NULL,
	`title` VARCHAR(100) NOT NULL,
	`author` VARCHAR(20) NOT NULL,
	`create_time` DATETIME NOT NULL,
	`views` INT(20) NOT NULL
	)ENGINE=INNODB DEFAULT CHARSET=utf8;

1.2 实体类编写

• 注意：这里的创建时间与数据库的字段不对应，需要在下面的步骤中配置自动驼峰转换才能查询正常。

package com.jiu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @CLassName : Blog
 * @Description : TODO
 * @Author : 九九
 * @Date : 2021/7/11,21:08
 **/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private Long views;
}

1.3 编写核心配置文件mybatis-config.xml

• 注意自动驼峰转换的配置。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="db.properties"/>

    <settings>
        <setting name="logImpl" value="LOG4J"/>
<!--        下划线驼峰自动装换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.jiu.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/jiu/mapper/Blog.xml"/>
    </mappers>

</configuration>

1.4 编写实体类对应的接口以及mapper.xml文件

1. BlogMapper接口

package com.jiu.mapper;
/**
 *@InterfaceName : BlogMapper
 *@Description : TODO
 *@Author : 九九
 *@Date : 2021/7/11,21:11
**/
public interface BlogMapper {

}

2. BlogMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jiu.mapper.BlogMapper">

</mapper>

1.5 编写ID自动生成工具类

package com.jiu.utils;

import java.util.UUID;

/**
 * @CLassName : IDUtil
 * @Description : TODO
 * @Author : 九九
 * @Date : 2021/7/11,21:12
 **/
public class IDUtil {

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

}

1.6 向数据库插入数据

1. BlogMapper接口方法

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

2. 编写对应的xml文件

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

3. 在测试类中运行新增数据

    @Test
    public void addBlogTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDUtil.getID());
        blog.setTitle("Mybatis");
        blog.setAuthor("赵丽颖");
        blog.setCreateTime(new Date());
        blog.setViews(99999L);
        int res = mapper.addBlog(blog);
        if (res == 1){
            System.out.println("insert success.");
        }

        blog.setId(IDUtil.getID());
        blog.setTitle("SpringBoot");
        res = mapper.addBlog(blog);
        if (res == 1){
            System.out.println("insert success.");
        }

        blog.setId(IDUtil.getID());
        blog.setTitle("SpringMVC");
        res = mapper.addBlog(blog);
        if (res == 1){
            System.out.println("insert success.");
        }

        blog.setId(IDUtil.getID());
        blog.setTitle("Spring");
        res = mapper.addBlog(blog);
        if (res == 1){
            System.out.println("insert success.");
        }

        sqlSession.commit();
        sqlSession.close();
    }

2 . if语句

• 需求：根据作者名字和博客名字来查询博客！如果作者名字为空，那么只根据博客名字查询，反之，则根据作者名来查询。

1. BlogMapper接口方法

//    根据author和title查询blog,如果author为null,那么只根据title查询,反之,只根据author查询
    List<Blog> queryBlogIf(Map map);

2. 编写对应mapper.xml文件

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

3. 测试

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

        Map<String, String> map = new HashMap<>();
        List<Blog> blogs;
        map.put("author","赵丽颖");
        map.put("title","Mybatis");

        blogs = mapper.queryBlogIf(map);
        blogs.forEach(System.out::println);

        map.remove("author");
        blogs = mapper.queryBlogIf(map);
        blogs.forEach(System.out::println);

        map.put("author","赵丽颖");
        map.remove("title");
        blogs = mapper.queryBlogIf(map);
        blogs.forEach(System.out::println);
        
        map.remove("author");
        blogs = mapper.queryBlogIf(map);
        blogs.forEach(System.out::println);

        sqlSession.close();
    }

• 当author和title都不为null时，查询语句为：

select * from mybatis.blog where title = ? and author = ?

查询结果如下：

• 当author为null，但title不为null时，查询语句为：

select * from mybatis.blog where title = ? 

查询结果为：

• 当author不为null，但title为null时，查询语句为：

select * from mybatis.blog where and author = ?

显然SQL语法错误，此时查询也发生了错误：

• 当author和title都为null时，查询语句为：

select * from mybatis.blog where 

显然，同上语法错误，查询同样错误。

• 那么如何解决以上SQL语句拼接错误呢？
• where语句能为我们很好的解决这个问题。

3 . where语句

1. 修改上面编写的BlogMapper.xml文件

• 这里使用了where标签，而不是简单的添加where！

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

2. 测试

结果如下：

• 由LOG4J输出的SQL语句可以看到：“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

4 . Set语句

• 在进行更新操作时，SQL语句含有set关键词，应该使用set语句进行处理。

1. 编写接口方法

//    使用set语句更新blog
    int updateBlog(Blog blog);

2. 编写接口对应的mapper文件

• 这里添加id = #{id}是为了防止title和author同时为null时SQL拼接成update mybatis.blog where id = ?；

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

3. 测试

    @Test
    public void updateBlogTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = mapper.queryBlogByID("78dcb9a17af94798a07273e32ed0b511");
        System.out.println("before update 1.");
        System.out.println(blog);

        blog.setTitle("Mybatis从入门到精通");
        int res = mapper.updateBlog(blog);
        if (res == 1){
            System.out.println("update success 1.");
            System.out.println("after update 1.");
            System.out.println(blog);
        }

        blog.setAuthor("宣璐");
        res = mapper.updateBlog(blog);
        if (res == 1) {
            System.out.println("update success 2.");
            System.out.println("after update 2.");
            System.out.println(blog);
        }

        sqlSession.commit();
        sqlSession.close();
    }

结果如下：

5 . choose语句

• 有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose标签可以解决此类问题，类似于 Java 的 switch 语句。

1. 接口方法

    List<Blog> queryBlogChoose(Map map);

2. 编写mapper文件

    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select *
        from mybatis.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 queryBlogChooseTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,String> map = new HashMap<>();
        map.put("title","SpringMVC.");
        map.put("author","宣璐");
        map.put("views","99999");

        List<Blog> blogs = mapper.queryBlogChoose(map);
        blogs.forEach(System.out::println);

        map.remove("title");
        blogs = mapper.queryBlogChoose(map);
        blogs.forEach(System.out::println);

        map.remove("author");
        blogs = mapper.queryBlogChoose(map);
        blogs.forEach(System.out::println);

        sqlSession.close();
    }

结果如下：

6 . SQL片段

• 为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。

• 以上述select语句例子作为实例。

• 首先抽取SQL片段。这个SQL片段可以用于判断title和author是否为null，如果否则添加条件。

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

• 然后在我们的语句（select、update、delete、insert）中引用SQL片段。

    <select id="queryBlogIf" resultType="blog">
        select *
        from mybatis.blog
        <where>
            <!--<if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>-->
            <include refid="if-title-author-null"></include>
        </where>
    </select>

• 查询测试，结果同之前一致。
  
• 注意
  • 最好基于 单表来定义 sql 片段，提高片段的可重用性。
  • 在 sql 片段中不要包括 where。

7 . foreach

• 需求：我们需要查询 blog 表中 id 分别为1,2,3的博客信息。

1. 接口方法编写

	List<Blog> queryBlogForeach(Map map);

2. 编写对应的mapper文件

    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select *
        from blog
        <where>
            <if test="views != null">
                views = #{views}
            </if>
<!--        collection : 指定输入对象中的集合属性,如本例中的集合[1,2,3]
            item : 每次遍历生成的对象,如本例中的1,2,3
            本次查询的SQL语句为select * from blog where views = #{views} and (id = 1 or id = 2 or id = 3);
            循环片段为and (id = 1 or id = 2 or id = 3)
            open : 开始遍历时拼接的字符串,本例中为and (
            separator : 遍历对象之间需要拼接的字符串,本例中为or
            close : 结束时拼接的字符串,本例中为)   -->
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>;
    </select>

3. 测试

    @Test
    public void queryBlogForeachTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);
        map.put("views","99999");

        List<Blog> blogs = mapper.queryBlogForeach(map);
        blogs.forEach(System.out::println);

        sqlSession.close();
    }

结果如下：