Hibernate 6 and JPQL Window Functions
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to show you how you can use Hibernate 6 to write JPQL queries that use SQL Window Functions.
This feature has been added in Hibernate 6, which provides a new query parser called SQM (Semantic Query Model), which is more powerful than the previous Hibernate HQL query capabilities.
SQL Window Functions
As I explained in this article, Window Functions are extremely powerful since they allow us to apply aggregation functions on a given set of records without having to reduce the result set to a single row per partition, as it’s the case for the GROUP BY
clause.
For instance, let’s assume we have the following database tables:
The account
is the parent table, and the account_transaction
is the child table since it has an account_id
Foreign Key column referencing the id
Primary Key of the account
table.
The account
table has two records associated with Alice and Bob:
| id | iban | owner | |----|-----------------|-------------| | 1 | 123-456-789-010 | Alice Smith | | 2 | 123-456-789-101 | Bob Johnson |
And the account_transaction
contains transactions that belong to both Alice and Bob:
| id | amount | created_on | account_id | |----|--------|---------------------|------------| | 1 | 2560 | 2019-10-13 12:23:00 | 1 | | 2 | -200 | 2019-10-14 13:23:00 | 1 | | 3 | 500 | 2019-10-14 15:45:00 | 1 | | 4 | -1850 | 2019-10-15 10:15:00 | 1 | | 5 | 2560 | 2019-10-13 15:23:00 | 2 | | 6 | 300 | 2019-10-14 11:23:00 | 2 | | 7 | -500 | 2019-10-14 14:45:00 | 2 | | 8 | -150 | 2019-10-15 10:15:00 | 2 |
We are now interested in a statement report that contains the following columns:
- the entry number of each statement entry relative to each account
- the transaction identifier
- the account identifier
- the transaction timestamp
- the transaction amount
- the account balance at the moment the transaction happened
To get this report, we need to execute the following SQL query:
SELECT ROW_NUMBER() OVER( PARTITION BY account_id ORDER BY created_on, id ) AS nr, id, account_id, created_on, amount, SUM(amount) OVER( PARTITION BY account_id ORDER BY created_on, id ) AS balance FROM account_transaction ORDER BY id
This SQL query uses two Window Functions:
The ROW_NUMBER
function works as follows:
First, it will partition the query result set by the account_id
, therefore dividing the result sets into two groups as we have transactions belonging to just two individual accounts:
ROW_NUMBER() OVER( PARTITION BY account_id ORDER BY created_on, id ) AS nr,
Second, it will sort each partition chronologically:
ROW_NUMBER() OVER( PARTITION BY account_id ORDER BY created_on, id ) AS nr,
The reason we’re using both the created_on
and the id
columns when sorting is to prevent the case when two transactions are registered at the very same instant. By using the id
, which is also monotonically increasing, we make sure that the current frame spans from the very first partition record to the current processing row.
Once the records are partitioned and sorted, the ROW_NUMBER
assigns consecutive numbers to each record. Note that the numbering is reset when switching to a new partition.
The SUM
function uses the same partitioning and sorting logic:
SUM(amount) OVER( PARTITION BY account_id ORDER BY created_on, id ) AS nr,
As already explained, the default frame on which the Window Function is applied spans from the very first record in the current partition up to the current processing element. For this reason, the SUM
function is going to produce a running total.
So, the SQL query that produces the report we are interested in looks like this:
SELECT ROW_NUMBER() OVER( PARTITION BY account_id ORDER BY created_on, id ) AS nr, id, account_id, created_on, amount, SUM(amount) OVER( PARTITION BY account_id ORDER BY created_on, id ) AS balance FROM account_transaction ORDER BY id
And when executing this query, we get the following result:
| nr | id | account_id | created_on | amount | balance | |----|----|------------|----------------------------|--------|---------| | 1 | 1 | 1 | 2019-10-13 12:23:00.000000 | 2560 | 2560 | | 2 | 2 | 1 | 2019-10-14 13:23:00.000000 | -200 | 2360 | | 3 | 3 | 1 | 2019-10-14 15:45:00.000000 | 500 | 2860 | | 4 | 4 | 1 | 2019-10-15 10:15:00.000000 | -1850 | 1010 | | 1 | 5 | 2 | 2019-10-13 15:23:00.000000 | 2560 | 2560 | | 2 | 6 | 2 | 2019-10-14 11:23:00.000000 | 300 | 2860 | | 3 | 7 | 2 | 2019-10-14 14:45:00.000000 | -500 | 2360 | | 4 | 8 | 2 | 2019-10-15 10:15:00.000000 | -150 | 2210 |
Hibernate JPQL with Window Functions
Prior to Hibernate 6, the only way to use Windows Functions with entity queries was via Blaze Persistence. Since Hibernate 6 provides a new Semantic Query Model, the entity query language is much more powerful than it used to be.
For this reason, with Hibernate 6, you can now execute the following JPQL query:
List<StatementRecord> records = entityManager.createQuery(""" SELECT ROW_NUMBER() OVER( PARTITION BY at.account.id ORDER BY at.createdOn ) AS nr, at, SUM(at.amount) OVER( PARTITION BY at.account.id ORDER BY at.createdOn ) AS balance FROM AccountTransaction at ORDER BY at.id """, StatementRecord.class) .unwrap(Query.class) .setTupleTransformer((Object[] tuple, String[] aliases) -> new StatementRecord( longValue(tuple[0]), (AccountTransaction) tuple[1], longValue(tuple[2]) ) ) .getResultList(); assertEquals(8, records.size()); StatementRecord record1 = records.get(0); assertEquals( 1L, record1.nr().longValue() ); assertEquals( 1L, record1.transaction().getId().longValue() ); assertEquals( 1L, record1.transaction().getAccount().getId().longValue() ); assertEquals( 2560L, record1.balance().longValue() );
Because this is a projection query, we are using the new TupleTransformer
to return a list of StatementRecord
objects that have the following structure:
public record StatementRecord( Long nr, AccountTransaction transaction, Long balance ) {}
When running the above JPQL query, Hibernate 6 executes the following SQL query:
SELECT ROW_NUMBER() OVER( PARTITION BY a1_0.account_id ORDER BY a1_0.created_on ), a1_0.id, a1_0.account_id, a1_0.amount, a1_0.created_on, SUM(a1_0.amount) OVER( PARTITION BY a1_0.account_id ORDER BY a1_0.created_on ) FROM account_transaction a1_0 ORDER BY a1_0.id
Notice that unlike in Hibernate 5, 4, or 3, the generated SQL query doesn’t use additional aliases to the projection since the JDBC ResultSet
is read by column index, not by the alias names, which also provides better performance.
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Hibernate 6 provides many benefits, and having support for Window Functions allows us to write projections that combine both entities and aggregated data.
This new Hibernate version is quite revolutionary, and there are many more query features that will soon be implemented thanks to the new Semantic Query Model used by all JPQL and Criteria entity queries.
