[eclipselink-users] Lazy loading performance problem

[Warren Tang <warren.c.tang@xxxxxxxxx>]

Hi, everybody

I have an ExamPaper entity which has many ChoiceQuestions. The questions are lazy loaded by default (fetch=LAZY).

Now I want to get all the quesitions that belong to a specific ExamPaper, so I use the following JPQL:

  "select p.questions from ExamPaper p where p.id=:paperId";

The problem is that questions are "lazy" loaded, so there is one "SELECT" executed for *every single* question. But I want to select them all in one go for performance reasons. What should I do?

------------------ Code listing -----------------------------------

@Entity
public class ExamPaper {
  public static final String SELECT_QUESTION_BY_PAPER = "select p.questions from ExamPaper p where p.id=:paperId";
 
  @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String name;
  @OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = {CascadeType.ALL})
  List<ChoiceQuestion> questions;

  ... ...
}

      query = em.createQuery(ExamPaper.SELECT_QUESTION_BY_PAPER, ChoiceQuestion.class);
      query.setParameter("paperId", examPaperId);
      query.setFirstResult(startPosition);
      query.setMaxResults(maxResult);
      list = query.getResultList();

--
Regards,
Warren Tang