使用 jOOQ 連接兩個表
一、簡介
jOOQ（Java 物件導向查詢）是一個強大的函式庫，它使我們能夠以物件導向的方式編寫 SQL 查詢，從而簡化了 Java 中的資料庫互動。連接表是關係資料庫中的基本操作，允許我們根據特定條件組合多個表中的資料。在本教程中，我們將探索 jOOQ 中可用的各種類型的聯結。
2. 設定jOOQ
使用 jOOQ 連接兩個表涉及利用 jOOQ 提供的 DSL（域特定語言）來建立 SQL 查詢。
要使用 jOOQ，我們需要將jOOQ和PostgreSQL依賴項新增到 Maven 專案的pom.xml檔案中：
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>在使用join之前，我們需要使用jOOQ建立與資料庫的連線。我們建立一個方法getConnection()來取得用於資料庫互動的DSLContext物件：
public static DSLContext getConnection() {
try {
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DSLContext context = DSL.using(conn, SQLDialect.POSTGRES);
return context;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}我們將在整個教程中使用context物件與資料庫互動：
DSLContext context = DBConnection.getConnection();此外，jOOQ 提供了一個程式碼產生器，可以根據我們的資料庫模式產生 Java 類別。我們假設表Store 、 Book和BookAuthor是在資料庫中使用各自的架構建立的。
接下來，我們可以使用DSLContext物件在註釋為@BeforeClass方法中插入測試數據，以確保它在每次測試之前運行。讓我們將測試資料插入整合到我們的設定方法中：
@BeforeClass
public static void setUp() throws Exception {
context = DBConnection.getConnection();
context.insertInto(Tables.STORE, Store.STORE.ID, Store.STORE.NAME)
.values(1, "ABC Branch I ")
.values(2, "ABC Branch II")
.execute();
context.insertInto(Tables.BOOK, Book.BOOK.ID, Book.BOOK.TITLE, Book.BOOK.DESCRIPTION,
Book.BOOK.AUTHOR_ID, Book.BOOK.STORE_ID)
.values(1, "Article 1", "This is article 1", 1, 1)
.values(2, "Article 2", "This is article 2", 2, 2)
.values(3, "Article 3", "This is article 3", 1, 2)
.values(4, "Article 4", "This is article 4", 5, 1)
.execute();
context.insertInto(Tables.BOOKAUTHOR, Bookauthor.BOOKAUTHOR.ID, Bookauthor.BOOKAUTHOR.NAME,
Bookauthor.BOOKAUTHOR.COUNTRY)
.values(1, "John Smith", "Japan")
.values(2, "William Walce", "Japan")
.values(3, "Marry Sity", "South Korea")
.values(4, "Morry Toh", "England")
.execute();
}3. 使用join子句
在 jOOQ 中， SelectJoinStep<Record>是一個接口，表示建構帶有聯接的SELECT查詢的過程中的一個步驟。我們可以使用select()等方法來指定要從相關表格中檢索哪些欄位。
jOOQ中的join()方法用於根據指定條件在表之間執行內部連接。內部聯結檢索兩個表中都符合特定條件的行。
以下是根據作者 ID 連線Book和BookAuthor表的範例：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.join(Tables.BOOKAUTHOR)
.on(field(Tables.BOOK.AUTHOR_ID).eq(field(Tables.BOOKAUTHOR.ID)));
assertEquals(3, query.fetch().size());這是一個示範連接多個表格的擴充範例：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.join(Tables.BOOKAUTHOR)
.on(field(Tables.BOOK.AUTHOR_ID).eq(field(Tables.BOOKAUTHOR.ID)))
.join(Tables.STORE)
.on(field(Tables.BOOK.STORE_ID).eq(field(Tables.STORE.ID)));
assertEquals(3, query.fetch().size());我們在Store表格中新增了另一個聯接。此連線操作會根據Book表中的STORE_ID欄位和Store表中的 ID 欄位連接Book和Store表。透過新增此附加聯接，查詢現在從三個表中檢索資料： Book 、 BookAuthor和Store 。
4. 使用外連接
除了預設的內連接之外，jOOQ還支援各種連接類型，例如外部連接。即使連接表中沒有匹配的記錄，外部連接也允許我們檢索記錄。
4.1.左外連接
左聯接包括左表Book中的所有行以及右表BookAuthor中的匹配行。右表中任何不匹配的行對於特定於作者的列都將具有null值。
讓我們看看如何使用 jOOQ 執行左外連線：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.leftOuterJoin(Tables.BOOKAUTHOR)
.on(field(Tables.BOOK.AUTHOR_ID).eq(field(Tables.BOOKAUTHOR.ID)));
assertEquals(4, query.fetch().size());在輸出中，最後一行的作者列顯示null ，而不是對應的作者條目：
+----+---------+---------+-----------------+--------+------+-------------+-------+
| id|author_id|title |description |store_id| id|name |country|
+----+---------+---------+-----------------+--------+------+-------------+-------+
| 1| 1| Book 1|This is book 1| 1| 1|John Smith |Japan |
| 2| 2| Book 2|This is book 2| 2| 2|William Walce|Japan |
| 3| 1| Book 3|This is book 3| 2| 1|John Smith |Japan |
| 4| 5| Book 4|This is book 4| 1|{null}|{null} |{null} |
+----+---------+---------+-----------------+--------+------+-------------+-------+執行左外連接時，如查詢所示，左表Book中的所有行都包含在結果集中。在這種情況下，即使BookAuthor表中最後一行沒有匹配的author_id ，它仍然出現在輸出中。但是，由於BookAuthor表中沒有可用的相應數據，因此該行的特定於作者的列（ id 、 name 、 country ）具有null值。
4.2.右外連接
相反，右連接包含右表BookAuthor中的所有行，並將它們與左表Book中的行進行比對。左表中與右表中任何條目都不匹配的行對於特定於書籍的列將具有空值。
讓我們看看如何使用 jOOQ 執行右外連線：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.rightOuterJoin(Tables.BOOKAUTHOR)
.on(field(Tables.BOOK.AUTHOR_ID).eq(field(Tables.BOOKAUTHOR.ID)));
assertEquals(5, query.fetch().size());與左外連接類似，在輸出中，最後兩位作者沒有關聯的圖書記錄，導致null值：
+------+---------+---------+-----------------+--------+----+-------------+-----------+
| id|author_id|title |description |store_id| id|name | country|
+------+---------+---------+-----------------+--------+----+-------------+-----------+
...
|{null}| {null}|{null} |{null} | {null}| 4|Morry Toh |England |
|{null}| {null}|{null} |{null} | {null}| 3|Marry Sity |South Korea|
+------+---------+---------+-----------------+--------+----+-------------+-----------+4.3.全外連接
完整外連接合併表Book和BookAuthor中的所有行，無論是否有符合。在相反的表中沒有匹配項的行的該表中的列具有null值。
要在 jOOQ 中執行完全外部連接，我們可以使用以下語法：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.fullOuterJoin(Tables.BOOKAUTHOR)
.on(field(Tables.BOOK.AUTHOR_ID).eq(field(Tables.BOOKAUTHOR.ID)));
assertEquals(6, query.fetch().size());5. 使用自然連接
自然連線根據符合的列名自動決定連接條件。當使用公共列（例如，連接條件很簡單）時，這會很有幫助 AUTHOR_ID ：
SelectJoinStep<Record> query = context.select()
.from(Tables.BOOK)
.naturalJoin(Tables.BOOKAUTHOR);
assertEquals(4, query.fetch().size());但是，如果列名不是用於連接或資料類型不匹配，則可能會出現意外結果。在輸出中，我們觀察到其中一筆記錄匹配不正確：
+----+---------+---------+-----------------+--------+----+-------------+-------+
| id|author_id|title |description |store_id| id|name |country|
+----+---------+---------+-----------------+--------+----+-------------+-------+
...
| 4| 5| Book 4|This is book 4| 1| 4|Morry Toh |England|
+----+---------+---------+-----------------+--------+----+-------------+-------+6. 使用交叉連接
交叉聯結是最基本的聯接類型，其中一個表中的每一行都與另一個表中的每一行組合。這在我們有Store和Book表的特定場景中非常有用。我們想要顯示所有可能的商店-書籍組合的清單。
讓我們檢查一下執行交叉連接時的結果：
SelectJoinStep<Record> query = context.select()
.from(Tables.STORE)
.crossJoin(Tables.BOOK);
assertEquals(8, query.fetch().size());交叉連接有效地產生每種可能的組合，使我們能夠展示「 Branch I – Book 1 」、「 Branch I – Book 2 」等選項。但是，應謹慎使用交叉聯接，因為可能會建立非常大的資料集，特別是在涉及的表有很多行的情況下。
七、結論
在這篇文章中，我們學習如何在 jOOQ 中連接表格。我們討論了各種類型的聯接，包括內聯接、外聯接（左聯接、右聯接和全外聯接）、自然聯結和交叉聯接。此外，我們發現自然連接和交叉連接可能很有用，但由於潛在的意外結果或效能問題，應謹慎使用，尤其是對於大型資料集。
與往常一樣，範例的原始程式碼可在 GitHub 上取得。