TL;DR（一句話總結）

從 Spring Data JDBC 3.2.0-M2 開始，Spring Data JDBC 支援單查詢載入（Single Query Loading）。單查詢載入能夠用一個 SELECT 語句載入任意聚合（aggregate）。

要啟用單查詢載入，您需要在 RelationalMappingContext 上呼叫 setSingleQueryLoadingEnabled(true) 方法。

在 3.2.0-M2 版本中，這僅適用於簡單聚合，即由一個聚合根（aggregate root）和單個其他實體集合組成的聚合。它也僅限於 CrudRepository 中的 findAll、findById 和 findAllByIds 方法。未來版本將對此進行改進。最後一個限制是您使用的資料庫必須支援分析函式（也稱為視窗函式）。除記憶體資料庫（H2 和 Hsql DB）外，所有官方支援的資料庫都支援。

您可以將單查詢載入縮寫為 SQL，但請，別這樣做。

如果您想了解它是如何工作的，以及我們是如何想到這個方法的，請繼續閱讀。

問題

概念上，Spring Data JDBC 會一次性載入完整的聚合。然而，到目前為止，如果您檢視實際執行的 SQL，您會發現對於非平凡的聚合，會執行多個 SQL 語句。例如，考慮一個 Minion 型別，它引用一個 Hobby 集合和一個 Toy 實體集合。當 Spring Data JDBC 載入一批這樣的 Minions 時，它會：

1. 執行一個 SELECT ... FROM MINION
2. 對於該查詢中的每個結果，它會：
3. 執行一個 SELECT ... FROM HOBBY
4. 執行一個 SELECT ... FROM TOY

這效率低下，被稱為 N+1 問題，因為對於包含單個集合的聚合，要載入 N 個聚合，需要執行 N+1 個查詢（一個用於根，N 個用於子實體）。如果只有一個集合，您可以進行 JOIN，但當存在多個集合時，這種方法就會失效。

這個問題絕非 Spring Data JDBC 特有。其他 ORM 使用不同的策略來儘量減少這個問題。例如，它們可能會將一個子實體 JOIN 到聚合根。或者，它們可能會使用批處理載入相關實體。所有這些方法都限制了問題的影響，但它們只是治標不治本。此外，大多數人實際上會告訴您，您不能真正透過單個查詢做到這一點，因為您會得到所有子表的交叉積（cross product），這可能非常糟糕。想象一下 5 個子表，每個 Minion 有 10 個條目。它們的交叉積將是 1010101010 = 10000 行！

思路

很久以前，我記起了我的前同事 Frank Gerberding 說過的一句話：“關係資料庫的問題在於它們總是返回表格，而有時你需要一個樹狀結構。”嗯，他是用德語說的，我不記得他的確切用詞了，但大致意思是這樣。這讓我開始思考：確實，SQL 查詢基本上總是返回一個表格。但我如何在其中表示一個樹狀結構呢？換句話說：你如何在 Excel 中表示一個聚合的資料？如果你忽略 Excel 本質上是一個擁有超能力的關係資料庫這一事實，而只是把它當作一個單一的電子表格來處理呢？

我們先從一個相當簡單的例子開始。

class Minion {
    @Id
    Long id;
    String name;
    List<Toy> toys;
    // the skills you need to excel at this hobby.
    List<Hobby> hobbies;
}

Toy 和 Hobby 目前都只有一個 name 屬性。

如果我想在 Excel 中表示它，我可能會這樣做：

Minion id	Minion name	toys name	hobbies name
1	Bob	Teddy	Hold Teddy
		Blue Light	Look Cute
			Follow Kevin
2	Kevin	...	...

從查詢中獲得像這樣的結果會非常棒。用一次對 ResultSet 的遍歷來構造 Java 例項並不困難。

這時我記起 SQL 實際上是圖靈完備的。因此，我可以用 SQL 來表達這一點。問題只是如何表達！知道問題有解決方案總是很有幫助的。當你能夠讓你腦海中那個試圖說服你沒有解決方案、你只是在浪費時間的想法沉默下來時，找到解決方案就會容易得多。

行號（Row Numbers）

集合的元素透過行在 Minion 中的索引“連線”起來。但這個索引在資料庫中並不存在。幸運的是，您可以使用 row_number() 視窗函式相當容易地建立這樣的索引。

如果您不瞭解視窗函式（也稱為分析函式），它們類似於聚合函式，但 group by 不會將所有匹配的行摺疊成一行。相反，分析函式應用於由 group by 定義的視窗，並且結果在每一行中都可用。而且它並不總是對組中的所有行返回相同的結果。您可以使用這些函式做更多事情。您應該瞭解更多。但對於我們目前手頭的問題，我們只需要：

• row_number()，它為組中的所有行分配唯一且連續遞增的數字。
• count(*)，它計算組中的行數。我知道，很意外吧。

我們從為每個子表建立一個子查詢（subselect）開始。每個子查詢選擇底層表的所有列，一個 row_number() 和 count(*)，每個都按 minion_id 分組。

( 
  select *,
    row_number() over (partition by minion_id) h_rn,
    count(*) over (partition by minion_id) h_cnt
  from hobby
) h

