Spring Data JPA - Join Fetch, Entity Graphs - to fetch nested levels of OneToMany related data . . .

One-to-many relationship in Databases is quite common. It is also quite cumbersome in terms of how many aspects that need to be considered for getting it correctly implemented. Just to list few aspects - related JPA annotations, relationship keys specified in annotations, fetch modes, fetch types, joins, query types, performance, N+1 data problem, cartesian product, DISTINCT to eliminate duplicates, indexes etc. With Spring Data, JPA and Hibernate as the default implementation provider there are few JPA annotations, Hibernate specific annotations, JPQL queries, Java collection types, all these will get added to the mix.

Environment: Java 21, Spring Boot 3.2.3, PostgreSQL 15.3, maven 3.9.6 on macOS Catalina 10.15.7

If you have JPA entities related through OneToMany relationships two multiple levels, then some special care is required to fetch data in a performant manner by avoiding the classic N+1 query issue, or even multiple queries. Eash query is a roundtrip to Database and it adds up its own baggage.

Let's take a simple example of Country has many States, State has many Cities. We want to represent this relationship in JPA Entities and query using Spring Data JPA Repositories.

The entities with relationships look something like:

public abstract class BaseEntity implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(nullable = false, updatable = false) @ToString.Exclude protected Long id; /** For optimistic locking */ @Version protected Long version; @CreationTimestamp @Column(nullable = false, updatable = false, columnDefinition = "TIMESTAMP WITH TIME ZONE") protected OffsetDateTime dateCreated = OffsetDateTime.now(); @UpdateTimestamp @Column(nullable = false, columnDefinition = "TIMESTAMP WITH TIME ZONE") protected OffsetDateTime lastUpdated = OffsetDateTime.now(); } @Entity public class Country extends BaseEntity { private String name; @OneToMany(mappedBy = "country", cascade = CascadeType.ALL, orphanRemoval = true) @Builder.Default @ToString.Exclude private Set<State> states = new LinkedHashSet<>(); } @Entity public class State extends BaseEntity { private String name; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "country_id") @ToString.Exclude private Country country; // owning side of the relationship @OneToMany(mappedBy = "state", cascade = CascadeType.ALL, orphanRemoval = true) @Builder.Default @ToString.Exclude private Set<City> cities = new LinkedHashSet<>(); } @Entity public class City extends BaseEntity { private String name; @ManyToOne @JoinColumn(name = "state_id") @ToString.Exclude private State state; }

And a repository interface like:

@Repository public interface CountryRepository extends JpaRepository<Country, Long> { Optional<State> findByName(String name); }

One way to fetch all related data in a single query is by writing JPQL query with JOIN FETCH. This involves making sure to use all @OneToMany annotated properties to use Set and not List, and not using FetchType.EAGER and FetchMode.JOIN., and by writing a JPQL query with @Query annotation as shown below. Make a note of both DISTINCT and JOIN FETCH. This will result into one query which fetches for a Country all States, for each State all Cities data. If it is huge set of records, your best bet is to use @EntityGraph recommended. Lets say that our data is not huge and we want to use JPQL. In this case, the repository method annotatted with JPQL Query looks like:

@Repository public interface CountryRepository extends JpaRepository { @Query(""" SELECT DISTINCT country from Country country JOIN FETCH country.states state JOIN FETCH state.cities city WHERE country.name = :name """) Optional<Country> findByName(String name); }

Gotcha

• If you use List instead of Set, you might bump into infamous HIBERNATE concept called bag and an exception like - MultipleBagFetchException: cannot simultaneously fetch multiple bags which will force you to read a whole lot of text in finding the information you need, digging through googled links and StackOverflow without much luck, and eventually breaking your head ;)

• There are also other ways to tackle this problem. Writing native query in the @Query annotation is another way. I wouldn't go that route as I don't want to get sucked by databases. I am sure if you take that route, you will have someone around you ready to argue in favor of Sub Selects, Stored Procedures etc. ;). My personal preference is to stay away from diving deep into database,  avoid abyss ;)

Sample Spring Boot Application - Country-State-City

Here is the link to a sample Spring Boot 3.2.3 GraphQL application which has both @Query JPQL way and @EntityGraph way of getting the single generated query that is performant in fetching all related data in one roundtrip.

References

• Spring data JPA - Fetch and Load Graphs