Notes

主题

10.动态 SQL ​

动态 SQL 就是根据不同的条件生成不同的 SQL 语句

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

1.搭建环境 ​

1.1 SQL ​

bash
DROP TABLE IF EXISTS `blog`;
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 '浏览量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `blog` VALUES ('0001', 'hong/My-Note', '谢禹宏', '2021-04-21 00:00:00', '100');
INSERT INTO `blog` VALUES ('0002', 'MyBatis-Plus', '苞米豆', '2021-04-21 00:00:00', '100');
INSERT INTO `blog` VALUES ('0003', 'Hello MyBatis', '母鸡', '2021-04-21 00:00:00', '120');
INSERT INTO `blog` VALUES ('0004', 'Hello Vuew', '尤雨溪', '2021-01-21 00:00:00', '100');
INSERT INTO `blog` VALUES ('0005', 'Hello Linux', '林纳斯', '2001-04-21 00:00:00', '120');

1.2 实体类 ​

java
package org.hong.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private Integer views;
}

1.3 MyBatisUtil 工具类 ​

java
package org.hong.util;

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

    private static SqlSessionFactory sqlSessionFactory;

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

    // 既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
    // SqlSession 提供了在数据库执行 SQL 命令所需的所有方法
    public static SqlSession getSqlSession(){
        // openSession(): 此方式打开SQL会话, 事务是开启状态
        // openSession(true): 此方式打开SQL会话, 事务是关闭状态
        return sqlSessionFactory.openSession();
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

1.4 jdbc.properties ​

bash
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=1234

1.5 log4j.properties ​

bash
log4j.rootLogger=DEBUG,A1

log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n

1.6 MyBatis 核心配置文件 ​

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

    <!-- 导入外部配置文件, 放在最前面 -->
    <properties resource="jdbc.properties"/>

    <settings>
        <!-- 设置日志输出, 方便观察sql语句和参数 -->
        <setting name="logImpl" value="LOG4J"/>
        <!-- 开启驼峰命名法 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--
        environments配置项目的运行环境, 可以配置多个
        default: 启用的环境
     -->
    <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>

    <!-- 每一个Mapper.xml都需要在MyBatis核心配置文件中注册!!! -->
    <mappers>
        <package name="org.hong.mapper"/>
    </mappers>

</configuration>

1.7 BlogMapper.java ​

java
package org.hong.mapper;

public interface BlogMapper {

}

1.8 BlogMapper.xml ​

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="org.hong.mapper.BlogMapper">

</mapper>

2.if ​

2.1 接口方法 ​

java
// 查询博客, 携带了哪个字段查询条件就带上这个字段的值
List<Blog> getByBlog(Blog blog);

2.2 方法映射 ​

xml
<!-- if -->
<select id="getByBlog" resultType="org.hong.pojo.Blog">
    select * from blog where
    <!-- test: 判断的表达式 (OGNL)-->
    <if test="title != null and title.trim() != ''">
        title like #{title}
    </if>
    <if test="author != null and author.trim() != ''">
        and author like #{author}
    </if>
</select>

2.3 测试用例 ​

java
@Test
public void testIf(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    blog.setTitle("%MyBatis%");
    // 这里赋值一个空字符串
    blog.setAuthor("");
    List<Blog> list = mapper.getByBlog(blog);
    list.forEach(System.out :: println);

    sqlSession.commit();
    sqlSession.close();
}

2.4 控制台打印 ​

shell
## 发送的sql并没有带上author字段
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==>  Preparing: select * from blog where title like ?
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==> Parameters: %MyBatis%(String)
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] <==      Total: 2
Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100)
Blog(id=0003, title=Hello MyBatis, author=母鸡, createTime=2021-04-21, views=120)

2.5 问题 ​

如果不给 title 赋值，SQL 就会变成这样：select * from user where and author like #{author}

这样的 SQL 明显是非法的，如何解决？

解决方案：

  1. 在 where 添加后面添加 1=1, 以后的条件都 and xxx``（不推荐, 会导致数据库性损失）
  2. MyBatis 使用 where 标签来将所有的查询条件包括在内，MyBatis 会**自动的忽略 where 后第一个不合法的 and 或 or**, 并且在**有条件的情况下自动拼接上 where**

3.where ​

Mapper 接口和测试不变，对 if 演示的方法映射进行改造！！！

3.1 方法映射 ​

xml
<!-- where -->
<select id="getByBlog" resultType="org.hong.pojo.Blog">
    select * from blog
    <where>
        <if test="title != null and title.trim() != ''">
            and title like #{title}
        </if>
        <if test="author != null and author.trim() != ''">
            and author like #{author}
        </if>
    </where>
</select>

3.2 测试用例 ​

