Типы соединений SQL

1. введение

В этом уроке мы покажем различные типы SQL-соединений и то, как они могут быть легко реализованы в Java.

2. Определение модели

Давайте начнем с создания двух простых таблиц:

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)
);

И заполните их некоторыми тестовыми данными:

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);

Обратите внимание, что в нашем образце набора данных не у всех авторов есть статьи, и наоборот. Это сыграет большую роль в наших примерах, которые мы увидим позже.

Давайте также определим POJO, который мы будем использовать для хранения результатов операций СОЕДИНЕНИЯ на протяжении всего нашего урока:

class ArticleWithAuthor {

    private String title;
    private String authorFirstName;
    private String authorLastName;

    // standard constructor, setters and getters
}

В наших примерах мы извлекем заголовок из таблицы СТАТЕЙ и данные об авторах из таблицы АВТОРОВ.

3. Конфигурация

Для наших примеров мы будем использовать внешнюю базу данных PostgreSQL, работающую на порту 5432. Помимо ПОЛНОГО СОЕДИНЕНИЯ, которое не поддерживается ни в MySQL, ни в H2, все предоставленные фрагменты должны работать с любым поставщиком SQL.

Для нашей реализации Java нам понадобится драйвер PostgreSQL :


    org.postgresql
    postgresql
    42.2.5
    test

Давайте сначала настроим java.sql.Connection для работы с нашей базой данных:

Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.
  getConnection("jdbc:postgresql://localhost:5432/myDb", "user", "pass");

Далее, давайте создадим класс DAO и некоторые служебные методы:

class ArticleWithAuthorDAO {

    private final Connection connection;

    // constructor

    private List 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 mapToList(ResultSet resultSet) throws SQLException {
        List 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;
    }
}

В этой статье мы не будем вдаваться в подробности об использовании ResultSet , Statement, и Connection. Эти темы рассматриваются в наших JDBC соответствующих статьях.

Давайте начнем изучать SQL-соединения в разделах ниже.

4. Внутреннее соединение

Давайте начнем с, возможно, самого простого типа соединения. ВНУТРЕННЕЕ СОЕДИНЕНИЕ – это операция, которая выбирает строки, соответствующие заданному условию, из обеих таблиц. Запрос состоит по крайней мере из трех частей: выбор столбцов, объединение таблиц и условие объединения.

Имея это в виду, сам синтаксис становится довольно простым:

SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME
  FROM ARTICLE INNER JOIN AUTHOR 
  ON AUTHOR.ID=ARTICLE.AUTHOR_ID

Мы также можем проиллюстрировать результат ВНУТРЕННЕГО СОЕДИНЕНИЯ как общую часть пересекающихся множеств:

Теперь давайте реализуем метод для ВНУТРЕННЕГО СОЕДИНЕНИЯ в классе ArticleWithAuthorDAO :

List 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);
}

И проверить это:

@Test
public void whenQueryWithInnerJoin_thenShouldReturnProperRows() {
    List articleWithAuthorList = articleWithAuthorDAO.articleInnerJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(4);
    assertThat(articleWithAuthorList)
      .noneMatch(row -> row.getAuthorFirstName() == null || row.getTitle() == null);
}

Как мы уже упоминали ранее, ВНУТРЕННЕЕ СОЕДИНЕНИЕ выбирает только общие строки по заданному условию. Глядя на наши вставки, мы видим, что у нас есть одна статья без автора и один автор без статьи. Эти строки пропускаются, потому что они не соответствуют заданному условию. В результате мы получаем четыре объединенных результата, и ни один из них не имеет пустых данных об авторах или пустого заголовка.

5. Левое соединение

Далее, давайте сосредоточимся на ЛЕВОМ СОЕДИНЕНИИ. Этот вид соединения выбирает все строки из первой таблицы и сопоставляет соответствующие строки из второй таблицы. Если совпадения нет, столбцы заполняются null значениями .

Прежде чем мы погрузимся в реализацию Java, давайте взглянем на графическое представление ЛЕВОГО СОЕДИНЕНИЯ:

В этом случае результат СОЕДИНЕНИЯ LEFT включает в себя каждую запись из набора, представляющего первую таблицу, с пересекающимися значениями из второй таблицы.

Теперь давайте перейдем к реализации Java:

List 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);
}

Единственное отличие от предыдущего примера заключается в том, что мы использовали ЛЕВОЕ ключевое слово вместо ВНУТРЕННЕГО ключевого слова.

Прежде чем мы протестируем наш метод ЛЕВОГО СОЕДИНЕНИЯ, давайте еще раз взглянем на наши вставки. В этом случае мы получим все записи из таблицы СТАТЕЙ и соответствующие им строки из таблицы АВТОРОВ. Как мы уже упоминали ранее, не у каждой статьи есть автор, поэтому мы ожидаем, что вместо данных автора будут иметь null значения:

@Test
public void whenQueryWithLeftJoin_thenShouldReturnProperRows() {
    List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}

6. Правое соединение

ПРАВОЕ СОЕДИНЕНИЕ очень похоже на ЛЕВОЕ, но оно возвращает все строки из второй таблицы и соответствует строкам из первой таблицы. Как и в случае ЛЕВОГО СОЕДИНЕНИЯ, пустые совпадения заменяются значениями null .

Графическое представление такого рода соединения является зеркальным отражением того, которое мы проиллюстрировали для ЛЕВОГО СОЕДИНЕНИЯ:

Давайте реализуем ПРАВИЛЬНОЕ СОЕДИНЕНИЕ в Java:

List 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);
}

Опять же, давайте посмотрим на наши тестовые данные. Поскольку эта операция соединения извлекает все записи из второй таблицы, мы ожидаем получить пять строк, и поскольку не каждый автор уже написал статью, мы ожидаем, что некоторые null значения в столбце TITLE:

@Test
public void whenQueryWithRightJoin_thenShouldReturnProperRows() {
    List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
}

7. Полное Внешнее Соединение

Эта операция соединения, вероятно, самая сложная. ПОЛНОЕ СОЕДИНЕНИЕ выбирает все строки как из первой, так и из второй таблицы независимо от того, выполнено условие или нет.

Мы также можем представить ту же идею, что и все значения из каждого пересекающегося множества:

Давайте взглянем на реализацию Java:

List 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);
}

Теперь мы можем проверить наш метод:

@Test
public void whenQueryWithFullJoin_thenShouldReturnProperRows() {
    List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(6);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}

Еще раз давайте посмотрим на тестовые данные. У нас есть пять разных статей, одна из которых не имеет автора, и четыре автора, у одного из которых нет назначенной статьи. В результате ПОЛНОГО СОЕДИНЕНИЯ мы ожидаем получить шесть строк. Четыре из них сопоставлены друг с другом, а остальные два-нет. По этой причине мы также предполагаем, что будет по крайней мере одна строка со значениями null в обоих столбцах данных АВТОРА и одна строка со значением null в столбце ЗАГОЛОВКА.

8. Заключение

В этой статье мы рассмотрели основные типы SQL-соединений. Мы рассмотрели примеры четырех типов соединений и то, как они могут быть реализованы в Java.

Как всегда, полный код, используемый в этой статье, доступен на GitHub .