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

Документация PostgreSQL описывает оконные функции следующим образом:

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

Однако оконные функции не позволяют сгруппировать строки в виде одной на выходе, как это делают неоконные агрегатные вызовы. В данном случае строки сохраняют свою уникальность.

Распространенным примером оконной функции является запрос, который выбирает всех сотрудников с указанием их зарплаты и включает среднюю зарплату по отделу. Вот образец такого запроса в диалекте SQL PostgreSQL.

SELECT firstName, 
       lastName, 
       department, 
       salary, 
       avg(salary) OVER (PARTITION BY department)
FROM Employee e

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

Как видно на следующем скриншоте, запрос вернул выбранные столбцы таблицы Employee, рассчитал среднюю зарплату по всем отделам и упорядочил сотрудников в каждом отделе по их FirstName.

Как пользователь Hibernate, теперь вы можете спросить себя, как применять оконные функции. Ответ зависит от версии Hibernate. Начиная с версии 6, вы можете использовать оконные функции в запросах JPQL (Java Persistence Query Language — платформенно-независимый объектно-ориентированный язык запросов). Более старые версии Hibernate поддерживают их только в нативных запросах. Но если вы знаете, как мапировать набор результатов нативного запроса, то никаких ограничений это не вызовет.

Оконные функции в Hibernate 6

Hibernate 6 привносит несколько проприетарных улучшений в JPQL. Одним из них является поддержка оконных функций. Это немного упрощает поддержку различных RDBMS (Relational Database Management System — системы управления реляционными базами данных) и маппинг результатов вашего запроса на DTO-проекцию (Data Transfer Object — объект передачи данных).

Синтаксис для использования оконной функции в JPQL очень похож на SQL. Вы должны добавить к вызову функции предложение OVER, чтобы определить оконную функцию. Внутри этой оконной функции вы можете:

• Использовать ключевое слово PARTITION BY для определения фрейма, к которому вы хотите применить функцию.

• Добавить предложение ORDER BY для упорядочивания элементов внутри фрейма.

• Добавить предложение ROWS, RANGE или GROUPS, чтобы определить, сколько строк, какой диапазон значений или сколько различных групп значений должно быть включено во фрейм. Не все RDMBS поддерживают режимы RANGE и GROUPS. Поэтому, пожалуйста, ознакомьтесь с документацией вашей базы данных для получения дополнительной информации. По умолчанию фрейм включает текущую и все предыдущие строки в пределах существующей партиции.

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

List<Object[]> result = em.createQuery("""
                                        SELECT firstName, 
                                               lastName, 
                                               department, 
                                               salary, 
                                               avg(salary) OVER (PARTITION BY department)
                                        FROM Employee e""", Object[].class)
                          .getResultList();

Теперь вы можете задаться вопросом, почему оператор JPQL идентичен ранее показанному оператору SQL. Причина проста. Вы определяете оператор JPQL на основе ваших классов сущностей и их атрибутов, а не на основе вашей модели таблицы. Затем Hibernate генерирует SQL-запрос на основе предоставленного JPQL-запроса и ваших определений отображения. В этом примере имена атрибутов класса сущностей Employee идентичны столбцам таблицы Employee. Таким образом, видимого различия между операторами нет.

Определив свой запрос как оператор JPQL, вы получаете преимущество в том, что Hibernate генерирует специфичный для базы данных SQL-оператор на основе предоставленного JPQL-оператора. Таким образом, если вам необходимо поддерживать несколько RDBMS, Hibernate справится с различиями в диалектах поддерживаемого SQL.

Маппинг результатов запроса

Как вы видели в предыдущем сниппете кода, мой запрос возвращал List<Object[]>. Эта структура данных не очень удобна в использовании, и лучше мапировать ее на List (список) DTO-проекций. В данном примере я мапирую каждую запись результата на объект EmployeeInfo.

public class EmployeeInfo {
     
    private String firstName;
 
    private String lastName;
 
    private String department;
 
    private Double salary;
 
    private Double avgSalary;
 
    public EmployeeInfo() {}
     
