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?

I'm running an online workshop on the 11th of October about High-Performance SQL.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW