Why jOOQ Matters#

在使用關聯式資料庫時，最終一切都歸結為 SQL 語句。Hibernate entity query 適合讀寫邏輯交易，但對於 reporting、analytics 或 ETL (Extract, Transform, Load) 等場景，native SQL query 才是最佳選擇，因為它們能利用資料庫特有的功能，如 Window Functions 或 Common Table Expressions。

雖然 JPA 和 Hibernate 提供了執行 native SQL 的方式，但僅限於靜態語句。若需要動態構建 native SQL，JPA 和 Hibernate 就不夠用了——這正是 jOOQ 的用武之地。

How jOOQ Works#

jOOQ 是一個 query builder framework，允許開發者透過 Java API 產生各種資料庫專屬的 SQL 語句。DSLContext 是建構 SQL 語句的起點，需要兩個元素：

• 一個 JDBC Connection 的參考
• 一個 database dialect，用於將 Java API query 表示轉換為資料庫專屬的 SQL

DSLContext sql = DSL.using(connection, SQLDialect.POSTGRES_9_5);

jOOQ 的 Java API 語法幾乎與 SQL 一一對應，大多數 jOOQ query 都是自描述的。

DML Statements#

透過 DSLContext，可以執行基本的 DML 語句（insert、update、delete、select）。execute() 方法回傳受影響的資料列數，fetch() 方法用於取得查詢結果集。

• Delete – sql.deleteFrom(table("post")).execute()
• Insert – sql.insertInto(table("post")).columns(...).values(...).execute()
• Update – sql.update(table("post")).set(...).where(...).execute()
• Select – sql.select(...).from(...).where(...).fetch()

上述範例以字串方式引用 table 名稱和 column 名稱，但 jOOQ 提供了更好的方式——Java-based schema。

Java-based Schema#

如同 JPA 為 Criteria query 定義 Metamodel API，jOOQ 可以產生一個映射資料庫結構的 Java-based schema。其運作流程為：

1. 應用程式設定觸發 Generation Tool
2. Generation Tool 透過 reverse engineering 讀取資料庫結構
3. 產出 Java-based Schema 類別

使用 Java-based schema 的優勢：

• 編譯期型別檢查 – stored procedure 的參數型別、query 參數及結果集都能在編譯時綁定
• 重構安全 – 當 column 名稱變更時，若未更新 jOOQ 語句，應用程式將無法編譯
• IDE 自動補全 – 提升開發效率，減少 typo 的可能性

import static com.vladmihalcea.book.hpjp.jooq.pgsql.schema.Tables.POST;

sql.insertInto(POST).columns(POST.ID, POST.TITLE)
   .values(1L, "High-Performance Java Persistence")
   .execute();

雖然 jOOQ 不使用 Java-based schema 也能正常運作，但建議盡可能使用 typesafe query 以獲得更好的安全性和開發體驗。

Upsert#

在資料庫術語中，upsert 是 insert 和 update 的混合操作：先嘗試 insert，若因 unique constraint 失敗則改為 update。SQL:2003 和 SQL:2008 標準定義了 MERGE 語句，但各資料庫的實作方式不同。

jOOQ 實作了 upsert 操作，能自動將 Java-based query 轉換為底層資料庫專屬的 SQL 語法：

sql
.insertInto(POST_DETAILS)
.columns(POST_DETAILS.ID, POST_DETAILS.CREATED_BY, POST_DETAILS.CREATED_ON)
.values(id, owner, timestamp)
.onDuplicateKeyUpdate()
.set(POST_DETAILS.UPDATED_BY, owner)
.set(POST_DETAILS.UPDATED_ON, timestamp)
.execute();

Oracle#

Oracle 使用 MERGE 語句實作 upsert，包含 WHEN MATCHED THEN UPDATE 和 WHEN NOT MATCHED THEN INSERT 兩個分支。

SQL Server#

與 Oracle 相同，SQL Server 也使用 MERGE 語句，語法結構類似。

PostgreSQL#

PostgreSQL 使用 ON CONFLICT 子句實作 upsert：

INSERT INTO "post_details" ("id", "created_by", "created_on")
VALUES (1, 'Alice', CAST('2016-08-11 12:56:01.831' AS timestamp))
ON CONFLICT ("id") DO
UPDATE SET
    "updated_by" = 'Alice',
    "updated_on" = CAST('2016-08-11 12:56:01.831' AS timestamp)

MySQL#

MySQL 的語法與 PostgreSQL 類似，使用 ON DUPLICATE KEY UPDATE：

INSERT INTO `post_details` (`id`, `created_by`, `created_on`)
VALUES (1, 'Alice', '2016-08-11 13:27:53.898')
ON DUPLICATE KEY
UPDATE
    `post_details`.`updated_by` = 'Alice',
    `post_details`.`updated_on` = '2016-08-11 13:27:53.898'

Batch Updates#

JDBC batching 對資料存取層的寫入效能至關重要。Hibernate 為使用 identity column 的 entity 無法自動進行 insert batching，因為 Hibernate 需要在 persist entity 時就知道 identity column 的值。

jOOQ 採用 WYSIWYG（所見即所得）的方式處理持久化，對於 MySQL 的 AUTO_INCREMENT identity 策略，jOOQ 的 JDBC batching 也能正常運作：

BatchBindStep batch = sql.batch(sql.insertInto(POST, POST.TITLE).values("?"));
for (int i = 0; i < 3; i++) {
    batch.bind(String.format("Post no. %d", i));
}
int[] insertCounts = batch.execute();

