MyBatis Dynamic SQL基本使用

一、概念
二、特性
- - Hamcrest是什么
三、MyBatis Dynamic SQL 快速入门
- 3.1 环境准备
- 3.2 定义表和列
- 3.3 创建 MyBatis3 映射器
- 3.4 使用 MyBatis3 执行 SQL
四、数据库对象表示
- 4.1 表或视图表示
- 4.2 表别名
- 4.3 列表示
五、Where 子句支持
- 5.1 简单的 where 子句
- 5.2 复杂的 where 子句
- 5.3 子查询
- 5.4 独立的 where 子句
六、select 语句
- 6.1 join
- 6.2 联合查询
- 6.3 多选查询
七、运行机制

在这里插入图片描述

一、概念

该库是一个用于生成动态 SQL 语句的框架。将其视为类型安全的 SQL 模板库，并提供对 MyBatis3 和 Spring JDBC模板的额外支持。
该库通过实现类似 SQL 的DSL 来工作，该 DSL 创建一个包含完整 SQL 语句以及该语句所需的任何参数的对象。SQL 语句对象可以被 MyBatis 直接用作 mapper方法的参数。

二、特性

类型安全
富有表现力（语句的构建方式可以清楚地传达其含义，灵感来自 Hamcrest）
灵活
可扩展性
占用小（该库是一个需要添加的小依赖项。它没有传递依赖）

Hamcrest是什么

Hamcrest 是一个用于编写匹配器（matcher）对象的框架，它允许以声明方式定义“匹配（match）”规则。这个框架主要用于编写断言，以便于在单元测试中进行使用。Hamcrest 提供了一系列可重用的断言和匹配器，使得编写测试更加简单和直观。它是一个Java库，能组合成灵活的表达式，以测试为目的，用于编写断言和匹配器。

三、MyBatis Dynamic SQL 快速入门

使用 MyBatis 动态 SQL 需要一下步骤：

创建表和列对象
（对于 MyBatis3 ）创建映射器（基于 XML 或 Java）
编写和使用 SQL

3.1 环境准备

create table Person (
    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,
    address_id int not null,
    primary key(id)
);

package examples.simple;

import java.util.Date;

public class PersonRecord {
    private Integer id;
    private String firstName;
    private LastName lastName;
    private Date birthDate;
    private Boolean employed;
    private String occupation;
    private Integer addressId;

    // getters and setters omitted
}

3.2 定义表和列

该类org.mybatis.dynamic.sql.AlisableSqlTable 用于定义表。表定义包括表的实际名称（如果使用，包括模式或目录）。如果需要，可以在 select 语句中应用表别名。您的表应该通过扩展 AlisableSqlTable<T>类来定义。该类org.mybatis.dynamic.sql.SqlColumn用于定义库中使用的列。列定义包括：

Java 类型
实际的列名（可以在 select 语句中应用别名）
JDBC 类型
（可选）如果不需要默认类型处理程序，则在 MyBatis 中使用的类型处理程序的名称

package examples.simple;

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

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

public final class PersonDynamicSqlSupport {
    public static final Person person = new Person();
    public static final SqlColumn<Integer> id = person.id;
    public static final SqlColumn<String> firstName = person.firstName;
    public static final SqlColumn<LastName> lastName = person.lastName;
    public static final SqlColumn<Date> birthDate = person.birthDate;
    public static final SqlColumn<Boolean> employed = person.employed;
    public static final SqlColumn<String> occupation = person.occupation;
    public static final SqlColumn<Integer> addressId = person.addressId;

    public static final class Person extends AliasableSqlTable<Person> {
        public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
        public final SqlColumn<String> firstName = column("first_name", JDBCType.VARCHAR);
        public final SqlColumn<LastName> lastName = column("last_name", JDBCType.VARCHAR, "examples.simple.LastNameTypeHandler");
        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 final SqlColumn<Integer> addressId = column("address_id", JDBCType.INTEGER);

