太長不看 (TL;DR)

從 Spring Data JDBC 3.2.0-M2 開始，Spring Data JDBC 支援單一查詢載入。單一查詢載入使用單一 select 語句載入任意聚合。

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

在 3.2.0-M2 中，這僅適用於簡單的聚合，包含一個聚合根和一個其他實體的集合。它也僅限於 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 使用不同的策略來盡量減少這個問題。例如，它們可能會將一個子實體加入到聚合根。或者，它們可能會對相關實體使用批次載入。所有這些方法都限制了問題的影響，但它們僅僅是治標不治本。此外，大多數人實際上會告訴你，你真的無法在單一查詢中做到這一點，因為你會得到所有子表的交叉乘積，這可能非常糟糕。想像一下 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 名稱	玩具名稱	嗜好名稱
1	Bob	Teddy	抱抱 Teddy
		藍光	裝可愛
			跟著 Kevin
2	Kevin	...	...

從查詢中獲得這樣的結果真的很好。從 ResultSet 中單次遍歷來構建 Java 實例應該不難。

此時我記得 SQL 實際上是圖靈完備的。因此，我可以用 SQL 表達這個。這只是一個如何表達的問題！知道問題有解決方案總是有幫助的。當您可以關閉腦海中原本試圖說服您沒有解決方案，而您只是在浪費時間的聲音時，找到解決方案就會變得容易得多。

列號

集合的元素有點像是被 Minion 中的行索引「連接」起來的。但是該索引在資料庫中不存在。幸運的是，您可以使用 row_number() 視窗函數輕鬆創建這樣的索引。

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

• row_number()，它為組中的所有行分配一個唯一的、連續遞增的數字。
• count(*)，它計算組中的行數。我知道，很令人驚訝。

我們首先為每個子表創建一個子查詢。每個子查詢選擇底層表中的所有列、一個 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 名稱	玩具名稱	t_rn	嗜好名稱	h_rn
1	1	Bob	Teddy	1	抱抱 Teddy	1
1	1	Bob	藍光	2	抱抱 Teddy	1
1	1	Bob	Teddy	1	裝可愛	2
1	1	Bob	藍光	2	裝可愛	2
1	1	Bob	Teddy	1	跟著 Kevin	3
1	1	Bob	藍光	2	跟著 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 名稱	玩具名稱	t_rn	嗜好名稱	h_rn
1	1	Bob	Teddy	1	抱抱 Teddy	1
1	1	Bob	藍光	2	裝可愛	2
1	1	Bob	Teddy	1	跟著 Kevin	3
2	1	Kevin	...	...	...	...

例如，對於沒有匹配玩具的嗜好，一個玩具的資料會一遍又一遍地重複。我們真的想將其減少為 null 值。在這個玩具範例中，這沒有太大的區別，但是這些值可能是部落格文章上的長篇評論，並且需要相當長的時間才能透過網路傳輸。為此，我們將幾乎所有列都替換為如下所示的表達式

case when x_rn = rn then name end

這裡 x_rn 是子查詢的 row_number，它是問題中列的來源。rn 是總 row_number - 也就是說，所有子查詢都連接到的 row_number。這個條件基本上表示：如果子查詢有此行的資料，則使用它；否則，僅使用 null。我們在所有普通列上使用此模式。只有在以下段落中描述的進一步連接中使用的列才不會使用這種方式處理。

現在我們的結果看起來就像期望的那樣。

Minion ID	m_rn	Minion 名稱	玩具名稱	t_rn	嗜好名稱	h_rn
1	1	Bob	Teddy	1	抱抱 Teddy	1
1	1		藍光	2	裝可愛	2
1	1				跟著 Kevin	3
2	1	Kevin	...	...	...	...

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

條件

到目前為止，我們基本上查看了 findAll 操作的查詢。大約半年前，我已經有一個適用於 findAll 的解決方案，但對於 findById 或 findByAddressName 之類的東西沒有產生有效的解決方案。這不是上面提出的解決方案的問題。任何 where 子句都應用於聚合根的最內層 select，並且由於 joins，限制了所有資料。索引可以很好地支援這一點，您無論如何都會為外鍵和 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 告訴我們。