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. Each 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);
}
JPQL
Java Persistencw Query Language (JPQL) is portable query language to query persistent entities irrespective of the mechanism used to store those entities. Typically in a Java application the entities are Java classes. Similar to SQL, it provides select, update, delete statements, join operations, aggregations, subqueries etc. Hibernate supports both JPQL and HQL.
Spring Data JPA offers different ways to define query methods in Repository interface like: 1) Derived queries for which the query is derived/generated from the name of the method by following conventions 2) Declared Queries by annotating query method with @Query annotation 3) Named Queries etc.
JPQL - Fetch Entities
In the above JPQL declared query in CountryRepository using @Query annotation, a typical JPQL to query entity object (Country) is shown. Typically, entity object is mapped to Database table and entity query results into SQL query generated by the underlying JPA implementation like Hibernate. The query result is Database records fetched from table(s) and the raw data is transformed into Entity objects.
JPQL - Fetch Custom objects
JPQL also supports custom objects through JPQL Constructor expressions. A constructor expression can be specified in JPQL to return a custom object instead of Entity object. Below is an example a code snippet in which a light-weight custom Java record is returned instead of Entity object.
@Repository
public interface StateRepository extends JpaRepository<State, Long> {
/**
* JPQL - Query to fetch specific fields of Entity and return a non-entity custom objects
*
* @param population the population
* @return list of light-weight StatePopulation objects
*/
@Query("""
SELECT new com.giri.countrystatecity.domain.StatePopulation(state.name, state.population) from State state
WHERE state.population > :population
""")
List<StatePopulation> findAllStatesByPopulationGreaterThan(Long population);
}
where StatePopulation is simply a record like: public record StatePopulation(String name, Long population) { }
JPQL - Fetch Raw specified column data
Similar to the custom object, raw column data can also be fetched and then required object csn be constructed from the returned data. Following is code snippet for fetching raw data and constructing a data record object.
/**
* JPQL - Query to fetch specific fields of Entity and return Raw data
*
* @param population the population
* @return list of light-weight StatePopulation objects
*/
@Query("""
SELECT state.name, state.population from State state
WHERE state.population > :population
""")
List<List<Object>> findAllStatesByPopulationGreaterThanJpqlRaw(Long population);
where raw data fetched can be converted into required objects in a service method like shown below:
public List<StatePopulation> getAllByPopulationGreaterThanJpqlRaw(Long population) {
List<List<Object>> states = stateRepository.findAllStatesByPopulationGreaterThanJpqlRaw(population);
return states.stream()
.map(record -> new StatePopulation((String) record.get(0), (Long)record.get(1)))
.toList();
}
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 N+1 query 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.
Posts
