Spring АйО

✨ Spring Tips: Рекурсивные запросы в Hibernate



Рекурсивные запросы в SQL очень полезны при работе с иерархическими или графовыми структурами данных. Конструкция WITH, введенная в SQL:1999, позволяет задавать Common Table Expressions (CTE), которые представляют собой именованные подзапросы. CTE упрощают сложные запросы, улучшают их читаемость и, что самое важное, позволяют нам реализовать рекурсию.



Рассмотрим таблицу, которая хранит информацию о сотрудниках и их менеджерах:

CREATE TABLE employees (

  employee_id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,

  employee_name VARCHAR(255),

  manager_id INTEGER,

  CONSTRAINT pk_employees PRIMARY KEY (employee_id)

);



ALTER TABLE employees ADD CONSTRAINT 

FK_EMPLOYEES_ON_MANAGER FOREIGN KEY (manager_id) 

REFERENCES employees (employee_id);

Эта таблица представляет собой список сотрудников, где у каждого сотрудника есть уникальный идентификатор (employee_id), имя (employee_name) и ссылка на менеджера (manager_id). Поле manager_id является внешним ключом, ссылающимся на employee_id в той же таблице, что позволяет создать иерархическую структуру, где один сотрудник может быть менеджером для других.



Предположим, что нам нужно выбрать всех подчиненных определенного менеджера. Для решения этой задачи мы можем использовать рекурсивный SQL запрос:

WITH RECURSIVE EmployeeHierarchy AS (

    -- Базовый случай: начнем с верхнего уровня менеджера

    SELECT employee_id, employee_name, manager_id

    FROM employees

    WHERE manager_id = :id



    UNION ALL



    -- Рекурсивный случай: найдем всех сотрудников, подчиненных найденным ранее сотрудникам

    SELECT e.employee_id, e.employee_name, e.manager_id

    FROM employees e

    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

)



SELECT *

FROM EmployeeHierarchy;

До выхода версии Hibernate 6.2, для выполнения подобных запросов приходилось использовать nativeQuery. Пример с использованием entityManager выглядит следующим образом:

var sql = {SQL-запрос, представленный выше};



var employees = entityManager.createNativeQuery(sql, Employee.class)

        .setParameter("id", 1)

        .getResultList();

Либо так, если используется Spring Data JPA:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    @Query(value = "{SQL-запрос, представленный выше}", nativeQuery = true)

    List<Employee> findAllEmployeesByManagerIdSQL(@Param("id") Integer id);

}



List<Employee> employees = employeeRepository.findAllEmployeesByManagerIdSQL(1);

С выходом Hibernate 6.2, появилась возможность писать рекурсивные запросы, используя HQL. Запрос, представленный выше, теперь можно написать так:

String jpql = """

        WITH EmployeeHierarchy AS (

            SELECT e.employeeId AS id, e.employeeName AS name, e.manager AS mgr

            FROM Employee e

            WHERE e.manager.id = :id

        

            UNION ALL

        

            SELECT e.employeeId, e.employeeName, e.manager.id

            FROM Employee e

            JOIN EmployeeHierarchy eh ON e.manager.id = eh.id

        )

        SELECT new Employee(

            eh.id,

            eh.name,

            eh.mgr

        )

        FROM EmployeeHierarchy eh

        """;



var employees = entityManager.createQuery(jpql, Employee.class)

        .setParameter("id", 1)

        .getResultList();

А также со Spring Data JPA:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    @Query(value = "{JPQL-запрос, представленный выше}")

    List<Employee> findAllEmployeesByManagerIdJPQL(@Param("id") Integer id);

}



List<Employee> employees = employeeRepository.findAllEmployeesByManagerIdJPQL(1);

Как можно заметить, синтаксис HQL и SQL запросов довольно сильно похож. Но есть некоторые различия:



* В отличие от стандартного SQL, в Hibernate нет необходимости использовать ключевое слово RECURSIVE

* В Hibernate имена атрибутов CTE задаются через псевдонимы в выражении SELECT. Другими словами, в заголовке CTE имена не указываются.



#SpringTips #Hiberante #CTE