jOOQ（Java 面向对象查询）是一个功能强大的库，它使我们能够以面向对象的方式编写 SQL 查询，从而简化了 Java 中的数据库交互。连接表是关系数据库中的基本操作，允许我们根据特定条件组合多个表中的数据。在本教程中，我们将探索 jOOQ 中可用的各种类型的联接。
设置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);
}
}
|
我们将在整个教程中使用上下文对象与数据库交互：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();
}
|
使用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。
使用外连接
除了默认的内连接之外，jOOQ还支持各种连接类型，例如外连接。即使连接表中没有匹配的记录，外连接也允许我们检索记录。
1.左外连接
左联接包括左表Book中的所有行以及右表BookAuthor中的匹配行。右表中任何不匹配的行对于特定于作者的列都将具有空值。
让我们看看如何使用 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）具有空值。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());
|
与左外连接类似，在输出中，最后两位作者没有关联的图书记录，导致空值：
+------+---------+---------+-----------------+--------+----+-------------+-----------+
| 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|
+------+---------+---------+-----------------+--------+----+-------------+-----------+
|
3.全外连接
完整外连接合并表Book和BookAuthor中的所有行，无论是否存在匹配。在相反的表中没有匹配项的行的该表中的列具有空值。
要在 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());
|
使用自然连接
自然连接根据匹配的列名自动确定连接条件。当连接条件使用像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|
+----+---------+---------+-----------------+--------+----+-------------+-------+
|
使用交叉连接
交叉联接是最基本的联接类型，其中一个表中的每一行都与另一个表中的每一行组合。这在我们有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 中连接表。我们讨论了各种类型的联接，包括内联接、外联接（左联接、右联接和全外联接）、自然联接和交叉联接。此外，我们发现自然连接和交叉连接可能很有用，但由于潜在的意外结果或性能问题，应谨慎使用，尤其是对于大型数据集。