让 Java 再次伟大 - 吊打面试官之分页查询的终极实战策略

学会这款 全新设计的 Java 脚手架 ，面试不再怕！

前言

JOOQ 就是 SQL，当你在使用 JOOQ 的时候，你就是在使用 SQL；一切你能够在 SQL 标准下实现的操作，几乎都能在 JOOQ 以相同的方式实现。记住这句话，你就理解了整个JOOQ 的设计思想。

Offset & Limit

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 标准的支持。

最后

使用 JOOQ，通过其对 SQL 标准的全面支持，外加将 SQL 运行时异常提前到编译时的类型安全机制， 将会帮你重新拿起 SQL 这个超级武器横扫一切需求。

以上所有代码都可以在 Github仓库 中找到。如果你觉得教程有帮助，求顺手给一个 Star，万分感谢。