Can't fetch join lazy initialized collections

Question

I am unable to perform a simple fetchjoin because of MultipleBagFetchException.

@Entity
public class Person {

    @OneToMany(mappedBy="person",fetch=FetchType.LAZY)
    private List<Auto> autos;    
}


@Entity
public class Auto {

    @ManyToOne
    @JoinColumn(name = "person_id", nullable = false)
    private Person person;

   @OneToMany(mappedBy="auto",fetch=FetchType.LAZY)
   private List<Tool> tools;

}

@Entity
@Table(name="tool")
public class Tool {

    @ManyToOne
    @JoinColumn(name = "auto_id", nullable = false)
    private Auto auto;
}

As you can see all of my associactions uses default fetchtype.

 @Query("SELECT p FROM Person p JOIN FETCH p.autos a JOIN FETCH a.tools")
 List<Person>findAll();

result:

Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.example.entities.Person.autos, com.example.entities.Auto.tools]

I have read about this exceptions, but in those cases the reason for this exception was the usage of EAGER fetch type for collections. What about this? This the most simple Entity relation.

And on the top of that lets suppose we are not allowed to touch the Entities. How to solve this only on the query side?

Answer 1

There is one way to avoid n+1 queries without touching the entities, only changing the query for findAll. We can write a wrapper function which will first load persons with autos and them fetch all tools in a single select.

PersonRepository

    @Query("SELECT distinct p FROM Person p JOIN FETCH p.autos a")
    List<Person> findAll();

Wrapper code

    List<Person> persons = personRepository.findAll();
    Session session = (Session) entityManager.getDelegate();
    List<Auto> autos = new ArrayList<>();
    for (Person person : persons) {
        if(!CollectionUtils.isEmpty(person.getAutos())) {
            autos.addAll(person.getAutos());
        }
    }
    try{
        autos = session.createQuery("select distinct a from Auto a Join fetch a.tools " +
                " where a in :autos", Auto.class)
                .setParameter("autos", autos)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

The first query will be:

SELECT DISTINCT
          person0_.id       AS id1_6_0_,
          autos1_.id        AS id1_0_1_,
          person0_.name     AS name2_6_0_,
          autos1_.name      AS name2_0_1_,
          autos1_.person_id AS person_i3_0_1_,
          autos1_.person_id AS person_i3_0_0__,
          autos1_.id        AS id1_0_0__
FROM
          Person person0_
          INNER JOIN
                    Auto autos1_
          ON
                    person0_.id=autos1_.person_id

The second query generated will be :

    SELECT
          auto0_.id        AS id1_0_0_,
          tools1_.id       AS id1_8_1_,
          auto0_.name      AS name2_0_0_,
          auto0_.person_id AS person_i3_0_0_,
          tools1_.auto_id  AS auto_id3_8_1_,
          tools1_.name     AS name2_8_1_,
          tools1_.auto_id  AS auto_id3_8_0__,
          tools1_.id       AS id1_8_0__
FROM
          Auto auto0_
          INNER JOIN
                    Tool tools1_
          ON
                    auto0_.id=tools1_.auto_id
WHERE
          auto0_.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Other than this I believe our options are limited, we will have to change Tool entity FetchMode or add BatchSize for default FetchMode.SELECT in order to get Tools in a separate query.

    @OneToMany(mappedBy = "auto", fetch = FetchType.LAZY)
    @Fetch(FetchMode.SUBSELECT)
    private List<Tool> tools;

The query will be

SELECT
          tools0_.auto_id AS auto_id3_8_1_
        , tools0_.id      AS id1_8_1_
        , tools0_.id      AS id1_8_0_
        , tools0_.auto_id AS auto_id3_8_0_
        , tools0_.name    AS name2_8_0_
FROM
          Tool tools0_
WHERE
          tools0_.auto_id IN
          (
                    SELECT
                              autos1_.id
                    FROM
                              Person person0_
                              INNER JOIN
                                        Auto autos1_
                              ON
                                        person0_.id=autos1_.person_id
          )