The JPA EntityManager createNativeQuery is a Magic Wand

Introduction

I found this very interesting question on the Hibernate forum, and, in this post, I want to demonstrate you why native SQL queries are awesome.

Domain Model

Considering we have the following entities:

animalstable

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:

id name
1 Linda
2 Berta
3 Siggi

As well as 2 stable rows:

id title
1 Stable 1
2 Stable 2

And the following 6 animal_stable entries:

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

The problem

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)
.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 animal entry.

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 animal.

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

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.

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

2 thoughts on “The JPA EntityManager createNativeQuery is a Magic Wand

    1. The test case is located on GitHub, and you can run it to see that without DISTINCT, the number of rows is given by the number of rows in animal_stable which satisfy the predicate (e.g. 4 rows). However, there are duplicated entries, as we only expect to have 2 rows in our ResultSet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s