真正的Mybatis动态sql — MyBatis Dynamic SQL

13,355 阅读8分钟

现状

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

提到Mybatis动态Sql,多数人瞬间想到的画面是这样的

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

或者像这样

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

Mapper XML 中借助 动态 SQL 元素 来实现SQL语句的条件拼接。

不得不说Mybatis的这个实现已经非常强大了,大大提高了我们拼接SQL的效率。 但是对于程序员来说,在代码中进行条件判断远比在XML中更自如,更灵活,更有底气,我们更希望在享受 强大的 MyBatis 映射 的同时,让动态SQL的编写更加简洁、更加可控 —— 于是真正的Mybatis动态SQL —— MyBatis Dynamic SQL 应运而生。


MyBatis Dynamic SQL

项目地址

github.com/mybatis/myb…

官方文档

mybatis.org/mybatis-dyn…

项目介绍

Initial Release - December 17, 2017 Last Published: 23 November 2019 | Version: 1.1.4

2017-12-17,该项目发布了第一版本, 最近一个版本是2019-11-23发布的1.1.4,该项目是Mybatis官方项目之一。

作者

Jeff Butler【https://github.com/jeffgbutler】,是 Mybatis组织 成员,是 Mybatis 的主要贡献者,同时也是我们常用工具 MyBatis Generator 的作者。

Mybatis Generator

可想而知,Mybatis Dynamic SQLMybatis Genrator 很好的支持,在Mybatis Dynamic SQL 最新版发布的第二天,2019-11-24 Mybatis Genrator 1.4.0 发布,这个版本做了较大改动,主要是

  • New Runtime for Kotlin using MyBatis Dynamic SQL
  • New Runtime for Java using MyBatis Dynamic SQL
  • MyBatis Dynamic SQL is now the default runtime
  • Move to Java 8
  • Remove support for iBatis2

主要是移除对iBatis2的支持,同时使用 MyBatis Dynamic SQL 作为默认运行时,这无疑给Mybatis用户带来了极大便利。在以后的文章会进行详细介绍

简介

这个项目是用来生成动态SQL语句的框架。可以将它看作是额外支持MyBatis3和Spring JDBC的类型安全的SQL模板库。

该库将生成完整的DELETE、INSERT、SELECT和UPDATE格式化语句,可由MyBatis或Spring使用。最普遍的使用情况是生成能够被Mybatis直接使用的的语句和一组匹配参数。同时,它也能够生成兼容Spring JDBC templates的语句和参数对象。

这个库通过实现一个 类似于SQL领域专用语言 (domain-specific language,DSL)来工作,DSL能够创建一个包含完整SQL语句以及语句所需要全部参数的对象。这个对象能够被Mybatis作为传入Mapper方法的参数直接使用,正如我们平常所做的那样。

特性

该库能生成如下类型的SQL语句(并不止于这些,最新信息可查看 Change Log):

  • 支持子查询的灵活的WHERE子句
  • 带有灵活WHERE子句的DELETE语句
  • 多种INSERT语句:
    • 单条记录的插入语句并且将会插入null值(一个“完全”插入)
    • 单条记录的插入语句并且将忽略输入值为null的列(一个“选择性”插入)
    • 带有SELECT语句的插入
    • 多行插入(类似于<foreach/>)
    • 批量插入(Spring Batch 或 JDBC Batch)
    • 支持返回自增主键
  • 带有灵活列表、灵活WHERE子句的SELECT语句,支持distinct、count(distinct ...)、group by、 joins, unions, “order by”, 等等。
  • 带有灵活WHERE子句的UPDATE语句,像插入语句一样,也两种更新:
    • “完全”更新
    • “选择性”更新
目标

这个库的主要目标是:

  • 类型安全 — 该库将尽可能确保参数类型与数据库列类型匹配
  • 富有表现力 — 语句以一种清晰地传达其含义的方式构建 (Hamcrest提供的一些灵感)
  • 灵活的 — where子句能够使用and、or以及嵌套条件的任意组合来构建
  • 可扩展的 — 该库将生成为MyBatis3、Spring JDBC Template或纯JDBC工作的语句。它还可以扩展为为其他框架生成子句。如果没有一个内置的 条件语句 充分满足您的需要,你可以很容易的自定义(毕竟代码的编写就是你擅长的事)
  • 小 — 没有依赖传递

提示

Java版本在快速的更新,但是直到今天,对于Java开发者影响最大的或许就是Java8,Java8为开发者带了TYPE_USE Annotations,带来了函数编程和Lamda新的日期和时间处理库Optional等等,甚至有人说 —— Java8给开发者带来了工具和机会【www.infoq.com/articles/Ty…】 比如:

同样的,Java8促使了 MyBatis Dynamic SQL 的出现,后续我们会看到,函数编程在 MyBatis Dynamic SQL 被运用到极致。

所以,如果要使用 MyBatis Dynamic SQL ,请保证你的工作环境为:Java8+

快速使用

步骤

使用 MyBatis Dynamic SQL 需要下列步骤:

  • 创建Table和Column对象
  • (为Mybatis3)创建 Mappers (XML or Java Based)
  • 写SQL并且去使用它

出于讨论的目的,首先我们展示出来用来执行CRUD的表结构:

create table SimpleTable (
   id int not null,
   first_name varchar(30) not null,
   last_name varchar(30) not null,
   birth_date date not null, 
   employed varchar(3) not null,
   occupation varchar(30) null,
   primary key(id)
);

定义常量Tables和Columns

