1. Introduction
1.介绍
In this tutorial, we’ll show different types of SQL joins and how they can be easily implemented in Java.
在本教程中，我们将展示不同类型的SQL连接以及如何在Java中轻松实现它们。
2. Defining the Model
2.定义模型
Let’s start by creating two simple tables:
让我们从创建两个简单的表开始。
CREATE TABLE AUTHOR
(
ID int NOT NULL PRIMARY KEY,
FIRST_NAME varchar(255),
LAST_NAME varchar(255)
);
CREATE TABLE ARTICLE
(
ID int NOT NULL PRIMARY KEY,
TITLE varchar(255) NOT NULL,
AUTHOR_ID int,
FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(ID)
);
And fill them with some test data:
并用一些测试数据填充它们。
INSERT INTO AUTHOR VALUES
(1, 'Siena', 'Kerr'),
(2, 'Daniele', 'Ferguson'),
(3, 'Luciano', 'Wise'),
(4, 'Jonas', 'Lugo');
INSERT INTO ARTICLE VALUES
(1, 'First steps in Java', 1),
(2, 'SpringBoot tutorial', 1),
(3, 'Java 12 insights', null),
(4, 'SQL JOINS', 2),
(5, 'Introduction to Spring Security', 3);Note that in our sample data set, not all authors have articles, and vice-versa. This will play a big part in our examples, which we’ll see later.
请注意，在我们的样本数据集中，并非所有作者都有文章，反之亦然。这将在我们的例子中起到很大的作用，我们将在后面看到。
Let’s also define a POJO that we’ll use for storing the results of JOIN operations throughout our tutorial:
让我们也定义一个POJO，我们将在整个教程中用来存储JOIN操作的结果。
class ArticleWithAuthor {
private String title;
private String authorFirstName;
private String authorLastName;
// standard constructor, setters and getters
}In our examples, we’ll extract a title from the ARTICLE table and authors data from the AUTHOR table.
在我们的例子中，我们将从ARTICLE表中提取一个标题，从AUTHOR表中提取作者数据。
3. Configuration
3.配置
For our examples, we’ll use an external PostgreSQL database running on port 5432. Apart from the FULL JOIN, which is not supported in either MySQL or H2, all provided snippets should work with any SQL provider.
在我们的例子中，我们将使用一个运行在5432端口的外部PostgreSQL数据库。除了MySQL和H2都不支持的FULL JOIN之外，所有提供的片段都可以在任何SQL提供者中使用。
For our Java implementation, we’ll need a PostgreSQL driver:
对于我们的Java实现，我们将需要一个PostgreSQL驱动。
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
<scope>test</scope>
</dependency>Let’s first configure a java.sql.Connection to work with our database:
让我们首先配置一个java.sql.Connection来与我们的数据库一起工作。
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.
getConnection("jdbc:postgresql://localhost:5432/myDb", "user", "pass");Next, let’s create a DAO class and some utility methods:
接下来，让我们创建一个DAO类和一些实用方法。
class ArticleWithAuthorDAO {
private final Connection connection;
// constructor
private List<ArticleWithAuthor> executeQuery(String query) {
try (Statement statement = connection.createStatement()) {
ResultSet resultSet = statement.executeQuery(query);
return mapToList(resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
return new ArrayList<>();
}
private List<ArticleWithAuthor> mapToList(ResultSet resultSet) throws SQLException {
List<ArticleWithAuthor> list = new ArrayList<>();
while (resultSet.next()) {
ArticleWithAuthor articleWithAuthor = new ArticleWithAuthor(
resultSet.getString("TITLE"),
resultSet.getString("FIRST_NAME"),
resultSet.getString("LAST_NAME")
);
list.add(articleWithAuthor);
}
return list;
}
}In this article, we’ll not dive into details about using ResultSet, Statement, and Connection. These topics are covered in our JDBC related articles.
在这篇文章中，我们将不深入讨论关于使用ResultSet、Statement、和Connection的细节。这些主题在我们的JDBC相关文章中有所涉及。
Let’s start exploring SQL joins in sections below.
让我们在下面的章节中开始探索SQL连接。
4. Inner Join
4.内部连接
Let’s start with possibly the simplest type of join. The INNER JOIN is an operation that selects rows matching a provided condition from both tables. The query consists of at least three parts: select columns, join tables and join condition.
让我们从可能是最简单的连接类型开始。内联（INNER JOIN）是一个从两个表中选择与提供的条件相匹配的记录的操作。查询至少由三部分组成：选择列、连接表和连接条件。
Bearing that in mind, the syntax itself becomes pretty straightforward:
考虑到这一点，语法本身就变得非常简单了。
SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME
FROM ARTICLE INNER JOIN AUTHOR
ON AUTHOR.ID=ARTICLE.AUTHOR_IDWe can also illustrate the result of INNER JOIN as a common part of intersecting sets:
我们还可以说明INNER JOIN的结果是相交集的一个共同部分：。
Let’s now implement the method for the INNER JOIN in the ArticleWithAuthorDAO class:
现在让我们在ArticleWithAuthorDAO类中实现INNER JOIN的方法。
List<ArticleWithAuthor> articleInnerJoinAuthor() {
String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
+ "FROM ARTICLE INNER JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
return executeQuery(query);
}And test it:
并测试它。
@Test
public void whenQueryWithInnerJoin_thenShouldReturnProperRows() {
List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleInnerJoinAuthor();
assertThat(articleWithAuthorList).hasSize(4);
assertThat(articleWithAuthorList)
.noneMatch(row -> row.getAuthorFirstName() == null || row.getTitle() == null);
}As we mentioned before, the INNER JOIN selects only common rows by a provided condition. Looking at our inserts, we see that we have one article without an author and one author without an article. These rows are skipped because they don’t fulfill the provided condition. As a result, we retrieve four joined results, and none of them has empty authors data nor empty title.
正如我们之前提到的，INNER JOIN只通过提供的条件选择共同的行。看一下我们的插入，我们看到有一篇文章没有作者，有一个作者没有文章。这些行被跳过，因为它们不符合所提供的条件。结果，我们检索到四个连接结果，其中没有一个是空的作者数据或空的标题。
5. Left Join
5 左侧连接
Next, let’s focus on the LEFT JOIN. This kind of join selects all rows from the first table and matches corresponding rows from the second table. For when there is no match, columns are filled with null values.
接下来，让我们关注一下LEFT JOIN。这种连接从第一个表中选择所有行，并从第二个表中匹配相应的行。当没有匹配时，列会被填充为null 值。
Before we dive into Java implementation, let’s have a look at a graphical representation of the LEFT JOIN:
在我们深入研究Java实现之前，让我们看看LEFT JOIN的图形表示。
In this case, the result of the LEFT JOIN includes every record from the set representing the first table with intersecting values from the second table.
在这种情况下，LEFT JOIN的结果包括代表第一个表的集合中的每一条记录，并与第二个表的值相交。
Now, let’s move to the Java implementation:
现在，让我们转到Java的实现。
List<ArticleWithAuthor> articleLeftJoinAuthor() {
String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
+ "FROM ARTICLE LEFT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
return executeQuery(query);
}The only difference to the previous example is that we used the LEFT keyword instead of the INNER keyword.
与之前的例子唯一不同的是，我们使用了LEFT关键字而不是INNER关键字。
Before we test our LEFT JOIN method, let’s again take a look at our inserts. In this case, we’ll receive all the records from the ARTICLE table and their matching rows from the AUTHOR table. As we mentioned before, not every article has an author yet, so we expect to have null values in place of author data:
在我们测试LEFT JOIN方法之前，让我们再次看看我们的插入。在这种情况下，我们将接收来自ARTICLE表的所有记录和AUTHOR表的匹配行。正如我们之前提到的，不是每篇文章都有作者，所以我们希望有null值来代替作者数据。
@Test
public void whenQueryWithLeftJoin_thenShouldReturnProperRows() {
List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor();
assertThat(articleWithAuthorList).hasSize(5);
assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}6. Right Join
6.右键连接
The RIGHT JOIN is much like the LEFT JOIN, but it returns all rows from the second table and matches rows from the first table. Like in case of the LEFT JOIN, empty matches are replaced by null values.
RIGHT JOIN与LEFT JOIN很相似，但是它返回第二张表中的所有记录，并与第一张表中的记录相匹配。与LEFT JOIN的情况一样，空匹配被替换为null值。
The graphical representation of this kind of join is a mirror reflection of the one we’ve illustrated for the LEFT JOIN:
这种连接的图形表示是我们为LEFT JOIN所说明的图形的镜像反映。
Let’s implement the RIGHT JOIN in Java:
让我们在Java中实现RIGHT JOIN。
List<ArticleWithAuthor> articleRightJoinAuthor() {
String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
+ "FROM ARTICLE RIGHT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
return executeQuery(query);
}Again, let’s look at our test data. Since this join operation retrieves all records from the second table we expect to retrieve five rows, and because not every author has already written an article, we expect some null values in the TITLE column:
再一次，让我们看看我们的测试数据。因为这个连接操作从第二个表中检索所有的记录，我们期望检索到五条记录，而且因为不是每个作者都已经写了一篇文章，我们期望在TITLE列中有一些null值。
@Test
public void whenQueryWithRightJoin_thenShouldReturnProperRows() {
List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor();
assertThat(articleWithAuthorList).hasSize(5);
assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
}7. Full Outer Join
7.全外链
This join operation is probably the most tricky one. The FULL JOIN selects all rows from both the first and the second table regardless of whether the condition is met or not.
这个连接操作可能是最棘手的。FULL JOIN从第一个和第二个表中选择所有的记录，无论条件是否满足。
We can also represent the same idea as all values from each of the intersecting sets:
我们也可以用每个相交集的所有值来表示同样的想法。
Let’s have a look at the Java implementation:
让我们看一下Java的实现。
List<ArticleWithAuthor> articleOuterJoinAuthor() {
String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
+ "FROM ARTICLE FULL JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
return executeQuery(query);
}Now, we can test our method:
现在，我们可以测试我们的方法。
@Test
public void whenQueryWithFullJoin_thenShouldReturnProperRows() {
List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor();
assertThat(articleWithAuthorList).hasSize(6);
assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}Once more, let’s look at the test data. We have five different articles, one of which has no author, and four authors, one of which has no assigned article. As a result of the FULL JOIN, we expect to retrieve six rows. Four of them are matched against each other, and the remaining two are not. For that reason, we also assume that there will be at least one row with null values in both AUTHOR data columns and one with a null value in the TITLE column.
再一次，让我们看一下测试数据。我们有五篇不同的文章，其中一篇没有作者，还有四位作者，其中一位没有指定文章。作为FULL JOIN的结果，我们期望检索到六条记录。其中四条是相互匹配的，其余两条是不匹配的。由于这个原因，我们还假设至少有一行在AUTHOR数据列中都有null值，还有一行在TITLE列中有null值。
8. Conclusion
8.结论
In this article, we explored the basic types of SQL joins. We looked at examples of four types of joins and how they can be implemented in Java.
在这篇文章中，我们探讨了SQL连接的基本类型。我们看了四种类型的连接的例子以及如何在Java中实现它们。
As always, the complete code used in this article is available over on GitHub.
一如既往，本文中使用的完整代码可在GitHub上找到。