Hibernate fetching associations of bidirectional relationship having a hierarchical structure

Question

Let's say I have 3 models as follows:

@Entity
@Data
class Continent {
    @Id
    private Long id;

    private String name;

    ...
}

@Entity
@Data    
class Country {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Continent continent;

    private String name;

    ...
}

@Entity
@Data
class City {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Country country;

    private String name;

    ...
}

I want to fetch Continent by it's Primary key id. But I also want all the Countries that belong to a Continent and all the cities that belong to a Country.

Currently I am making 3 separate calls, first query Continent table, then query Country table by FK Continent id and at last querying City table by FK Country id.

I want to query everything in a single call. So I add bidirectional OneToMany relationship with EAGER fetching as follows:

@Entity
@Data
class Continent {
    @Id
    private Long id;

    private String name;

    ...
    
    @OneToMany(mappedBy = "continent", fetch = FetchType.EAGER)
    private List<Country> countries;
}

@Entity
@Data    
class Country {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Continent continent;

    private String name;

    ...
    
    @OneToMany(mappedBy = "country", fetch = FetchType.EAGER)
    private List<City> cities;
}

@Entity
@Data
class City {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Country country;

    private String name;

    ...
}

Everything works fine. I see two left outer joins in Hibernate generated query.

Now the issue is I do not want to eagerly fetch all the related associations everytime. One option is to change all OneToMany associations to LAZY and have join fetch in JPQL query.

So for fetching all the Countries of a Continent, I can have a JPQL query as below

SELECT u FROM Continent u JOIN FETCH u.countries WHERE u.id=?1

But for a hierarchy like Continent -> Country -> City, where I also want to populate the City fields, I am unable to design a JPQL query. Any ideas how I proceed further?

Edit: Future readers might be interested in reading JPA : How to define @NamedEntityGraph for 3 levels?

maybe try it like this : select u From SELECT u FROM Continent u JOIN FETCH u.countries c JOIN FETCH c.cities WHERE u.id=?1 wouldn't this retrieve the data the way you want it ? — Essameldeen Youssef, Nov 21 '20 at 20:53
No it does not work. I have already tried that. It gives: `Validation failed for query for method` — JavaLearner, Nov 23 '20 at 11:01
sorry my bad i misstyped something in the query its supposed to be like so : SELECT u FROM Continent u JOIN FETCH u.countries c JOIN FETCH c.cities WHERE u.id=?1 basically the idea is you use join fetch to get the nested entities you need could you try this and let me know ? — Essameldeen Youssef, Nov 23 '20 at 12:20

score 1 · Answer 1 · answered Nov 23 '20 at 11:43

You could use e.g. @EntityGraph, but I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Continent.class)
public interface ContinentDto {
    @IdMapping
    Long getId();
    String getName();
    Set<CountryDto> getCountries();

    @EntityView(Country.class)
    interface CountryDto {
        @IdMapping
        Long getId();
        String getName();
        Set<CityDto> getCities();
    }
    @EntityView(City.class)
    interface CityDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ContinentDto a = entityViewManager.find(entityManager, ContinentDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

ContinentDto findById(Long id);

I suppose, this answer validates that there is no easy way to solve this with plain JPA. I tried with NamedEntityGraph and subgraphs as mentioned in https://www.baeldung.com/jpa-entity-graph#defining-an-entity-graph but it resulted in MultipleBagFetchException — JavaLearner, Nov 27 '20 at 09:17

score 0 · Answer 2 · answered Nov 27 '20 at 09:15

I came up with the following approach.

First I marked all the associations as LAZY:

Continent model

@OneToMany(fetch = FetchType.LAZY)
private List<Country> countries = new ArrayList<>();

Country model

@OneToMany(fetch = FetchType.LAZY)
private List<City> cities = new ArrayList<>();

Next, I created Repositories and added JPQL queries to join fetch the required associations

public interface ContinentRepository extends JpaRepository<Continent, Long>
{
    @Transactional(readOnly = true)
    @Query(value = "SELECT u FROM Continent u JOIN FETCH u.countries WHERE u.id=?1")
    public Optional<Continent> findContinent(Long id);    
}

public interface CountryRepository extends JpaRepository<Country, Long>
{
    @Transactional(readOnly = true)
    @Query(value = "SELECT u FROM Country u JOIN FETCH u.cities WHERE u.continent.id=?1")
    public List<Continent> findCountry(Long continentId);    
}

Next in my Service class, when I want to load the Continent with all the Countries and Cities field initialized, I perform calls as follows:

@Service
public class ContinentService
{
    @Transactional(readOnly = true)
    public Optional<Continent> getContinent(Long continentId)
    {
        countryRepository.findCountry(continentId);
        return continentRepository.findContinent(continentId);
    }
}

Basically this first loads all the countries filtered by continentId with cities initialized in the first call and then loads the continent with all the countries in the second call. So all the associations are loaded in the current persistence context.

Hibernate fetching associations of bidirectional relationship having a hierarchical structure

2 Answers2