[FIXED] Build criteria API query to avoid MultipleBagFetchException

Issue

We have a Lesson entity, every Lesson has lists of students and guests which attend the lesson:

public class Lesson {
    @Id
    private Long id;
    // ...other properties
    @OneToMany(mappedBy = "lesson", cascade = CascadeType.ALL)
    private List<Student> students;
    @OneToMany(mappedBy = "lesson", , cascade = CascadeType.ALL)
    private List<Guest> guests;
    // ...constructors, getters and setters
}
// --------------------------------------------------------------------------------------------
public class Student {
    @Id
    private Long id;
    // ...
    @ManyToOne
    @JoinColumn(name = "lesson_id")
    private Lesson lesson;
    // ...
}
// -------------------------------------------------------------------------------------------
public class Guest {
    @Id
    private Long id;
    // ...
    @ManyToOne
    @JoinColumn(name = "lesson_id")
    private Lesson lesson;
    // ...
}

I want to get all lessons with students and guests fetched so I build the following queries with criteria API:

@Repository
public class LessonCriteriaRepositoryImpl implements LessonCriteriaRepository {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Lesson> findAll() {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<Lesson> criteriaQuery = builder.createQuery(Lesson.class);
        Root<Lesson> lesson = criteriaQuery.from(Lesson.class);
        lesson.fetch(Lesson_.students, JoinType.LEFT);
        lesson.fetch(Lesson_.guests, JoinType.LEFT);
        criteriaQuery.select(lesson).distinct(true);
        TypedQuery<Lesson> query = em.createQuery(criteriaQuery);
        return query.getResultList();
    }
}

and I get MultipleBagFetchException because I cannot fetch several collections at one time. According to Vlad Mihalcea (https://twitter.com/vlad_mihalcea) post (Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags) the proper way to beat MultipleBagFetchException is making two separate queries and fetch collections one after another.

But I cannot understand how to build such two queries fetching collections one after another using criteria API. (I need to use criteria API because I gave as an example here some very simplified code, in real app I have complex filters and I use many predicates to build a query).

Solution

Based on the recommendations of the article https://vladmihalcea.com/hibernate-multiplebagfetchexception/, we can build two queries like this:

@Repository
public class LessonCriteriaRepositoryImpl implements LessonCriteriaRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Lesson> findAll() {
        //build first query for fetching students
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Lesson> criteriaQuery = builder.createQuery(Lesson.class);
        Root<Lesson> lesson = criteriaQuery.from(Lesson.class);
        lesson.fetch("students", JoinType.LEFT);
        criteriaQuery.select(lesson).distinct(true);
        TypedQuery<Lesson> query1 = entityManager.createQuery(criteriaQuery);
        List<Lesson> lessons = query1.getResultList();

        //build second query for fetching guests
        builder = entityManager.getCriteriaBuilder();
        criteriaQuery = builder.createQuery(Lesson.class);
        lesson = criteriaQuery.from(Lesson.class);
        lesson.fetch("guests", JoinType.LEFT);
        criteriaQuery.select(lesson).distinct(true).where(lesson.in(lessons));
        TypedQuery<Lesson> query2 = entityManager.createQuery(criteriaQuery);
        return query2.getResultList();
    }
}

Answered By - saver