MyBatis学习笔记-12-动态SQL

动态SQL.

简单理解，根据不同的条件生成不同的SQL语句

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

• 借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

• 标签元素

  • 1if
  • 2choose(when,otherwise)
  • 3trim(where,set)
  • 4foreach

1、搭建环境.

（已配置好mybatis-config.xml配置文件，写好MybatisUtils.java工具类）

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.创建实体类

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

3.创建Mapper接口

public interface BlogMapper {
}

4.创建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="dao.BlogMapper">


</mapper>

5.绑定Mapper到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">
        <property name="password" value="123456"/>
    </properties>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
        <!--开启驼峰和下滑线的转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <environments default="development">
        <environment id="oracle">
            <transactionManager type="MENAGED"></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>
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
                    <property name="username" value="root"/>
                    <property name="password" value="mysql"/>
                </dataSource>
        </environment>

    </environments>
    <mappers>
        <mapper resource="dao/BlogMapper.xml" />
    </mappers>

</configuration>

2、标签元素.

1. if.

接口方法添加.

List<Blog> getBlogByIF(Map<String, Object> map);

BlogMapper.xml添加SQL语句，使用 if 标签.

    -- 使用1=1 是避免 没有条件是的 where不能单独存在
    select * from mybatis.blog where 1=1
    -- if 标签 当满足执行条件时，才添加 需要的SQL语句
    -- test : 执行条件
       
        and title = #{title}
    
    
        and author = #{author}
    

测试方法.

@Test
public void getBlogByIf(){
    try(SqlSession sqlSession  = MybatisUtils.getSqlSession();) {
        BlogMapper userDao = sqlSession.getMapper(BlogMapper.class);

        Map<String, Object> map = new HashMap<>();
        //map.put("title","如何学好mybatis");
        map.put("author", "lmk");

        List<Blog> list = userDao.getBlogByIF(map);

        list.forEach(System.out::printlWn);
    }
}

结果展示.

2.choose （when，otherwise）.

接口方法添加.

List<Blog> getBlogByChoose(Map<String, Object> map);

BlogMapper.xml添加SQL语句，使用choose标签 （choose 相当于 switch ，when 相当于 case ，otherwise相当于 default）.

<!-- choose 标签的使用 -->
<select id="getBlogByChoose" resultType="pojo.Blog">
    -- choose 只能添加一条sql语句 默认 是 otherwise
    select * from mybatis.blog  where 1=1
    <choose>
        <when test="title != null">
            and title=#{title}
        </when>
        <when test="author != null">
            and author=#{author}
        </when>
        <otherwise>
            and views=#{views}
        </otherwise>
    </choose>
</select>

测试方法.

@Test
public void getBlogByChoose(){
    try(SqlSession sqlSession  = MybatisUtils.getSqlSession();) {
        BlogMapper blogDao = sqlSession.getMapper(BlogMapper.class);

        Map<String, Object> map = new HashMap<>();
        map.put("views","100");
        List<Blog> list = blogDao.getBlogByChoose(map);

        list.forEach(System.out::println);

    }
}

结果显示.

3.where.

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除

接口方法添加.

List<Blog> getBlogByWhere(String author);

BlogMapper.xml添加SQL语句，使用where标签.

<select id="getBlogByWhere" resultType="pojo.Blog">
    -- 使用where 标签后 如果没有连接的sql语句，则where不做任何变化；
    -- 如果有连接的sql语句，且第一个语句是以and开头，则他会自动添加where，并删掉第一个语句的and
    select * from mybatis.blog
    <where>
        <if test="author != null">
            and author=#{author}
        </if>
    </where>
</select>

测试方法.

@Test
public void getBlogByWhere(){
    try(SqlSession sqlSession  = MybatisUtils.getSqlSession();) {
        BlogMapper blogDao = sqlSession.getMapper(BlogMapper.class);

        String author = "lmk";
        List<Blog> list = blogDao.getBlogByWhere(author);

        list.forEach(System.out::println);

    }
}

结果显示.

4.set（用于update）.

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

接口方法添加.

int updateViewsBySet(Map<String, Object> map);

BlogMapper.xml添加SQL语句，使用set标签.

<update id="updateViewsBySet" parameterType="map">
    -- 如果有连接语句 set标签会自动添加set ,去掉最后的连接语句的逗号
    update mybatis.blog
    <set>
        <if test="title!=null">
            title=#{title},
        </if>
        <if test="author!=null">
            author=#{author},
        </if>
        <if test="views!=null">
            views=#{views},
        </if>
    </set>
    where id=#{id}
</update>

测试方法.

@Test
public void getBlogBySet(){
    try(SqlSession sqlSession  = MybatisUtils.getSqlSession();) {
        BlogMapper blogDao = sqlSession.getMapper(BlogMapper.class);

        Map map = new HashMap<>();
        map.put("views","300");
        map.put("id","1");
        int i = blogDao.updateViewsBySet(map);
        //提交事务
        sqlSession.commit();
        if(i == 0){
            System.out.println("失败");
        }else{
            System.out.println("成功");
        }
    }
}

结果显示.

5.trim（定制化 where 、set …）.

prefix表示 需要动态添加的 关键字

prefixOverrides表示 去掉符合其值的连接语句的前缀

suffixOverrides表示 去掉符合其值的连接语句的后缀

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

<!--等价于-->

<where>
    ...
</where>

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

<!--等价于-->

<set>
    ...
</set>

6.foreach.

用于对集合的遍历（尤其是在构建IN条件语句的时候）

https://www.cnblogs.com/man-tou/p/11343800.html

接口方法添加.

List<Blog> getBlogByForeach(List<Integer> list);

BlogMapper.xml添加SQL语句，使用foreach标签.

<select id="getBlogByForeach" parameterType="list" resultType="pojo.Blog">
    select * from mybatis.blog
    where id in
    <foreach item="id"  collection="list"
             open="(" separator="," close=")">
        #{id}
    </foreach>
    -- item: 本次迭代获取的对象 值(Map)
    -- index: 键（Map）
    -- collection 集合 （类型可以是 list，set，map ，array）
    -- open:左边字符 close:右边字符 separator:分隔符
</select>
<!--
    注意：parameterType 与 collection 需要对应(不管你传入的是是什么名字
    以本次foreach为例，传入的类型是 List 变量名是 ids，根据对应关系，List === list，那么collection = list)
    list  ====== list
    map  ====== map的key
    Object[] ====== array   item:对象 item.属性名
    对象 ====== 对象里面的集合字段名（List类型）
-->

测试方法.

@Test
public void getBlogByForeach(){
    try(SqlSession sqlSession = MybatisUtils.getSqlSession();){
        BlogMapper blogDao = sqlSession.getMapper(BlogMapper.class);

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

        List<Blog> list = blogDao.getBlogByForeach(ids);
        list.forEach(System.out::println);
    }
}

结果显示.

3、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、set标签

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