JPA Query Approach For NESTED bags

Question

For multibags, I've followed and implemented Vlad Mihalcea's great post The best way to fix the Hibernate MultipleBagFetchException.

public class Car {
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
    private List<CarWheel> wheels = new ArrayList<>();

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
    private List<CarDoor> doors = new ArrayList<>();
}

The query to get a list of cars and their doors and windows without MultiBagException would be

List<Car> cars = em.createQuery("select distinct c from Car c
                 left join fetch c.windows", Car.class)
                 .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                 .getResultList();

          cars = em.createQuery("select distinct c from Car c
                 left join fetch c.doors where c in :cars", Car.class)
                 .setParameter("cars", cars)
                 .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                 .getResultList();
 return cars;

But how do I adapt that approach when it's a single bag and that bag has a bag of its own. How do get a list of cars and its wheels and its wheel's lug nuts - in a single query?

public class CarWheel {
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "wheel")
    private List<LugNuts> lugs = new ArrayList<>();
}

My work around has been to loop over the array of cars and then query for each car's wheel's lugs. But that is not the best way, I surmise, as making multiple db calls in a loop cannot be efficient. If I don't explicitly query for the lugs, however, I get Lazy Load exception. This is a simplified made up example by the way.

Answer 1

Here is the way. But your CarWheel entity has to provide either ManyToOne relationship to Car or contain carId as column.

List<Car> cars = em.createQuery("select distinct c from Car c
                 left join fetch c.windows", Car.class)
                 .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                 .getResultList();

          cars = em.createQuery("select distinct c from Car c
                 left join fetch c.doors where c in :cars", Car.class)
                 .setParameter("cars", cars)
                 .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                 .getResultList();
          
          List<CarWheel> carWheels = em.createQuery("select distinct cw from CarWheel cl
                 left join fetch cw.lugs where cw.car.id in :carIds", CarWheel.class)
                 .setParameter("carIds", cars.stream().map(c -> c.getId()).toList())
                 .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                 .getResultList();
// CarWheel.lugs entities are in persistent context.
// So, car.getWheel()...getLugs() will not trigger another SELECT statement 
return cars;