jOOQ 會將所有 insert 合併到單一資料庫 roundtrip 中執行。

使用 Hibernate 搭配 MySQL 且需要大量 insert 時，建議使用 jOOQ 來執行 batch insert 操作。

Inlining Bind Parameters#

預設情況下，jOOQ 與 Hibernate 一樣使用 PreparedStatement 和 bind parameter values，以利用 statement caching。

然而，在某些情境下，bind parameter values 可能影響 execution plan 的選擇。當 cached plan 不適用於特定的 bind parameter values 時，可以透過 inlining 將參數值直接嵌入 SQL 語句中，強制資料庫重新產生或選擇對應的 execution plan。

要啟用 inline 模式，需在建立 DSLContext 時設定 StatementType.STATIC_STATEMENT：

DSLContext sql = DSL.using(connection, sqlDialect(),
    new Settings().withStatementType(StatementType.STATIC_STATEMENT));

此設定會將所有 bind parameter values 直接嵌入 SQL 字串中，而非使用 ? placeholder。

書中所有 Hibernate 和 jOOQ 的 SQL 範例看起來像是 inline 參數，實際上是為了可讀性才手動將 bind parameter values 嵌入。真實環境下，預設的 PreparedStatement 都使用 bind parameter placeholder。

Complex Queries#

jOOQ 的一大優勢是能將複雜的 SQL query 以程式化方式構建，包含 Window Functions、Derived Tables 和 Recursive CTE (Common Table Expressions)。

• jOOQ API 的語法幾乎完全對應實際的 SQL 語句，使得程式碼具有高度可讀性
• 大型 query 可以拆分為多個獨立的方法（如將 WITH RECURSIVE 部分抽取為 withRecursiveExpression 方法），提升可讀性並減少程式碼重複
• 查詢結果可直接透過 fetchInto() 映射到自定義的 DTO class
• 使用 ResultTransformer 可將扁平結果集轉換為樹狀結構

Stored Procedures and Functions#

jOOQ 在呼叫 stored procedure 和 user-defined function 方面特別出色。如同掃描資料庫 metadata 建構 Java-based schema，jOOQ 也能為 stored procedure 產生對應的 Java class。

例如，一個回傳 REFCURSOR 的 PostgreSQL function：

PostCommentScores postCommentScores = new PostCommentScores();
postCommentScores.setPostid(postId);
postCommentScores.setRankid(rank);
postCommentScores.execute(sql.configuration());
return postCommentScores.getReturnValue().into(PostCommentScore.class);

透過 jOOQ，呼叫資料庫 stored procedure 和 user-defined function 就像呼叫普通的 Java method 一樣簡單。

Streaming#

處理大型結果集時，建議將資料集分割成多個較小的子集進行批次處理，以達到更好的記憶體分配。Streaming 是控制 fetched result set 大小的另一種方式，jOOQ 讓操作 database cursor 變得非常容易。

jOOQ 提供了 Java 8 Stream API 來瀏覽底層的 database cursor：

try (Stream<PostCommentDetailsRecord> stream = sql
    .selectFrom(POST_COMMENT_DETAILS)
    .where(POST_COMMENT_DETAILS.ID.gt(lastProcessedId))
    .stream()) {
    processStream(stream);
}

• 使用 try-with-resources 確保 database stream 在處理完畢後正確關閉
• 處理大量資料時，可使用固定大小的 MaxSizeHashMap（繼承 LinkedHashMap）作為 FIFO 資料結構，避免記憶體溢出

即使 streaming 非常適合處理超大結果集，但通常更建議以較小的 batch 進行操作，以避免 long-running transaction。

Keyset Pagination#

傳統的 offset pagination（JPA 和 Hibernate 預設支援）僅適用於小型結果集或瀏覽前幾頁時效率較高。越往後的頁面，資料庫需要做的工作越多。

要克服 offset pagination 的限制，有兩種替代方案：

1. 縮小結果集 – 透過多重篩選條件縮減範圍，使 offset pagination 不再成為瓶頸
2. Keyset pagination – 使用資料表的 primary key 標記當前讀取資料的位置

jOOQ 提供了 seek() 方法，可轉換為資料庫專屬的 keyset pagination query 語法：

SelectSeekStep2<Record3<Long, String, Timestamp>, Timestamp, Long>
    selectStep = sql
    .select(POST.ID, POST.TITLE, POST_DETAILS.CREATED_ON)
    .from(POST)
    .join(POST_DETAILS).on(POST.ID.eq(POST_DETAILS.ID))
    .where(POST_DETAILS.CREATED_BY.eq(user))
    .orderBy(POST_DETAILS.CREATED_ON.desc(), POST.ID.desc());

return (offsetPostSummary != null)
    ? selectStep
        .seek(offsetPostSummary.getCreatedOn(), offsetPostSummary.getId())
        .limit(pageSize)
        .fetchInto(PostSummary.class)
    : selectStep
        .limit(pageSize)
        .fetchInto(PostSummary.class);

• 第一頁時 offset 為 null，直接以 limit 取得
• 取得一頁結果後，以最後一筆記錄作為下一頁的 offset
• PostgreSQL 上產生的 SQL 使用 row value comparison，語法簡潔
• Oracle 11g 不支援 row value comparison 和專用的 result set 限制運算子，jOOQ 會自動模擬相同行為，因此產生的 SQL 較為複雜

當結果集規模較大時，keyset pagination 的效能遠優於 offset pagination。Markus Winand 的 No-Offset 文章詳細說明了為何 offset 的效率低於 keyset pagination。