MyBatis Dynamic SQL基本使用
一、概念
该库是一个用于生成动态 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);
}