我們實際上對聚合根也做了同樣的事情。但是，我們不需要 row_number，因為我們知道每行只有一個 Minion。因此，我們可以將其固定為 1。

( 
  select *,
    1 m_rn
  from minion
) m

按 ID 連線

接下來，我們使用標準的左連線（left join）將所有這些子查詢連線在一起

select *
from ( ... ) m
left join 
  ( ... ) h
  on m.id = h.minion_id
left join 
  ( ... ) t
  on m.id = t.minion_id

這正是上面我宣告不可接受的交叉積。

Minion id	m_rn	Minion name	toys name	t_rn	hobbies name	h_rn
1	1	Bob	Teddy	1	Hold Teddy	1
1	1	Bob	Blue Light	2	Hold Teddy	1
1	1	Bob	Teddy	1	Look Cute	2
1	1	Bob	Blue Light	2	Look Cute	2
1	1	Bob	Teddy	1	Follow Kevin	3
1	1	Bob	Blue Light	2	Follow Kevin	3
2	1	Kevin	...	...	...	...

我們想要的更類似於對不同行號的full outer join。不幸的是，在 SQL 中，您不能在一個列上使用 left join，在另一個列上使用 full outer join。但我們可以用一個 where 子句來解決這個問題。

基於行號的偽全外連線

where 子句的簡單版本是：

where m_rn = h_rn
and   m_rn = t_rn

這忽略了我們需要外連線語義的事實。為了解決這個問題，添加了許多 is null 檢查和與 cnt 列的比較，使得 where 子句相當難以閱讀。而且它足夠複雜，以至於我無法在不犯大量錯誤的情況下寫下來。因此，我在此省略細節。如果您真的想知道，請繼續並啟用 SQL 日誌記錄。

有了這個，我們將行數減少到了正確的數量。太棒了！但我們仍然在複製部分資料。

Minion id	m_rn	Minion name	toys name	t_rn	hobbies name	h_rn
1	1	Bob	Teddy	1	Hold Teddy	1
1	1	Bob	Blue Light	2	Look Cute	2
1	1	Bob	Teddy	1	Follow Kevin	3
2	1	Kevin	...	...	...	...

例如，對於沒有匹配玩具的興趣愛好，一個玩具的資料會被一遍又一遍地重複。我們真正想把它減少到 null 值。在玩具示例中這差別不大，但這些值可能是部落格文章上的長篇評論，透過網路傳輸需要花費相當多的時間。為此，我們用以下表達式替換了幾乎所有列：

case when x_rn = rn then name end

這裡的 x_rn 是作為列來源的子查詢的行號。rn 是總行號——即所有子查詢連線時所依據的行號。這個條件基本表達的意思是：如果子查詢對這一行有資料，就使用它；否則，只使用 null。我們將此模式用於所有普通列。只有用於後續連線的列（如下一段所述）才不受此處理。

現在我們的結果看起來正是我們想要的。

Minion id	m_rn	Minion name	toys name	t_rn	hobbies name	h_rn
1	1	Bob	Teddy	1	Hold Teddy	1
1	1		Blue Light	2	Look Cute	2
1	1				Follow Kevin	3
2	1	Kevin	...	...	...	...

我們返回最少數量的行，並且沒有重複的資料！但我們只對巢狀實體的一個層級這樣做！這透過簡單的遞迴解決：我們得到的結果看起來就像一個簡單的表格。因此，它可以像表格一樣使用。更準確地說，它可以替代新增行號的子查詢使用，因為它已經有了行號。

條件

到目前為止，我們基本上討論了 findAll 操作的查詢。大約半年前，我曾有一個適用於 findAll 但對於 findById 或 findByAddressName 等操作效率不高的解決方案。上面介紹的解決方案沒有這個問題。任何 where 子句都應用於聚合根的最內層 select，並且由於連線的存在，它會限制所有資料。這得到了您無論如何都會為外部索引鍵和 ID 建立的索引的良好支援，因此我們相信這種查詢方式可以高效執行。

展望

正如本文開頭所概述的，這種方法目前僅在 Spring Data JDBC、簡單聚合和非常特定的查詢方法中實現。我們希望將其應用於所有聚合、所有 Spring Data JDBC 查詢方法，甚至 Spring Data R2DBC。後者將使 Spring Data R2DBC 能夠讀取完整的聚合！這肯定會對您將來如何為 Spring Data Relational 指定查詢產生影響。當然，使用 Spring Data Relational 的下游專案也將從中受益。Spring 的 REST 和 GraphQL 支援就屬於此類。

關注這個 Github issue，瞭解關於此主題的更多進展。

結論

我們找到了一種透過單個查詢載入任意表格樹狀結構資料的方法。這與 Spring Data JDBC 完美契合，因為它處理的聚合就是這樣的樹狀結構。生成的查詢稍微複雜一些，但關係型資料庫管理系統（RDBMS）應該能夠高效地執行它們。

當然，我們現在正在尋找實際應用經驗和反饋：您是否遇到了問題？它對您的效能有影響嗎？請透過 Github 或 Stackoverflow 告知我們。