Using Window Functions with Hibernate 5 & 6
SQL is an incredibly powerful query language. It provides sheer endless possibilities to extract and transform information. One example of that is a window function. It enables you to perform operations on subsets of the table rows available in your query.
The PostgreSQL documentation explains window functions as:
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function.
However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities.
https://www.postgresql.org/docs/current/tutorial-window.html
A common example of a window function is a query that selects all employees with their salary and includes the average salary of each employee’s department. Here is an example of such a query in PostgreSQL’s SQL dialect.
SELECT firstName,
lastName,
department,
salary,
avg(salary) OVER (PARTITION BY department)
FROM Employee e
You define the window function by adding an OVER clause to the call of an aggregating function. Within that clause, you can then specify the partition on which you want to apply the function, define the ordering within the partition, and limit your partition size. In this example, I keep it simple and only define a simple partition.
As you can see in the following screenshot, the query returned the selected columns of the Employee table, calculated the average salary of every department, and ordered the employees of each department by their firstName.
As a Hibernate user, you might now ask yourself how to use window functions. The answer depends on your Hibernate version. Starting with version 6, you can use window functions in your JPQL queries. Older Hibernate versions only support them via native queries. But if you know how to map a native query’s resultset, this doesn’t introduce any limitations.
Window functions in Hibernate 6
Hibernate 6 brings several proprietary improvements to JPQL. One of them is the support for window functions. That makes supporting different RDBMS and mapping your query results to a DTO projection a little easier.
The syntax for using a window function in JPQL is very similar to SQL. You have to add an OVER clause to your function call to define the window function. Within that window function, you can then:
- Use the PARTITION BY keyword to define the frame on which you want to apply the function.
- Add an ORDER BY clause to order the elements within the frame.
- Add a ROWS, RANGE, or GROUPS clause to define how many rows, which value range, or how many different value groups shall be included in the frame. Not all RDMBS support the modes RANGE and GROUPS. So, please check your database’s documentation for more information.
The default frame includes the current and all prior rows within the current partition.
Let’s use these features to implement the query I showed you in the introduction. As you can see in the code snippet, the JPQL statement looks identical to the SQL statement.
List<Object[]> result = em.createQuery("""
SELECT firstName,
lastName,
department,
salary,
avg(salary) OVER (PARTITION BY department)
FROM Employee e""", Object[].class)
.getResultList();
You might now wonder why the JPQL statement looks identical to the previously shown SQL statement. The reason is simple. You define the JPQL statement based on your entity classes and their attributes and not based on your table model. Hibernate then generates an SQL statement based on the provided JPQL statement and your mapping definitions. In this example, the attribute names of the Employee entity class are identical to the columns of the Employee table. So, there is no visible difference between both statements.
By defining your query as a JPQL statement, you get the benefit that Hibernate generates a database-specific SQL statement based on the provided JPQL statement. So, if you need to support multiple RDBMS, Hibernate handles the differences in the supported SQL dialects.
Mapping the query results
As you saw in the previous code snippet, my query returned a List<Object[]>. That data structure isn’t very comfortable to use, and you should better map it to a List of DTO projections. In this example, I will map each result record to an EmployeeInfo object.
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
}
You have 2 main options to do that when executing a JPQL query. You can either use JPA’s constructor expressions or Hibernate’s ResultTransformer.
Mapping the query results using a constructor expression
You probably already know JPA’s constructor expressions from your other JPQL queries. It starts with the keyword new, followed by the fully qualified class name, and defines the call of a constructor that sets all attribute values.
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();
For this JPQL query, Hibernate executes the following SQL query.
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
As you can see in the log output, the constructor call isn’t part of the executed SQL statement. The SQL statement only selects all values required to call the constructor, and Hibernate calls the constructor when processing the query result.
Mapping the query results using a TupleTransformer (aka ResultTransformer)
Hibernate’s TupleTransformer gets you the same result but provides more flexibility. As I explained in my guide to ResultTransformers, you can either implement Hibernate’s TupleTransformer interface or use one of Hibernate’s standard transformers. Hibernate then calls that transformer when processing each record of the query result.
In this example, I’m using Hibernate’s AliasToBeanResultTransformer. It calls the no-arguments constructor of my EmployeeInfo class and tries to find a setter method for each alias defined in the query.
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();
Window functions in Hibernate 5
As mentioned earlier, Hibernate 5’s JPQL implementation doesn’t support window functions. If you want to use them, you have to define and execute a native SQL query. Hibernate doesn’t parse these queries. It only takes the provided statement and executes it. That means you can use all features supported by your database. But you must also handle the differences in the supported SQL dialects if you’re working with different RDBMS.
I use that approach in the following examples to execute the SQL statement I showed you in the introduction.
List<Object[]> result = em.createNativeQuery("""
SELECT firstName,
lastName,
department,
salary,
avg(salary) OVER (PARTITION BY department)
FROM Employee e""")
.getResultList();
As explained earlier, Hibernate doesn’t modify the provided native SQL statement. It simply executes it and returns the result.
14:53:00,980 DEBUG [org.hibernate.SQL] - SELECT firstName,
lastName,
department,
salary,
avg(salary) OVER (PARTITION BY department) as avgSalary
FROM Employee e
Mapping the query results
The native query in the previous example returns the result as a List of Object[]. That’s not very comfortable to use, especially if you want to call any other methods with it. But as a reader of my blog, you probably already know that you can tell Hibernate to map the result to a different data structure.
Mapping the query results using an @SqlResultSetMapping
I explained JPA’s @SqlResultSetMapping in great detail in a series of blog posts. If you’re not already familiar with it, I recommend reading the following articles:
- @SqlResultSetMapping – The basics
- @SqlResultSetMapping – Constructor result mappings
- @SqlResultSetMapping – Complex mappings
You can use the @SqlResultSetMapping annotation to specify how Hibernate shall map your query result. You can map it to managed entities, DTOs, scalar values, and combinations of these 3. In this example, I want Hibernate to call the EmployeeInfo‘s constructor that we used in the previous example. It accepts all attribute values and returns a fully initialized EmployeeInfo object.
@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 { ... }
Like the constructor expression I showed you for Hibernate 6, Hibernate applies the @SqlResultSetMapping when processing the query result. So, the mapping doesn’t affect the executed statement. It only changes how Hibernate handles the query result.
Mapping the query result using a ResultTransformer
You can also use Hibernate’s proprietary ResultTransformer to define the mapping of the query result. The ResultTransformer interface is deprecated in version 5, but that shouldn’t prevent you from using them. As I explain in my guide to Hibernate’s ResultTransformer, the Hibernate team split that interface into 2 interfaces in version 6, and you can easily migrate your implementations.
But in this case, you don’t even need to implement a custom transformer. Hibernate’s AliasToBeanResultTransformer easily maps your query result to your DTO class. You only need to define an alias for the return value of your window function.
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 then executes the native query and calls the AliasToBeanResultTransformer for every record of the result set.
Conclusion
Window functions are a powerful SQL feature. Starting with Hibernate 6, you can also use them in your JPQL query. As you saw in the code examples, the JPQL syntax is very similar to SQL. So, if you already know SQL window functions, you will not encounter any issues using them with JPQL.
If you’re still using Hibernate 5, you can use window functions in native SQL statements. Hibernate executes these statements without parsing them. So, you can use all features supported by your database. But Hibernate also no longer adjusts your query based on the database-specific SQL dialect.
Independent of the Hibernate version and the kind of query you’re executing, your query will return an Object[] or a List of Object[]s. You can map these using Hibernate’s proprietary ResultTransformer, or you can use JPA’s constructor expression in your JPQL query or an @SqlResultSetMapping annotation for your native query.