动态SQL（ if choose (when, otherwise) trim (where, set) foreach）

1.什么是动态SQL？

动态SQL就是指根据不同的条件生成不同的SQL语句
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
if choose (when, otherwise) trim (where, set) foreach

2.搭建环境

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

2.2 创建一个基础的MyBatis

2.2.1导包

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.10</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

2.2.2 编写配置文件

<?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="mapUnderscoreToCamelCase" value="true"/>
        <!--标准的日志工厂实现-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

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

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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>
        <package name="com/heng/mapper"/>
        <!-- <mapper resource="com/zj/mapper/UserMapper.xml"/>-->
        <!--      <mapper class="com.zj.mapper.UserMapper"/>-->

    </mappers>
</configuration>

driver =com.mysql.jdbc.Driver
url =jdbc:mysql://localhost:3306/mybatis?useSSL=true&userUnicode=true&characterEncoding=utf8
username=root
password=123456

2.2.3编写实体类

package com.heng.pojo;
import lombok.Data;
import java.util.Date;

/*
 *作者；${my}
 *时间；2020-02-21-23-24
 *描述：{         }
 */
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date creaTeime;
    private int views;
}

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

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

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

<?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.heng.mapper.blogmapper">

    <insert id="addBook" parameterType="Blog">
        insert into mybatis.blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{creaTeime},#{views});
    </insert>
    
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog where 1=1
        <if test="title!=null">
            and title =#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </select>

</mapper>

2.2.5插入数据

测试类插入数据

@Test
    public void test(){
        SqlSession session = MyBatisUtils.getSession();
        blogmapper mapper = session.getMapper(blogmapper.class);
        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("mybatis好难");
        blog.setAuthor("衡");
        blog.setCreaTeime(new Date());
        blog.setViews(284635654);
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("String真难");
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("Spring真好难");
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("mybatis难上难");
        mapper.addBook(blog);
         session.commit();
        session.close();
    }

IF标签（判断）

映射配置文件

    <!--if-->
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <!--where 1=1 方便测试-->
        where 1=1
        <!-- 条件if标签通过判断title参数是否为空决定是否使用这个SQL语句 -->
        <if test="title!=null">
            and title =#{title}
        </if>
        <!-- 条件if标签通过判断auther参数是否为空决定是否使用这个SQL语句 -->
        <if test="author!=null">
            and author=#{author}
        </if>
    </select>

测试类

    @Test
    public void testif(){
        SqlSession session = MyBatisUtils.getSession();
        blogmapper mapper = session.getMapper(blogmapper.class);
        HashMap map = new HashMap();
        map.put("title","mybatis好难");
        map.put("author","衡");

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

where (自动拼接删除)

 <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <!-- where 在至少一个条件下使用，如果第一个没有and 或or标签，where会自动给第一个加上，有and和or标签会自动取拼接上SQL语句 -->
            <if test="title!=null">
                 title =#{title}
            </if>
            <!-- 条件if标签通过判断auther参数是否为空决定是否使用这个SQL语句 -->
            <if test="author!=null">
                and author=#{author}
            </if>
        </where>

choose 选着(when什么时候, otherwise除此之外 )

choose选择语句，差不多等同于java语句中的switch语句。通过创建多个标签元素来选择符合要求的元素。when表示在满足某种情况下调用该内容，otherwise表示在所有情况都不满足的情况下调用的内容

 <!--Choose-->
    <select id="queryBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <!-- where 在至少一个条件下使用，如果第一个没有and 或or标签，where会自动给第一个加上，有and和or标签会自动取拼接上SQL语句 -->
        <where>
         <!--chose 选着只执行一个-->
            <choose>
                <when test="title!=null">
                    and title =#{title}
            </when>
                  <when test="author!=null">
                and author=#{author}
            </when>
                <!--otherwise 否着就执行-->
                <otherwise>
                    and views=#{views}
                </otherwise>
            </choose>
        </where>
    </select>

测试

@Test
    public void testwhen(){
        SqlSession session = MyBatisUtils.getSession();
        blogmapper mapper = session.getMapper(blogmapper.class);
        HashMap map = new HashMap();
    //  map.put("title","mybatis好难");
       // map.put("author","衡");
    map.put("views",284635654);

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

trim (where, set)

trim标签是一个格式化标签，可以用来代替set或者where标签的功能

   //更新博客
    int updateBlog(Map map);

 <!--set-->
    <update id="updateBlog" parameterType="map">
        update mybatis.blog
         <!--set 会动态的前置set关键字，同时会删除无关的逗号-->
        <set>
            <if test="title!=null">
               title =#{title},
            </if>
            <if test="author!=null">
                author=#{author}
            </if>
        </set>
        where id=#{id}
        </update>
<select id="blogs" resultType="blog">
        select * from mybatis.blog
    </select>

    @Test
    public void testwhenSet() {
        SqlSession session = MyBatisUtils.getSession();
        blogmapper mapper = session.getMapper(blogmapper.class);
        HashMap map = new HashMap();
       //  map.put("title","mybatis好难34");
         map.put("author","衡");
        map.put("id","dfdc43dd6f7e4f07af7c9a5cd1417422");
        mapper.updateBlog(map);
       
    }

foreach

接口

    //查询第1,2号记录的博客
    List<Blog> queryBlogForeach(Map map);

xml 配置

    <!--Foreach  拼接动态sql-->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

测试

@Test
    public void testForeach(){
        SqlSession session = MyBatisUtils.getSession();
        blogmapper mapper = session.getMapper(blogmapper.class);
        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);

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

动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了