联动
后文
前言
你可能很熟悉Mybatis，但是今天我们不讲这个基于字符串拼接的上古时代的库。今天我们谈一个基于 QueryDSL 实现的库。（这个库第一个版本诞生自 2009年），他叫做 JOOQ。
JOOQ 可以用一句话总结：当你在使用 JOOQ 的时候，你就是在使用 SQL；一切你能够在 SQL 标准下实现的操作，几乎都能在 JOOQ 以相同的方式实现。
记住这句话，你就理解了整个JOOQ 的设计思想。
JOOQ 就是类型安全的、可复用、和可以 Debug 的 SQL。
接下来我们回归分页查询的主题。
Offset & Limit
普通的分页查询写起来很容易，JOOQ 就是 SQL，所以他的 Java API 和 SQL 一模一样。
SQL
SELECT username FROM user LIMIT 2 OFFSET 1;
JOOQ
List<User> users = dsl.select(USER.USERNAME).from(USER).limit(2).offset(1).fetchInto(User.class);
OrderBy
SQL
SELECT username FROM user ORDER BY id DESC LIMIT 3 OFFSET 1;
JOOQ
List<User> users = dsl.select(USER.USERNAME)
.from(USER)
.orderBy(USER.ID.desc())
.limit(3)
.offset(1)
.fetchInto(User.class);
上面的都很简单，下面是一个复杂点的例子。
Ties
USER 表中有5行数据，其最后三行的 password 值是相同的；我们的需求是按 password asc 排序查询出前三条结果。
| id | username | password |
|---|---|---|
| 1 | testUserA | a |
| 2 | testUserB | b |
| 3 | testUserC | c |
| 4 | testUserD | c |
| 5 | testUserE | c |
这个需求满足起来很容易，使用传统的 order by & limit offset 就可以满足需求。
List<User> users = dsl.select(USER.USERNAME)
.from(USER)
.orderBy(USER.PASSWORD.asc())
.limit(3)
.offset(0)
.fetchInto(User.class);
但问题是记录中有 3 行数据的 password 是相同的，在业务上 testUserC testUserD testUserE 实际拥有相同的优先级。如果我们不想遗漏这些相同优先级的数据应该怎么办呢？
这就到了 withTies 上场的时候了：它会返回查询结果集中和最后一条数据优先级相同的数据。
@Test
@Sql(
statements = {
"INSERT INTO mjga.user (id, username, password) VALUES (1, 'testUserA','a')",
"INSERT INTO mjga.user (id, username, password) VALUES (2, 'testUserB','b')",
"INSERT INTO mjga.user (id, username, password) VALUES (3, 'testUserC','c')",
"INSERT INTO mjga.user (id, username, password) VALUES (4, 'testUserD','c')",
"INSERT INTO mjga.user (id, username, password) VALUES (5, 'testUserE','c')"
})
void fetchAndTiesQuery() {
List<User> users =
dsl.select(USER.USERNAME)
.from(USER)
.orderBy(USER.PASSWORD.asc())
.limit(3)
.withTies()
.offset(0)
.fetchInto(User.class);
assertThat(users.size()).isEqualTo(5);
assertThat(users.get(0).getUsername()).isEqualTo("testUserA");
assertThat(users.get(4).getUsername()).isEqualTo("testUserE");
}
上例我们指定了 limit 3 offset 0 但却查询出了 5 条结果，原因就是 testUserD 和 testUserE 和 testUserC 形成了绑定（withTies）关系。
这是什么 JOOQ 的神奇魔法吗？不是，这是 SQL 的标准功能，但是 Mysql 却不支持它；而诸如 Mybatis 之类的库又没有在应用层面模拟这个行为，这导致了你没有接触过它。
Window Function
再来个更高级点的例子：很多时候分页查询都需要我们额外统计一个「总数」。无论你用什么库或插件，其原理都是通过再运行一个 SELECT COUNT(*) AS total_count 之类的聚合查询来满足这个需求，这会导致你和 DB 多交互一次。
所以能不能只和 DB 交互一次，就同时获取查询结果集和统计总数呢？当然可以，窗口函数可以把结果集视为一个窗口，为这个结果集的每一行计算一个聚合值，而不改变结果集的行数。
SQL
SELECT *, COUNT(*) OVER () AS total_user
FROM user
ORDER BY id ASC
LIMIT 4 OFFSET 0;
JOOQ
不要被 Result<Record> 吓到，这就是一个自定义的 List<Map> 结构。
Result<Record> resultWithWindow = dsl.select(asterisk(), DSL.count().over().as("total_user"))
.from(USER)
.orderBy(USER.ID.asc())
.limit(4)
.offset(0)
.fetch();
Result
| id | username | total_user |
|---|---|---|
| 1 | Alice | 5 |
| 2 | Bob | 5 |
| 3 | Charlie | 5 |
| 4 | David | 5 |
这是什么 JOOQ 的神奇魔法吗？不是，这是 SQL 的标准功能，但是 Mysql 8 以下的版本却不支持它；如果你长期使用 Mybatis-plus 等相关插件，这也会导致你错过窗口函数，因为它们都没有提供对这些常见 SQL 标准的支持。
如果你喜欢 SQL
那你一定会喜欢 JOOQ 帮你把 Mybatis 的「运行时异常」提前到「编译时异常」的机制；像这样的类型安全的、可复用、可 Debug 的 SQL 将会颠覆你至今已来的 SQL 编码体验。
写在最后
我是 Chuck1sn，一个长期致力于现代 Jvm 生态推广的开发者。您的回帖、点赞、收藏、就是我持续更新的动力；举手之劳的一键三连对我来说是莫大的支持，非常感谢！还可关注我的账号，第一时间收到文章推送。