        public Person() {
            super("Person", Person::new);
        }
    }
}

3.3 创建 MyBatis3 映射器

该库将创建用作 MyBatis 映射器输入的类。这些类包括生成的 SQL 以及生成的 SQL 相匹配的参数集。两者都是 MyBatis 所需要的。这些对象旨在成为 MyBatis 映射器方法的唯一参数。
该库可以与 XML 和带注解的映射器一起使用，但我们建议在所有情况下都使用 MyBatis 的带注解的映射器支持。唯一需要 XML 的情况是当您编写 Join 语句时，在这种情况下，由于 MyBatis 注解在支持连接方面的限制，您将需要在 XML 中定义结果映射。

package examples.simple;

import java.util.List;
import java.util.Optional;

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.type.JdbcType;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
import org.mybatis.dynamic.sql.util.mybatis3.CommonCountMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonDeleteMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonInsertMapper;
import org.mybatis.dynamic.sql.util.mybatis3.CommonUpdateMapper;

@Mapper
public interface PersonMapper extends CommonCountMapper, CommonDeleteMapper, CommonInsertMapper<PersonRecord>, CommonUpdateMapper {

    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    @Results(id = "PersonResult", 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, typeHandler = LastNameTypeHandler.class),
            @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),
            @Result(column = "address_id", property = "addressId", jdbcType = JdbcType.INTEGER)
    })
    List<PersonRecord> selectMany(SelectStatementProvider selectStatement);

    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    @ResultMap("PersonResult")
    Optional<PersonRecord> selectOne(SelectStatementProvider selectStatement);
}

该映射器为表实现了完整的 CRUD 功能。基本接口 CommonCountMapper、CommonDeleteMapper 等提供插入、更新、删除和计数功能。由于自定义结果映射，只需编写 select 方法。请注意，CommonInsertMapper 如果插入生成了主键key，则接口将无法正确返回生成的主键key。

3.4 使用 MyBatis3 执行 SQL

在服务类中，您可以使用生成的语句作为映射器方法的输入。

@Test
void testGeneralSelect() {
    try (SqlSession session = sqlSessionFactory.openSession()) {
        PersonMapper mapper = session.getMapper(PersonMapper.class);

        SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed,
            occupation, addressId)
        .from(person)
        .where(id, isEqualTo(1))
        .or(occupation, isNull())
        .build()
        .render(RenderingStrategies.MYBATIS3);

        List<PersonRecord> rows = mapper.selectMany(selectStatement);
        assertThat(rows).hasSize(3);
    }
}

@Test
void testGeneralDelete() {
    try (SqlSession session = sqlSessionFactory.openSession()) {
        PersonMapper mapper = session.getMapper(PersonMapper.class);

        DeleteStatementProvider deleteStatement = deleteFrom(person)
        .where(occupation, isNull())
        .build()
        .render(RenderingStrategies.MYBATIS3);

        int rows = mapper.delete(deleteStatement);
        assertThat(rows).isEqualTo(2);
    }
}

四、数据库对象表示

MyBatis Dynamic SQL 使用代表关系表或视图的 Java 对象。

4.1 表或视图表示

org.mybatis.dynamic.sql.SqlTable 类用于表示数据库中的表或视图。 SqlTable 包含一个名称，以及代表表或视图中的列的 SqlColumn 对象的集合。
SQL 中的表或视图名称由三个部分组成：

The catalog ：可选，很少在 SQL Server 之外使用。如果未指定，将使用默认目录，许多数据库只有一个目录。
The schema ：可选，架构名或者库名。如果未指定，将使用默认架构或库名。
The table name：必选，表名。

例如：

“dbo..bar” ：dbo目录下的表名为 bar 的表。（SQL Server中使用）
“foo.bar” ：模式名或者库名为 foo 下的 bar 表。
“bar” ：表名为 bar 的表。

