5

Hibernate 6 and JPQL Window Functions

 2 years ago
source link: https://vladmihalcea.com/hibernate-jpql-window-functions/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Last modified:

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 and account_transaction 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    |

If you want to learn more about Window Functions, then you should also register for my next SQL online workshop as I’m going to discuss this topic in great detail.

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 4th of May about SQL Window Functions.

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK