Imagine a bacon-wrapped Ferrari. Still not better than our free technical reports.

Hibernate, JPA, and XRebel Continued: Understanding Eager, Lazy and Join Fetch queries

In the previous article we’ve uncovered a typical Hibernate issue – as Hibernate hides a lot of relational database complexity from us, it is easy to accidentally pull too much data through too many queries from the database. We used XRebel to identify that issue in Spring Petclinic, but haven’t figured out how to fix it yet:


We need to change the JPA query that is causing 24 SQL queries to be issued:

SELECT owner FROM Owner owner WHERE owner.lastName LIKE :lastName

We start by looking at the Owner class to understand what relationships are defined:

@Table(name = "owners")
public class Owner extends Person {
        cascade = CascadeType.ALL, 
        mappedBy = "owner", 
        fetch = FetchType.EAGER
    private Set<Pet> pets;

The key part of this definition is fetch = FetchType.EAGER. This instructs Hibernate to load all referred Pets, when an Owner entity is loaded from the database. This is as opposed to FetchType.LAZY, which instructs the opposite – referred Pets will only be loaded if and when accessed from the loaded Owner entity. To find out more check out this Stackoverflow thread.

Let’s try changing EAGER to LAZY and see what happens. JRebel helps us to change things on the fly and XRebel will tell us if we succeeded right away.


Here XRebel has conveniently caught and unwrapped the logged exception for us to see. The key message is:

Wrapped by org.apache.jasper.el.JspELException: /WEB-INF/jsp/owners/ownersList.jsp(33,12) ‘${owner.pets}’ Error reading ‘pets’ on type org.springframework.samples.petclinic.model.Owner

This means that we failed attempting to load Owner.getPets() on our JSP, because the Hibernate Session wasn’t open anymore. One solution to this problem is opening the Hibernate session at the beginning of request, but it is increasingly considered a bad practice. Indeed, it would not actually solve our problem, since the 10 queries to Pets would still be issued, just in this case from the JSP.

To solve this issue we need to use a little-known Hibernate feature called “fetch” join. This join causes the joined entities to be fetched in the same query, if possible. Modifying our query with a fetch join will look like this:

SELECT owner FROM Owner owner join fetch owner.pets WHERE owner.lastName LIKE :lastName

Running the query results in this:


We succeed from going from 24 queries to 20, but there is also an issue – we see Owner rows repeated, since we joined multiple pets per row. To fix that we just add a DISTINCT clause to the query:

SELECT DISTINCT owner FROM Owner owner join fetch owner.pets WHERE owner.lastName LIKE :lastName

However there are still 20 queries. Looking in the Pet entity we see this:

@Table(name = "pets")
public class Pet extends NamedEntity {

    @JoinColumn(name = "type_id")
    private PetType type;

        cascade = CascadeType.ALL, 
        mappedBy = "pet", 
        fetch = FetchType.EAGER
    private Set<Visit> visits;

Clearly we have more EAGER relationships to untangle. One additional caveat is that unlike @OneToMany relationships that default to LAZY, @ManyToOne relationships default to EAGER, so even though “type” doesn’t specify the fetch strategy, it is actually an EAGER one and needs to be set explicitly. By setting fetch strategy explicitly to LAZY on both relationships, we get this:

Just one query instead of 24. Great success!

Debugging problems like this is great with an interactive profiler like XRebel and a continuous development tool like JRebel. You get to identify the problem as you click through your application and test the fix right away.