在 MyBatis Dynamic SQL 中，表的全名应该在表对象的构造函数中提供。如果表名称需要在运行时更改（例如分片支持），则使用 withName 方法 AliasableSqlTable 以新名称创建实例。

我们建议在所有情况下都使用基类，AliaableSqlTable 因为它提供了最大的灵活性。该类 SqlTable 保留在库中只是为了与旧代码兼容。
例如：

import org.mybatis.dynamic.sql.AliasableSqlTable;

public class MyTable extends AliasableSqlTable<MyTable> {
    public MyTable() {
        super("MyTable", MyTable::new);
    }
}

或者

public class MyTable extends AliasableSqlTable<MyTable> {
    public MyTable() {
        super("MySchema.MyTable", MyTable::new);
    }
}

您可以更改表名称：

public class MyTable extends AliasableSqlTable<MyTable> {
    public MyTable() {
        super("Schema1.MyTable", MyTable::new);
    }
}

MyTable schema1Table = new MyTable();
MyTable schema2Table = schema1Table.withName("Schema2.MyTable");

4.2 表别名

在连接查询中，指定表别名通常是一个好习惯。该select语句支持以类似于自然 SQL 的方式在每个查询中指定表别名。

SelectStatementProvider selectStatement = 
            select(
                orderMaster.orderId,
                orderDate,
                orderLine.lineNumber,
                itemMaster.description,
                orderLine.quantity)
            .from(orderMaster, "om")
            .join(orderLine, "ol").on(orderMaster.orderId, equalTo(orderLine.orderId))
            .join(itemMaster, "im").on(orderLine.itemId, equalTo(itemMaster.itemId))
            .where(orderMaster.orderId, isEqualTo(2))
            .build()
            .render(RenderingStrategies.MYBATIS3);

在这样的查询中，当呈现查询时，库会自动将表别名附加到列名称中。在内部，列别名是通过在查询模型中维护的 HashMap 中查找关联表来确定的。如果不指定表别名，库将自动在连接查询中附加表名称。（但是不推荐这种，因为自连接的时候会有问题）
从版本 1.3.1 开始，有一个新方法，可以在表对象本身中指定别名。

User user1 = user.withAlias("u1");
User user2 = user.withAlias("u2");

SelectStatementProvider selectStatement = 
        select(
            user1.userId,
            user1.userName,
            user1.parentId)
        .from(user1)
        .join(user2).on(user1.userId, equalTo(user2.parentId))
        .where(user2.userId, isEqualTo(4))
        .build()
        .render(RenderingStrategies.MYBATIS3);

要启用此支持，您的表对象应该扩展 org.mybatis.dynamic.sql.AliasableSqlTable 而不是 org.mybatis.dynamic.sql.SqlTable 如下所示：

public static final class User extends AliasableSqlTable<User> {
    public final SqlColumn<Integer> userId = column("user_id", JDBCType.INTEGER);
    public final SqlColumn<String> userName = column("user_name", JDBCType.VARCHAR);
    public final SqlColumn<Integer> parentId = column("parent_id", JDBCType.INTEGER);

    public User() {
        super("User", User::new);
    }
}

如果使用别名表对象，并且还在 select 语句中指定别名，则语句中的别名 select 将覆盖表对象中的别名。

4.3 列表示

该类 org.mybatis.dynamic.sql.SqlColumn 用于表示表或视图中的列。一个 SqlColumn 始终相关联一个 SqlTable 。
该类 SqlColumn 具有额外的可选属性，对于 SQL 渲染非常有用——尤其是在 MyBatis3 中。这些包括：

列的 JDBC 类型： java.sql.JDBCType 。这将被渲染到 MyBatis3 兼容的参数标记中，这有助于选择类型处理程序以及插入或更新支持 null 的字段。
包含类型处理程序的字符串：类型处理程序别名或类型处理程序的完全限定类型。这将被渲染到 MyBatis3 兼容参数标记中。

