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;
}
@Repository
public interface CountryRepository extends JpaRepository<Country, Long> {
Optional<State> findByName(String name);
}
@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.