org.mybatis.dynamic.sql.SqlTable 类被用来定义一个Table。Table 的定义包含实际的表明(甚至是合适的schema 和 catalog )。如果期望,Table的alias能够用于SQL语句。你的Table应该继承SqlTable 类。 org.mybatis.dynamic.sql.SqlColumn 类被用来定义在库中使用的columns,SqlColumns 应该基于SqlTable来创建。一个列的定义包含:

  • The Java type
  • The actual column name (an alias can be applied in a select statement)
  • The JDBC type (optional)
  • 如果不需要默认的Type Handler,可以给出需要在Mybatis中使用的Type Handler的完全限定名。

(注意: 不同于PO(Persistent Object 持久化对象,它跟持久层(通常是关系型数据库)的数据结构形成一一对应的映射关系),在这里Table和Column对象,是对 以及 的严格意义的抽象,它们被定义为静态常量,允许你在SQL语句构建中不断重用,就像是在命令行中手写SQL语句那样。

建议使用如下使用模式来提供最大的灵活性。这个模式允许你用“qualified” or “un-qualified” 的习惯来使用你的Tables和columns,就像在命令行中写SQL语句那样。例如,可以将下面这个column写为firstName或simpleTable.firstName。

package examples.simple;

import java.sql.JDBCType;
import java.util.Date;

import org.mybatis.dynamic.sql.SqlColumn;
import org.mybatis.dynamic.sql.SqlTable;

public final class SimpleTableDynamicSqlSupport {
    public static final SimpleTable simpleTable = new SimpleTable();
    public static final SqlColumn<Integer> id = simpleTable.id;
    public static final SqlColumn<String> firstName = simpleTable.firstName;
    public static final SqlColumn<String> lastName = simpleTable.lastName;
    public static final SqlColumn<Date> birthDate = simpleTable.birthDate;
    public static final SqlColumn<Boolean> employed = simpleTable.employed;
    public static final SqlColumn<String> occupation = simpleTable.occupation;

    public static final class SimpleTable extends SqlTable {
        public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
        public final SqlColumn<String> firstName = column("first_name", JDBCType.VARCHAR);
        public final SqlColumn<String> lastName = column("last_name", JDBCType.VARCHAR);
        public final SqlColumn<Date> birthDate = column("birth_date", JDBCType.DATE);
        public final SqlColumn<Boolean> employed = column("employed", JDBCType.VARCHAR, "examples.simple.YesNoTypeHandler");
        public final SqlColumn<String> occupation = column("occupation", JDBCType.VARCHAR);

        public SimpleTable() {
            super("SimpleTable");
        }
    }
}

创建 MyBatis3 Mappers

这个库能创建用作MyBatis mapper输入的类。这些类包含生成的SQL以及匹配的参数集合。两者都是MyBatis所需要的。这些对象是MyBatis mapper方法的唯一参数。

(注意:MyBatis Dynamic SQL 不需要XML文件就能工作的很好,但并不意味着不支持XML,毕竟 MyBatis 最初被设计为是一个 XML 驱动的框架。当你使用关联查询,需要复杂的映射,那么使用XML <ResultMap> 与 MyBatis Dynamic SQL 结合起来或者是更好选择,你的XML或许只需要包含一些<ResultMap>)

例如,一个Mapper可以像下面这样:

package examples.simple;

import java.util.List;

import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;

@Mapper
public interface SimpleTableAnnotatedMapper {

    @InsertProvider(type=SqlProviderAdapter.class, method="insert")
    int insert(InsertStatementProvider<SimpleTableRecord> insertStatement);

    @UpdateProvider(type=SqlProviderAdapter.class, method="update")
    int update(UpdateStatementProvider updateStatement);

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @Results(id="SimpleTableResult", value= {
            @Result(column="A_ID", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="first_name", property="firstName", jdbcType=JdbcType.VARCHAR),
            @Result(column="last_name", property="lastName", jdbcType=JdbcType.VARCHAR),
            @Result(column="birth_date", property="birthDate", jdbcType=JdbcType.DATE),
            @Result(column="employed", property="employed", jdbcType=JdbcType.VARCHAR, typeHandler=YesNoTypeHandler.class),
            @Result(column="occupation", property="occupation", jdbcType=JdbcType.VARCHAR)
    })
    List<SimpleTableRecord> selectMany(SelectStatementProvider selectStatement);

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    @ResultMap("SimpleTableResult")
    SimpleTableRecord selectOne(SelectStatementProvider selectStatement);

    @DeleteProvider(type=SqlProviderAdapter.class, method="delete")
    int delete(DeleteStatementProvider deleteStatement);

    @SelectProvider(type=SqlProviderAdapter.class, method="select")
    long count(SelectStatementProvider selectStatement);
}

用Mybatis3执行SQL

在DAO或服务类中,可以使用生成的语句作为映射器方法的输入。下面是一个来自examples.simple.SimpleTableAnnotatedMapperTest 的示例:

    @Test
    public void testSelectByExample() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            SimpleTableAnnotatedMapper mapper = session.getMapper(SimpleTableAnnotatedMapper.class);
            
            SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation)
                    .from(simpleTable)
                    .where(id, isEqualTo(1))
                    .or(occupation, isNull())
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            List<SimpleTableRecord> rows = mapper.selectMany(selectStatement);

            assertThat(rows.size()).isEqualTo(3);
        }
    }

最后

想要了解更多,Mybatis Dynamic SQL 高级用法,工作原理,实践中的坑,以及如何更友好的使用 Mybatis Generator 可以关注 公众号:流花鬼的博客 ,持续更新中。。。