Запросы соединения таблиц и пагинация в JPA

Запросы соединения таблиц в JPA довольно распространены. Поскольку JPA позволяет определять пользовательские SQL-запросы на уровне репозиториев, реализация соединения таблиц с помощью пользовательских SQL-запросов довольно проста. Эта статья написана на основе предыдущей статьи Основы JPA, поэтому рекомендуется ознакомиться с ней, если вы не знакомы с JPA.

На основе предыдущей статьи мы создали два новых сущностных класса, как показано ниже:

Создание связанных сущностных классов

Company.java

@Entity
@Data
@NoArgsConstructor
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String companyName;
    private String description;

    public Company(String name, String description) {
        this.companyName = name;
        this.description = description;
    }
}

School.java

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class School {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String name;
    private String description;
}

Использование пользовательских SQL-запросов для реализации соединения таблиц

Предположим, что мы хотим получить информацию о Person по его идентификатору из таблицы person. Мы знаем, что информация о Person распределена по трем таблицам: Company, School и Person. Поэтому для получения всей информации о Person нам потребуется выполнить запрос соединения таблиц.Сначала нам нужно создать объект DTO, который будет содержать необходимую информацию о Person. Мы назовем его UserDTO и используем его для хранения и передачи нужных данных.

@Data
@NoArgsConstructor
@Builder(toBuilder = true)
@AllArgsConstructor
public class UserDTO {
    private String name;
    private int age;
    private String companyName;
    private String schoolName;
}

Теперь мы напишем метод для получения базовой информации о Person.

    /**
     * Запрос соединения таблиц
     */
    @Query(value = "select new github.snailclimb.jpademo.model.dto.UserDTO(p.name, p.age, c.companyName, s.name) " +
            "from Person p left join Company c on p.companyId = c.id " +
            "left join School s on p.schoolId = s.id " +
            "where p.id = :personId")
    Optional<UserDTO> getUserInformation(@Param("personId") Long personId);

Можно заметить, что приведённые выше SQL-запросы не сильно отличаются от тех, которые мы обычно используем. Основное отличие заключается в использовании операции с объектом new.

Создание пользовательского SQL-запроса для объединения таблиц и реализации пагинации

Если нам нужно получить текущую информацию о всех пользователях и реализовать пагинацию, то можно использовать следующий подход. Обратите внимание, что для реализации пагинации мы добавили свойство countQuery в аннотации @Query.

@Query(value = "select new github.snailclimb.jpademo.model.dto.UserDTO(p.name, p.age, c.companyName, s.name) " +
        "from Person p left join Company c on p.companyId = c.id " +
        "left join School s on p.schoolId = s.id ",
        countQuery = "select count(p.id) " +
                "from Person p left join Company c on p.companyId = c.id " +
                "left join School s on p.schoolId = s.id ")
Page<UserDTO> getUserInformationList(Pageable pageable);
```Пример использования:

```java
// Параметры пагинации
PageRequest pageRequest = PageRequest.of(0, 3, Sort.Direction.DESC, "age");
Page<UserDTO> userInformationList = personRepository.getUserInformationList(pageRequest);
// Общее количество элементов
System.out.println(userInformationList.getTotalElements()); // 6
// Общее количество страниц
System.out.println(userInformationList.getTotalPages()); // 2
System.out.println(userInformationList.getContent());

Дополнительно: другие применения пользовательских SQL-запросов

Вот два наиболее часто используемых примера:

1. Запрос с использованием оператора IN
2. Запрос с использованием оператора BETWEEN

Конечно, существует множество других применений, которые вам стоит попробовать самостоятельно.

Запрос с использованием оператора IN

Если вам нужно выбрать записи, соответствующие одному из нескольких значений, вы можете использовать запрос с оператором IN. В контексте JPA это выглядит следующим образом:

@Query(value = "select new github.snailclimb.jpademo.model.dto.UserDTO(p.name,p.age,c.companyName,s.name) " +
        "from Person p left join Company c on  p.companyId=c.id " +
        "left join School s on p.schoolId=s.id " +
        "where p.name IN :peopleList")
List<UserDTO> filterUserInfo(List peopleList);

Пример использования:

List<String> personList = new ArrayList<>(Arrays.asList("person1", "person2"));
List<UserDTO> userDTOS = personRepository.filterUserInfo(personList);

Запрос с использованием оператора BETWEEN

Запрос, который находит значения, соответствующие определенному диапазону. Например, следующий метод реализует запрос для поиска информации о людях, возраст которых находится в определенном диапазоне.```java @Query(value = "select new github.snailclimb.jpademo.model.dto.UserDTO(p.name,p.age,c.companyName,s.name) " + "from Person p left join Company c on p.companyId=c.id " + "left join School s on p.schoolId=s.id " + "where p.age between :small and :big") List filterUserInfoByAge(int small, int big);

Практическое использование:

```java
List<UserDTO> userDTOS = personRepository.filterUserInfoByAge(19, 20);

Заключение

В этом разделе мы изучили следующие ключевые моменты:

1. Создание пользовательских SQL-запросов для выполнения связанных запросов;
2. Создание пользовательских SQL-запросов для выполнения связанных запросов и реализации пагинации;
3. Условные запросы: IN-запросы, BETWEEN-запросы.