The JPA EntityManager createNativeQuery is a Magic Wand
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!
I found this very interesting question on the Hibernate forum, and, in this post, I want to demonstrate to you why native SQL queries are awesome.
Considering we have the following entities:
AnimalStable entity is represented by a database join table that links both the
Animal and the
Stable entities. The
registered_on property tells us when the
Animal was registered with a given
And, we have 3
animal table rows:
| id | name | |----|-------| | 1 | Linda | | 2 | Berta | | 3 | Siggi |
As well as 2
| id | title | |----|----------| | 1 | Stable 1 | | 2 | Stable 2 |
And the following 6
| id | registered_on | animal_id | stable_id | |----|---------------|-----------|-----------| | 1 | 2017-01-10 | 1 | 1 | | 2 | 2017-01-11 | 1 | 2 | | 3 | 2017-01-11 | 2 | 1 | | 4 | 2017-01-12 | 1 | 1 | | 5 | 2017-01-13 | 1 | 2 | | 6 | 2017-01-14 | 3 | 1 |
We want to know how many
animal(s) are in a particular
stable on a certain date. For this, we need to take into consideration the previous registrations and make sure that we are accounting for the latest
stable change of every particular
So, if we run the query for
2017-01-12 and for the first
stable, the result set should contain two entries:
Native SQL to the rescue!
Now, the question is looking for a solution with JPQL or HQL. However, entity queries are meant to select entities using a simple syntax, and so we lack support for Window Functions or Derived Tables.
But Hibernate and JPA have long been offering support for native SQL queries, and there is so much you can do if you don’t limit yourself to running entity queries only.
With Window functions
Using Window functions, we can solve this problem using the following query:
List<Animal> animals = entityManager.createNativeQuery(""" select distinct a.id, a.name from ( select animal_id, last_value(stable_id) over ( partition by a_s.animal_id order by a_s.registered_on range between unbounded preceding and unbounded following ) as last_stable_id from animal_stable a_s where a_s.registered_on <= :date ) a_s1 join animal a on a.id = a_s1.animal_id where a_s1.last_stable_id = :stable """, Animal.class) .setParameter("stable", stable1.id) .setParameter("date", Date.from( LocalDate.of(2017, 1, 12).atStartOfDay() .toInstant(ZoneOffset.UTC)), TemporalType.DATE) .getResultList();
What’s nice about this query is that we need to traverse the
animal_stable table only once since the window function allows us to fetch the last
stable_id of each particular
Without Window functions
Assuming you are using a database that does not support Window Functions, then you can run the following query:
List<Animal> animals = entityManager.createNativeQuery(""" select a.id, a.name from animal_stable a_s1 join ( select animal_id, max(registered_on) max_registered_on from animal_stable a_s where a_s.registered_on <= :date group by animal_id ) a_s2 on a_s1.animal_id = a_s2.animal_id and a_s1.registered_on = a_s2.max_registered_on join animal a on a.id = a_s1.animal_id where a_s1.stable_id = :stable order by a_s1.animal_id """, Animal.class) .setParameter("stable", stable1.id) .setParameter("date", Date.from( LocalDate.of(2017, 1, 12).atStartOfDay() .toInstant(ZoneOffset.UTC)), TemporalType.DATE) .getResultList();
Unlike the previous query, this one requires an additional join with a secondary query that determines the maximum registration for each particular
If you enjoyed this article, I bet you are going to love my upcoming 4-day Online Workshop!
- High-Performance Java Persistence Online Workshop (4 hours x 4 days) between the 29th of November and 22nd of December
Native queries are just awesome. You can take advantage of any feature your underlying database has to offer. Both the aforementioned queries return entities, so native queries are rather flexible too. Most often, you’d probably use a DTO projection since it performs better than fetching whole entities.
For this purpose,
EntityManager.createNativeQuery is a magic wand, and you should work your magic with it.