java
@Test
public void testWhere(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    blog.setTitle("");
    blog.setAuthor("%谢%");
    List<Blog> list = mapper.getByBlog(blog);
    list.forEach(System.out :: println);

    sqlSession.commit();
    sqlSession.close();
}

3.3 控制台打印 ​

shell
## MyBatis帮我们加上了where并且忽略了where后面第一个不合法的and或or
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==>  Preparing: select * from blog WHERE author like ?
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==> Parameters: %谢%(String)
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] <==      Total: 1
Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100)

3.4 问题 ​

我们进行模糊查询时，每次给属性赋值都加上了 %%，显示的加上通配符，这样并不是很好，应该让 MyBatis 为我们加上通配符，想要完成这个功能需要使用 bind 元素。

4.bind ​

元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。通常用来拼接模糊查询

Mapper 接口和测试不变，对 where 演示的方法映射进行改造！！！

4.1 方法映射 ​

xml
<select id="getByBlog" resultType="org.hong.pojo.Blog">
    select * from blog
    <where>
        <if test="title != null and title.trim() != ''">
            <!-- bind -->
            <bind name="newTitle" value="'%' + title + '%'"/>
            and title like #{newTitle}
        </if>
        <if test="author != null and author.trim() != ''">
            <bind name="newAuthor" value="'%' + author + '%'"/>
            and author like #{newAuthor}
        </if>
    </where>
</select>

4.2 测试用例 ​

java
@Test
public void testBind(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    // 直接赋值了一个y, 没有使用通配符
    blog.setTitle("y");
    List<Blog> list = mapper.getByBlog(blog);
    list.forEach(System.out :: println);

    sqlSession.commit();
    sqlSession.close();
}

4.3 控制台打印 ​

shell
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==>  Preparing: select * from blog WHERE title like ?
## MyBatis为sql语句赋值的参数, 可以发现y的左右两边加上了%通配符
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] ==> Parameters: %y%(String)
[main] [org.hong.mapper.BlogMapper.getByBlog]-[DEBUG] <==      Total: 3
Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100)
Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100)
Blog(id=0003, title=Hello MyBatis, author=母鸡, createTime=2021-04-21, views=120)

到这里，我们的 getByBlog 方法就变得很优雅了！！！

5.set ​

set元素会动态地在行首插入 SET关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

5.1 接口方法 ​

java
// 修改, 但是只修改属性值不为null的属性
int update(Blog blog);

5.2 方法映射 ​

xml
<!-- set -->
<update id="update">
    update blog
    <set>
        <if test="title != null and title.trim() != ''">
            , title = #{title}
        </if>
        <if test="author != null and author.trim() != ''">
            , author = #{author}
        </if>
        <if test="createTime != null">
            , create_time = #{createTime}
        </if>
        <if test="views != null and views > 0">
            , views = #{views}
        </if>
    </set>
    where id = #{id}
</update>

5.3 测试用例 ​

java
@Test
public void testSet(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    blog.setId("0001");
    blog.setCreateTime(Date.valueOf("2020-1-1"));
    int update = mapper.update(blog);

    sqlSession.commit();
    sqlSession.close();
}

5.4 控制台打印 ​

shell
## mybatis帮我们加上了set关键字, 并且删除了set后面第一个不合法的逗号
[main] [org.hong.mapper.BlogMapper.update]-[DEBUG] ==>  Preparing: update blog SET create_time = ? where id = ?
[main] [org.hong.mapper.BlogMapper.update]-[DEBUG] ==> Parameters: 2020-01-01(Date), 0001(String)
[main] [org.hong.mapper.BlogMapper.update]-[DEBUG] <==    Updates: 1

6.choose{when, otherwise} ​

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。（choose --> switch,when --> case,otherwise --> default）

6.1 接口方法 ​

java
// 有id, 根据id精准匹配; 有title就根据title进行模糊查询; 如果都没有就查询author为苞米豆的blog
List<Blog> getByBlogChoose(Blog blog);

6.2 方法映射 ​

xml
<select id="getByBlogChoose" resultType="org.hong.pojo.Blog">
    select * from blog
    <where>
        <choose>
            <when test="id != null and id > 0">
                and id = #{id}
            </when>
            <when test="title != null and title.trim() != ''">
                <bind name="title" value="'%' + title + '%'"/>
                and title like #{title}
            </when>
            <otherwise>
                and author = '苞米豆'
            </otherwise>
        </choose>
    </where>
</select>

6.3 测试用例 ​

java
@Test
public void testChoose(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    // 赋值 id 和 title
    Blog blog1 = new Blog();
    blog1.setId("0001");
    blog1.setTitle("my");
    List<Blog> list1 = mapper.getByBlogChoose(blog1);
    System.out.println(list1);

    // 只赋值 title
    Blog blog2 = new Blog();
    blog2.setTitle("my");
    List<Blog> list2 = mapper.getByBlogChoose(blog2);
    System.out.println(list2);

    // 什么都不赋值
    List<Blog> list3 = mapper.getByBlogChoose(null);
    System.out.println(list3);

    sqlSession.commit();
    sqlSession.close();
}

6.4 控制台打印 ​

shell
## 给 id 和 title 属性赋值, 因为choose只会进入一个hwen, 所以查询条件只有id, 没有title
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==>  Preparing: select * from blog WHERE id = ?
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==> Parameters: 0001(String)
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] <==      Total: 1
[Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100)]
## 只给 title 属性赋值, 查询条件加上了title
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==>  Preparing: select * from blog WHERE title like ?
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==> Parameters: %my%(String)
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] <==      Total: 3
[Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100), Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100), Blog(id=0003, title=Hello MyBatis, author=母鸡, createTime=2021-04-21, views=120)]
## 什么都没给, 传了一个null, 进入了otherwise中
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==>  Preparing: select * from blog WHERE author = '苞米豆'
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] ==> Parameters:
[main] [org.hong.mapper.BlogMapper.getByBlogChoose]-[DEBUG] <==      Total: 1
[Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100)]

7.foreach ​

7.1 接口方法 ​

java
// 通过id的List集合查询多条数据
List<Blog> getByIds(List<String> ids);

7.2 方法映射 ​

xml
<select id="getByIds" resultType="org.hong.pojo.Blog">
    select * from blog
    <where>
        <if test="list != null and list.size() > 0">
            <!--
                collection: 指定遍历的集合; 只能写与集合类型对应的名字,如果想使用其他名称则必须使用@param注解指定名称
                item: 将当前遍历的元素赋值给指定的变量
                separator: 元素之间的分隔符
                open: 指定开始符号
                close: 指定结束符号
                index: 遍历List的时候是index索引, item是当前值
                       遍历Map的时候index是map的key, item是map的值
            -->
            <foreach collection="collection" item="id" open="and id in(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

7.3 测试用例 ​

java
@Test
public void testForeach(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    String []ids = new String[] {"0001", "0002", "0003"};
    List<Blog> list = mapper.getByIds(Arrays.asList(ids));
    list.forEach(System.out :: println);

    sqlSession.commit();
    sqlSession.close();
}

7.4 控制台打印 ​

shell
[main] [org.hong.mapper.BlogMapper.getByIds]-[DEBUG] ==>  Preparing: select * from blog WHERE id in( ? , ? , ? )
[main] [org.hong.mapper.BlogMapper.getByIds]-[DEBUG] ==> Parameters: 0001(String), 0002(String), 0003(String)
[main] [org.hong.mapper.BlogMapper.getByIds]-[DEBUG] <==      Total: 3
Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100)
Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100)
Blog(id=0003, title=Hello MyBatis, author=母鸡, createTime=2021-04-21, views=120)

8.Sql (抽取可重用 SQL 片段) ​

场景：在真实开发中我们不能写这样的 SQL 语句 select * from xxx，是不能写 * 的，但是每写一个查询语句都写上全部的列名，就造成了代码的冗余，而且也不易于维护。还好 MyBatis 提供了解决方案。如果表中字段发生了更改，我们只需要修改 sql 片段就 OK 了。

8.1 接口方法 ​

java
List<Blog> getAll();

8.2 方法映射 ​

xml
<!--
    sql: 抽取片段
        id: 唯一标识
 -->
<sql id="column">
    id, title, author, create_time, views
</sql>
<select id="getAll" resultType="org.hong.pojo.Blog">
    <!--
        include: 引入sql节点定义的sql片段
            refid: 引用指定id的sql片段
     -->
    select <include refid="column"/> from blog
</select>

8.3 测试用例 ​

java
@Test
public void testSql(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    List<Blog> list = mapper.getAll();
    list.forEach(System.out :: println);

    sqlSession.commit();
    sqlSession.close();
}

8.4 控制台打印 ​

shell
## mybatis为我们拼接上了列名
[main] [org.hong.mapper.BlogMapper.getAll]-[DEBUG] ==>  Preparing: select id, title, author, create_time, views from blog
[main] [org.hong.mapper.BlogMapper.getAll]-[DEBUG] ==> Parameters:
[main] [org.hong.mapper.BlogMapper.getAll]-[DEBUG] <==      Total: 5
Blog(id=0001, title=hong/My-Note, author=谢禹宏, createTime=2020-01-01, views=100)
Blog(id=0002, title=MyBatis-Plus, author=苞米豆, createTime=2021-04-21, views=100)
Blog(id=0003, title=Hello MyBatis, author=母鸡, createTime=2021-04-21, views=120)
Blog(id=0004, title=Hello Vuew, author=尤雨溪, createTime=2021-01-21, views=100)
Blog(id=0005, title=Hello Linux, author=林纳斯, createTime=2001-04-21, views=120)

Released under the MIT License.

Copyright © 2023-present fk