mybatis的动态SQL

动态 SQL(官方描述)

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

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

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

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

通过 if, choose, when, otherwise,trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

---

=============================================================================

环境搭建

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配置文件 获取sqlSession的工具类

实体类:

package com.lx.pojo;


import java.util.Date;


public class Blog {

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


    public Blog() {
    }


    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }
}

Mapper接口:

package com.lx.dao;

import com.lx.pojo.Blog;

public interface BlogMapper {


}

mapper.xml配置文件:

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

<mapper namespace="com.lx.dao.BlogMapper">



</mapper>

获取sqlSession的工具类:

package com.lx.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

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

    //有了SqlSessionFactory ,可以从中获取SqlSession 的实例
    //SqlSession 在其中包含了面向数据库执行 SQL 命令的所有方法

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



}

配置mybatis-config核心配置文件

<?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="STDOUT_LOGGING"/>
        <!--是否开启自动驼峰命名规则（camel case）映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>


  <typeAliases>
        <package name="com.lx.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="${pwd}"/>
            </dataSource>
        </environment>
    </environments>



    <mappers>
        <mapper resource="com\lx\dao\BlogMapper.xml"/>
    </mappers>


</configuration>

---

if语句

1 . 编写接口方法:

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

2 . 编写SQL语句:

传的参数为Map类型,map中可能包含一个或多个条件,或者没有条件

正常sql语句应该是: select * from table where … 而在后面加了 1=1

使用 1=1 的目的是当没有传参数或传的参数没有在if匹配的时候保证该sql语句依然是正确的,能够查询出所有的数据 其实就是个高级点的sql语句拼接

使用if标签如果有参数匹配,凭借if里的sql语句

   <select id="queryBlogIF" parameterType="map" resultType="com.lx.pojo.Blog">


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

        <if test="author != null">
            and author =#{author}
        </if>

    </select>

3 . 测试类:

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

        HashMap map = new HashMap();

//        map.put("title","张华考进了北京大学");
//        map.put("author","我");

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

        for (Blog blog : blogs) {
            System.out.println(blog);

        }

        sqlSession.close();

    }

4 . 测试结果:

当为空参时,可以全部查出来:
只有一个条件:
两个冲突的条件,无法查出来:

---

where语句

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返
回的内容是以AND 或OR 开头的，则它会剔除掉。

修改上面的sql语句:

    <select id="queryBlogIF" parameterType="map" resultType="com.lx.pojo.Blog">
        select * from blog
        <where>
            <if test=" title != null">
                and title = #{title}
            </if>

            <if test=" author != null " >
                and author = #{author}
            </if>
        </where>
    </select>

测试结果与上述相同,where标签自动帮我们判断了 and 并决定是否抛弃它

---

choose、when、otherwise

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

1 编写接口方法:

//查询
    List<Blog> queryBlogChoose(Map map);

2 编写SQL语句:

when 当什么时 它与if的区别就是if如果有匹配都要拼接SQL语句,when只要有一个成功匹配时就不在拼接, otherwise是指当都不成立就执行什么,在这里是什么都不执行

<select id="queryBlogChoose" parameterType="map" resultType="com.lx.pojo.Blog">
        select * from blog
        <where>

            <choose>

                <when test=" title != null ">
                    and title = #{title}
                </when>

                <when test=" author != null " >
                    and author = #{author}
                </when>

                <when test=" views != null ">
                    and views = #{views}
                </when>


                <otherwise>

                </otherwise>

            </choose>


        </where>
    </select>

3 测试方法:

@Test
    public void queryblogchoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();

        //choose 只要满足第一个条件,就会忽略其他条件,它只会实现其中的一个条件

        map.put("title", "我们都有光明的前途" );
        map.put("author","张华");
        map.put("author","我们");


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

        for (Blog blog : blogs) {
            System.out.println(blog);
        }


        sqlSession.close();
    }

4 测试结果: 只查询了第一个条件,当为空参时,没有拼接字符串,执行的是select * from blog

---

trim、where、set

已经知道了where会为我们自动剔除 不需要的 and or ,使用trim定义它的功能,比如让它只剔除and

官网实例:

set

1 编写接口方法:

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

2 编写SQL语句:

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

            <if test=" author != null ">
                author = #{author},
            </if>

            <if test=" views != null ">
                views = #{views},
            </if>

        </set>
<!-- 保证SQL是正常的 加上判断条件 where id = #{id} -->
           where id = #{id}
    </update>

3 测试方法:

 @Test
    public void updateblog(){

        SqlSession sqlSession = MybatisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

//        mapper.addBlog(new Blog(IDUtils.getStringId(), "测试使用", "测试", new Date(), 0));

        HashMap map = new HashMap();

        map.put("title","测试使用3");
        map.put("views",996);
        map.put("id","f75fcf67285c4602902b707a40ea7063");

        mapper.updateBlog(map);



        sqlSession.close();
    }

4 测试结果:

---

foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候

查询 blog 表中 id 分别为1,2,3的博客信息

1 编写接口方法:

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

2 编写SQL语句:

正常的sql语句应该是 select * from blog where (id=1 or id=2 or id=3)
我们需要的就是让代码拼出来这句代码

• collection:指定输入对象中的集合属性
• item:每次遍历生成的对象
• open:开始遍历时的拼接字符串
• close:结束时拼接的字符串
• separator:遍历对象之间需要拼接的字符串

<!--
open为起始符  close为终结符  separator为拼接符
-->

    <select id="queryBlogForeach" parameterType="map" resultType="com.lx.pojo.Blog">

        select * from blog

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

3 测试方法:

 @Test
    public void queryblogforeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();

        ArrayList ids = new ArrayList();

        ids.add(1);
        ids.add(2);
        ids.add(3);

        
        map.put("ids",ids);

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

4 测试结果:

为什么传的id是integer类型 而id是varchar类型却依然能查出来,写法其实错误的,在这里查出来是因为没有其它的干扰项,mysql帮我们自动转换了,详见另一篇MySQL隐式类型转换

---

SQL片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽
取出来，然后使用时直接调用。

我觉得意义不大…

提取SQL片段：

<sql id="if-title-author">

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

<select id="queryBlogIf" parameterType="map" resultType="blog">
 select * from blog <where> 
 <!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
  <include refid="if-title-author"></include> 
  <!-- 在这里还可以引用其他的 sql 片段 -->
   </where> 
  </select>

---

动态 sql 语句的编写往往就是一个拼接的问题

---

未来补充:

---

对应文件mybatis-08