    public EmployeeInfo(String firstName, String lastName, String department, Double salary, Double avgSalary) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.department = department;
        this.salary = salary;
        this.avgSalary = avgSalary;
    }
 
    // getter and setter methods
 
}

У вас есть два основных способа сделать это при выполнении JPQL-запроса. Вы можете использовать либо конструктор выражений JPA, либо Hibernate's ResultTransformer.

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

Вероятно, вы уже знакомы с конструктором выражений JPA по другим JPQL-запросам. Они начинаются с ключевого слова new, за которым следует полное имя класса, и определяют вызов конструктора, который устанавливает значения всех атрибутов.

List<EmployeeInfo> emps = em.createQuery("""
                                            SELECT new com.thorben.janssen.EmployeeInfo(firstName, 
                                                                                        lastName, 
                                                                                        department, 
                                                                                        salary, 
                                                                                        avg(salary) OVER (PARTITION BY department))
                                            FROM Employee e""", EmployeeInfo.class)
                            .getResultList();

Для этого JPQL-запроса Hibernate выполняет следующий SQL-запрос.

12:26:08,677 DEBUG [org.hibernate.SQL] - select e1_0.firstName,e1_0.lastName,e1_0.department,e1_0.salary,avg(e1_0.salary) over(partition by e1_0.department order by e1_0.firstName) from Employee e1_0

Как видно из лога, вызов конструктора не является частью выполняемого SQL-запроса. SQL-оператор только выбирает все значения, необходимые для вызова конструктора, а Hibernate вызывает конструктор при обработке результата запроса.

Мапинг результатов запроса с помощью TupleTransformer (он же ResultTransformer)

С помощью TupleTransformer в Hibernate можно получить тот же результат, но он обеспечивает большую гибкость. Как я объяснял в своем руководстве по ResultTransformers, вы можете имплементировать интерфейс Hibernate TupleTransformer, либо использовать один из стандартных трансформеров Hibernate. Затем Hibernate вызывает этот трансформер при обработке каждой записи результата запроса.

В этом примере я использую AliasToBeanResultTransformer от Hibernate. Он вызывает конструктор без аргументов моего класса EmployeeInfo и пытается найти метод-сеттер для каждого алиаса (псевдонима), определенного в запросе.

List<EmployeeInfo> emps = session.createQuery("""
                                        SELECT firstName as firstName,
                                               lastName as lastName, 
                                               department as department,
                                               salary as salary,
                                               avg(salary) OVER (PARTITION BY department) as avgSalary
                                        FROM Employee e""", Object[].class)
                                .setTupleTransformer(new AliasToBeanResultTransformer<EmployeeInfo>(EmployeeInfo.class))
                                .getResultList();

Оконные функции в Hibernate 5

Как упоминалось ранее, имплементация JPQL в Hibernate 5 не поддерживает оконные функции. Для их использования необходимо определить и выполнить нативный SQL-запрос. Hibernate не выполняет парсинг этих запросов. Он только берет и исполняет предоставленный оператор. Это означает, что можно использовать все возможности, поддерживаемые вашей базой данных. Но вы также должны учитывать различия в поддерживаемых диалектах SQL, если работаете с разными RDBMS.

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

List<Object[]> result = em.createNativeQuery("""
                                        SELECT firstName, 
                                               lastName, 
                                               department, 
                                               salary, 
                                               avg(salary) OVER (PARTITION BY department)
                                        FROM Employee e""")
                          .getResultList();

Как объяснялось ранее, Hibernate не модифицирует предоставляемый нативный SQL-запрос. Он просто его исполняет и возвращает результат.

14:53:00,980 DEBUG [org.hibernate.SQL] - SELECT firstName,
       lastName,
       department,
       salary,
       avg(salary) OVER (PARTITION BY department) as avgSalary
FROM Employee e

Маппинг результатов запроса

Нативный запрос в предыдущем примере возвращает результат в виде List Object[]. Это не очень удобно в использовании, особенно если вы хотите вызвать с его помощью какие-либо другие методы. Но поскольку вы читаете мой блог, то, вероятно, уже знаете, что можно указать Hibernate мапировать результат в другую структуру данных.

