MyBatis---动态SQL

1.什么是动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。简单说动态SQL就是指根据不同的条件生成不同的SQL语句

虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
学好动态sql其实就是学好几个标签，因为创建工程的思路都是一样的，模板化套路的编程，首先导包，写全局配置文件，写实体类，然后是写实体类对应的接口和接口对应的xml文件，将接口对应的xml文件在全局配置中注册，然后测试方法。

2.xml 标签

动态SQL编程三个重要的部分：

• 实体类
• 实体类对应的接口
• 每一个接口对应的xml文件

2.1插入数据

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.导入依赖

<dependencies>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--mybatis-->

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
       <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
    </dependencies>

3.全局配置文件

<?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核心配置文件-->
<configuration>

    <!--引入外部配置文件-->
    <properties resource="db.properties"/>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰自动命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--包的别名配置-->
    <typeAliases>
        <package name="com.yang.pojo"/>
    </typeAliases>

     <!-- 一个environments 标签元素可以有多套配置 -->
    <environments default="development">
        <!-- 里面的每一个environment代表一个具体的环境 -->
        <environment id="development">
            <!--transactionManager 事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- dataSource 数据源配置 -->
            <dataSource type="POOLED">
                <!-- 连接数据库的配置i-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

4.两个资源配置文件

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
pwd=123456

log4j.properties

### 设置###
log4j.rootLogger = debug,stdout,D,E

### 输出信息到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### 输出DEBUG 级别以上的日志到=E://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = ./log/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### 输出ERROR 级别以上的日志到=E://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =./log/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout

5.实体类和工具类

实体类

@Data
public class Blog {
    private int id;
    private String title;
    private String author;
    private Date createDate;
    private int views;
}

工具类

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static{
        try {
            //使用Mybatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static SqlSession  getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }

}

public class IDutils {

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

6.接口类：

public interface BolgMapper {
 	//写方法来插入数据
    int addBlog(Blog blog); 
}

7.接口类对应的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.yang.mapper.BolgMapper">

    <insert id="addBlog" parameterType="Blog">
        insert into mybatis.blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createDate},#{views})
    </insert>
</mapper>

8.在Mybatis-config文件中进行对应绑定注册

<mappers>
<mapper namespace="com.yang.mapper.BlogMapper">
</mapper>

9.测试

@Test
public void testAddBlog() {

    SqlSession session = MyBatisUtils.getSession(true);
    BolgMapper mapper = session.getMapper(BolgMapper.class);

Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setCreateDate(new Date());
        blog.setAuthor("ly");
        blog.setTitle("mybatis");
        blog.setViews(9999);
        mapper.addBlog(blog);
        
        Blog blog1 = new Blog();
        blog1.setId(IDUtils.getId());
        blog1.setCreateDate(new Date());
        blog1.setAuthor("ly");
        blog1.setTitle("mybatis");
        blog1.setViews(9999);
		mapper.addBlog(blog1);
		
        Blog blog2 = new Blog();
        blog2.setId(IDUtils.getId());
        blog2.setCreateDate(new Date());
        blog2.setAuthor("ly");
        blog2.setTitle("mybatis");
        blog2.setViews(9999);
        mapper.addBlog(blog2);

        Blog blog3 = new Blog();
        blog3.setId(IDUtils.getId());
        blog3.setCreateDate(new Date());
        blog3.setAuthor("ly");
        blog3.setTitle("mybatis");
        blog3.setViews(9999);
         mapper.addBlog(blog3);
         
        Blog blog4 = new Blog();
        blog4.setId(IDUtils.getId());
        blog4.setCreateDate(new Date());
        blog4.setAuthor("ly");
        blog4.setTitle("mybatis");
        blog4.setViews(9999);
        mapper.addBlog(blog4);
}

每一个接口类都有一个对应的xml文件，而且需要注意的是xml文件需要配置在全局xml中

2.2 IF

在接口中写方法，然后在对应的xml中设置“作者”和“标题”搜索。
方法：

 List<Blog> getBlogByIf(Map map);

xml配置

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

测试：

@Test
    public void testgetBlogByIf() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
       // map.put("title","mybatis");
        map.put("author","ly");
        List<Blog> list = mapper.getBlogByIf(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

如果没有传入“title”，where后也没有条件，那么就会查询失败，反之若传入了“title”，那么就会对“title”一列进行模糊查找并返回 blog 结果。where title=?

也可以增加参数，“title”和“author”两个参数进行搜索，会自动拼接两个参数进行搜索where title=? and authour=?
如果我们需要拼接where后面的条件，又不希望客户端传递的错误信息，需要更加智能的where标签<where><where/>，如果有后面的判断语句，自动添加where后面的条件内容，如果后面语句开头是and或者or，它可以自动去掉。

2.3 choose (when, otherwise)

准备工作不变，然后在接口中写入方法，对应接口的xml中添加配置，测试
方法

List<Blog> queryBlogChoose(Map map);

xml配置

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

测试：

//只想查询作者为ly的数据
@Test
    public void testqueryBlogChoose() {

        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
       
        map.put("author","ly");
        List<Blog> list = mapper.queryBlogChoose(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }
    }

有时不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
思路是提供了"title"就按"title"查找，提供了"author"就按"author"查找，若两者都没有提供，就返回所有符合条件(两者都不满足会走otherwise，相当于Java-switch中的default)的结果.

2.4 trim (where,set)

准备工作不变，更新语句不用写接口方法，直接配置对应接口的xml文件，测试

xml配置

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

set 元素可以用于动态包含需要更新的列，而舍去其它的，set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。

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

测试：

@Test
public void testupdateBlog(){
    SqlSession session = MyBatisUtils.getSession(true);
    BolgMapper mapper = session.getMapper(BolgMapper.class);

    HashMap<String, String> map = new HashMap<String, String>();

    map.put("id","680b9f77c78c4429be1a9b7992da54a9");
    map.put("author","ly");
    mapper.updateBlog(map);
}

where的前身是：

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

set的前身是：

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

更新语句：where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

2.4 Foreach

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候，
foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。

xml配置

<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>
<!--查询当id=?时所有数据-->
<!--collection:集合类型；item：集合的变量名;
  open循环以什么开始;close循环以什么结束,separator:以什么分割
 -->

测试：

@Test
    public void testqueryBlogForeach() {
        SqlSession session = MyBatisUtils.getSession(true);
        BolgMapper mapper = session.getMapper(BolgMapper.class);

        HashMap<String,Object> map = new HashMap<String,Object>();
        ArrayList<String> list = new ArrayList<String>();
        list.add("6ff5e719ea004feab2f08d35ac6dd2fe");
        list.add("a79fe6f2fe96403e9a5349b2aaabded6");
        list.add("680b9f77c78c4429be1a9b7992da54a9");
        map.put("ids",list);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

    }

2.5 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 mybatis.blog
       <where>
           <include refid="if-title-author"></include>
       </where>
   </select>
   

注意事项：

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