动态SQL

什么是动态SQL？
指根据不同条件生成不同SQL语句

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

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;

创建基础工程

1、导包
2、编写配置文件
3、编写实体类

private int id;
private String title;
private String author;
private Date createTime;
private int views;
}

4、编写实体类对应Mapper接口和Mapper.xml文件

<BlogMapper>文件

public interface BlogMapper {
    //插入数据
    int addBook(Blog blog);
}

<BlogMapper.xml>文件

<mapper namespace="com.god.dao.BlogMapper">
    <insert id="addBook" parameterType="blog">
        insert into blog(id,title,author,create_time,views)
        values(#{id},#{title},#{author},#{createTime},#{views})
    </insert>
</mapper>

<IdUtils>文件

@SuppressWarnings("all")//抑制警告
public class IdUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replace("-","");
    }
}

<MyTest>文件

public class MyTest {

    @Test
    public void addInitBlog(){

        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();

        blog.setId(IdUtils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("狂神说");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBook(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Java");
        mapper.addBook(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Spring");
        mapper.addBook(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("微服务");
        mapper.addBook(blog);

        sqlSession.close();
    }
}

IF

<BlogMapper.java>


   //查询博客
    List<Blog> queryBlogIF(Map map);

<BlogMapper.xml>


    <select id="queryBlogIF" 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>

<MyTest.java>


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

        HashMap map = new HashMap();
        map.put("title","Spring");

        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose (when, otherwise)

<BlogMapper.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>

trim (where, set)

<BlogMapper.xml>文件

where

 <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>
        </where>
    </select>

set

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

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

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

所谓的动态sql，本质还是sql语句，只是可以在sql层面，去执行一个逻辑语句

SQL片段

有时会把一些功能的部分抽取出来，方便复用
1、使用SQL标签抽取公共的部分

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

2、在需要使用的地方使用include标签引用即可

  <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

注意事项：

• 最好基于单表来定义SQL片段
• 不要存在where标签

Foreach

官方文档：

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

<BlogMapper.xml>

<!-- select * from blog where 1=1 and (id=1 or id=2 or id=3)

    现在传递一个万能的map，这个map中可以存在一个集合-->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id =#{id}
            </foreach>
        </where>
    </select>

<MyTest.java>

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


        HashMap map = new HashMap();


        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);

        map.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

动态SQL就是在拼接SQL语句，只要保证SQL的正确性，按照SQL的格式，排列组合就好了
建议：

• 先在Mysql中写出完整SQL，再对应的修改动态SQL实现通用