Маппинг результатов запроса с помощью @SqlResultSetMapping

Я подробно рассказывал о @SqlResultSetMapping JPA в серии статей блога. Если вы еще не знакомы с ним, я рекомендую прочитать следующие статьи:

• @SqlResultSetMapping - Основы

• @SqlResultSetMapping - Маппинги результата конструктора

• @SqlResultSetMapping - Сложные маппинги

Вы можете использовать аннотацию @SqlResultSetMapping, чтобы указать, как Hibernate должен мапировать результат вашего запроса. Вы можете осуществить его маппинг на управляемые сущности, DTO, скалярные значения, а также комбинации этих трех параметров. В этом примере я хочу, чтобы Hibernate вызвал конструктор EmployeeInfo, который мы использовали ранее. Он принимает все значения атрибутов и возвращает полностью инициализированный объект EmployeeInfo.

@Entity
@SqlResultSetMapping(name = "EmpInfoMapping",
                     classes = @ConstructorResult(targetClass = EmployeeInfo.class,
                                                  columns = {@ColumnResult(name = "firstName"),
                                                             @ColumnResult(name = "lastName"),
                                                             @ColumnResult(name = "department"),
                                                             @ColumnResult(name = "salary"),
                                                             @ColumnResult(name = "avgSalary"),}))
public class Employee { ... }

Подобно конструктору выражения, который я показал вам для Hibernate 6, Hibernate применяет @SqlResultSetMapping при обработке результата запроса. Таким образом, маппинг не влияет на исполняемый оператор. Он вносит изменения только в то, как Hibernate обрабатывает результат запроса.

Маппинг результата запроса с помощью ResultTransformer

Вы также можете использовать проприетарный ResultTransformer Hibernate для определения маппинга результата запроса. Интерфейс ResultTransformer устарел в версии 5, но это не препятствует его использованию. Как поясняется в моем руководстве по ResultTransformer в Hibernate, команда Hibernate разделила этот интерфейс на два в версии 6, и вы можете легко осуществить миграцию своих имплементаций.

Но в этом случае вам даже не нужно имплментировать собственный трансформер. Трансформер Hibernate AliasToBeanResultTransformer легко мапирует результат запроса в ваш DTO-класс. Нужно только определить алиас для возвращаемого значения вашей оконной функции.

List<EmployeeInfo> emps = session.createNativeQuery("""
                                        SELECT firstName as "firstName",
                                               lastName as "lastName", 
                                               department as "department",
                                               salary as "salary",
                                               avg(salary) OVER (PARTITION BY department) as "avgSalary"
                                        FROM Employee e""")
                                .setResultTransformer(new AliasToBeanResultTransformer(EmployeeInfo.class))
                                .getResultList();

Затем Hibernate выполняет нативный запрос и вызывает AliasToBeanResultTransformer для каждой записи набора результатов.

Заключение

Оконные функции — это мощная фича SQL. Начиная с Hibernate 6, вы также можете использовать их в JPQL-запросах. Как видно из примеров кода, синтаксис JPQL очень похож на SQL. Поэтому, если вы уже знаете оконные функции SQL, у вас не возникнет проблем с их использованием в JPQL.

Если вы все еще используете Hibernate 5, то можете применять оконные функции в нативных операторах SQL. Hibernate исполняет эти операторы без их парсинга. Таким образом, вы можете использовать все, поддерживаемые вашей базой данных, возможности. Но Hibernate также больше не корректирует ваш запрос в соответствии с с диалектом SQL, специфичным для базы данных.

Независимо от версии Hibernate и типа выполняемого запроса, в ответ вы получите Object[] или List Object[]. Их можно мапировать с помощью проприетарного трансформера ResultTransformer от Hibernate, а можно использовать конструктор выражения JPA в JPQL-запросе или аннотацию @SqlResultSetSetMapping для нативного запроса.

Сегодня вечером пройдет бесплатный урок, посвященный метрикам и HealthChecks в Spring Boot приложении. На занятии рассмотрим практический пример мониторинга приложения со Spring Boot Actuator; какие данные можно собрать и как это сделать.

• Записаться на открытый урок