五、Where 子句支持

该库支持创建非常灵活的 where 子句。

5.1 简单的 where 子句

最简单的 where 子句的形式如下：

SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isEqualTo(3))
                .build()
                .render(RenderingStrategies.MYBATIS3);

该库附带了可在 where 子句中使用的各种条件，包括in、like、between、isNull、isNotNull 和所有正常的比较运算符。例如：

 SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isBetween(3).and(6))
                .build()
                .render(RenderingStrategies.MYBATIS3);

 SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isIn(3,4,5))
                .build()
                .render(RenderingStrategies.MYBATIS3);

SelectStatementProvider selectStatement = select(count())
                .from(simpleTable)
                .where(id, isNotNull())
                .build()
                .render(RenderingStrategies.MYBATIS3);

5.2 复杂的 where 子句

条件实际上可以以任何组合进行“与”和“或”组合。例如：

 SelectStatementProvider selectStatement = select(count())
                .from(simpleTable, "a")
                // isGreaterThan(2) -> id > 2
                .where(id, isGreaterThan(2))
                .or(occupation, isNull(), and(id, isLessThan(6)))
                .build()
                .render(RenderingStrategies.MYBATIS3);

5.3 子查询

SelectStatementProvider selectStatement = select(column1.as("A_COLUMN1"), column2)
                .from(table, "a")
                .where(column2, 
                       isIn(
                            select(column2)
                           .from(table)
                           .where(column2, isEqualTo(3))
                       ))
                .or(column1, isLessThan(d))
                .build()
                .render(RenderingStrategies.MYBATIS3);

5.4 独立的 where 子句

尽管很少见，但如果您希望为语句的其余部分编写自己的 SQL，则可以单独使用 where 子句支持。这样做可能有多种原因 - 主要是如果库不支持您想要使用的某些 SQL 或 MyBatis 功能。一个很好的例子是，如果您想将其他 SQL 附加到库生成的 SQL 中。如果您想使用独立的 where 子句，您可以编写如下所示的映射器方法：

@Select({
        "select id, animal_name, brain_weight, body_weight",
        "from AnimalData",
        "${whereClause}"
    })
    @ResultMap("AnimalDataResult")
    List<AnimalData> selectWithWhereClause(WhereClauseProvider whereClause);

您可以构建一个独立的 where 子句并像这样调用您的映射器：

Optional<WhereClauseProvider> whereClause = where(id, isNotBetween(10).and(60))
            .build()
            .render(RenderingStrategies.MYBATIS3);

    List<AnimalData> animals = whereClause.map(wc -> mapper.selectWithWhereClause(wc)).orElse(Collections.emptyList());

当语句不需要其他参数并且不涉及表别名时，此方法效果很好。

六、select 语句

select 语句的典型部分包括 select、distinct、from、join、where、group by、union all、order by、having
每个 select 语句可以为表设置别名
每个 select 语句可以为列指定别名
对聚合的一些支持（平均值、最小值、最大值、总和）
inner、left outer、right outer、full outer类型的等连接
where 子句中的子查询。例如，where foo in (select foo from foos where id < 36)
从另一个 select 中 select 。例如，select count(*) from (select foo from foos where id < 36)
多选。例如(select * from foo order by id limit 3) union (select * from foo order by id desc limit 3)

目前，该库不支持以下内容：

带表达式
相交、除外等

SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .where(id, isIn(1, 5, 7))
        .and(bodyWeight, isBetween(1.0).and(3.0))
        .orderBy(id.descending(), bodyWeight)
        .build()
        .render(RenderingStrategies.MYBATIS3);

List<AnimalData> animals = mapper.selectMany(selectStatement);

6.1 join

该库支持生成等值连接语句，通过列匹配定义的连接。例如：

 SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
            .from(orderMaster, "om")
            .join(orderDetail, "od").on(orderMaster.orderId, equalTo(orderDetail.orderId))
            .build()
            .render(RenderingStrategies.MYBATIS3);

