The 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 Stable.
And, we have 3 animal table rows:
As well as 2 stable rows:
And the following 6 animal_stable entries:
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 the latest stable change of every particular animal.
So, if we run the query for 2017-01-12 and for the first stable, the result set should contain two entries: Linda and Berta.
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)
LocalDate.of(2017, 1, 12).atStartOfDay()
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 animal entry.
Without Window functions
Assuming you are using a database that does not support Window Functions, then you can run the following query:
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.