《你不知道的 JAVA 系列博客》🔥 分頁查詢的達芬奇密碼。詳情 - java,springboot,docker,springsecurity,gradle Chuck1sn 動態日志

工程思維落地

《你不知道的 Java 系列》已將工程思維與設計理念落地，形成了一款全新設計的 Java 腳手架，可與博客配套使用。

前言

你可能很熟悉 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();