请注意，如果需要，您可以为表指定别名。如果不指定别名，则生成的 SQL 中将使用完整的表名。
可以在一条语句中连接多个表。例如：

 SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
            .from(orderMaster, "om")
            .join(orderLine, "ol").on(orderMaster.orderId, equalTo(orderLine.orderId))
            .join(itemMaster, "im").on(orderLine.itemId, equalTo(itemMaster.itemId))
            .where(orderMaster.orderId, isEqualTo(2))
            .build()
            .render(RenderingStrategies.MYBATIS3);

连接查询可能需要您在 XML 中定义 MyBatis 结果映射。这是唯一需要 XML 的实例。这是由于 MyBatis 注解在映射集合时的限制造成的。
该库支持四种连接类型：

.join(...)是一个 INNER 连接
.leftJoin(...)是 LEFT OUTER 连接
.rightJoin(...)是 RIGHT OUTER 连接
.fullJoin(...)是一个 FULL OUTER 连接

6.2 联合查询

该库支持生成 union 和 union all 查询。例如：

SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .union()
        .selectDistinct(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .orderBy(id)
        .build()
        .render(RenderingStrategies.MYBATIS3);

可以将任意数量的 SELECT 语句添加到 UNION 查询中。只允许使用一个 ORDER BY 短语。
对于这种类型的联合查询，“order by”和分页子句作为一个整体应用于查询。如果需要对嵌套查询应用“order by”或分页子句，请使用多选查询，如下所示。

6.3 多选查询

多选查询时联合 select 语句的一种特殊情况。不同之处在于“order by”和分页子句可以应用于合并查询。例如：

 SelectStatementProvider selectStatement = multiSelect(
            select(id, animalName, bodyWeight, brainWeight)
            .from(animalData)
            .orderBy(id)
            .limit(2)
        ).union(
            selectDistinct(id, animalName, bodyWeight, brainWeight)
            .from(animalData)
            .orderBy(id.descending())
            .limit(3)
        )
        .build()
        .render(RenderingStrategies.MYBATIS3);

七、运行机制

MyBatis 主要做了四件事：

它安全地执行 SQL 并抽象出 JDBC 的所有复杂性
它将参数对象映射到 JDBC 准备好的语句参数
它将 JDBC 结果集中的行映射到对象
它可以通过 XML 中的特殊标签生成动态SQL，或者通过使用各种模板引擎

该库充分利用了 MyBatis 中的前三个功能，本质上成为生成动态 SQL 的另一个模板引擎。
例如，MyBatis 可以执行如下格式的 SQL 字符串：

select id, description from table_codes where id = #{id,jdbcType=INTEGER}

这是带有 MyBatis 风格的标准 SQL，参数符号 #{id,jdbcType=INTEGER} 告诉 MyBatis 获取 id参数对象的属性并将其用作 JDBC 准备好的语句参数。
现在假设我们有两个这样的 Java 类：

public class TableCode {
  private Integer id;
  private String description;
  ... getters/setters
}

public class Parameter {
  private String sql = "select id, description from table_codes where id = #{id,jdbcType=INTEGER}";
  private Integer id;

  public Parameter(Integer id) {
    this.id = id;
  }

  public Integer getId() {
    return id;
  }

  public String getSql() {
    return sql;
  }
}

这些类可以与 MyBatis 映射器结合使用，如下所示：

public interface Mapper {

  @Select({
    "${sql}"
  })
  TableCode getTableCode(Parameter parameter);
}

将此映射器与 MyBatis 一起使用如下所示：

try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
    Mapper mapper = sqlSession.getMapper(Mapper.class);
    Parameter parameter = new Parameter(2);
    TableCode tableCode = mapper.getTableCode(parameter);
    assertThat(tableCode.getId()).isEqualTo(2);
}