[eclipselink-users] JOIN FETCH when Query.setFirstResult(startPosition)

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

Hello, everyone

I made a OneToMany relationship lazy loading by default, and use JOIN FETCH to eagerly fetch it when necessary:

    QUERY_SELECT_ALL_EAGER = "select p from ExamPaper p JOIN FETCH p.questions"

It works fine until startPosition is bigger than the total number of records in the table. Then it returns a list whose size is always equal to maxResult (passed to query.setMaxResults), even if maxResult is greater than the total number of records in the table. I would expect it just returned a zero instead.

The code listing (note the three fails in the following test):

-------------  TEST --------------------------

  @Test public void testRetrieveExamPaperListEager() {
    //given
    dao.clearExamPapers();
    assertEquals(0, dao.retrieveExamPaperList().size());
    String testName = getTestMethodName();
    createExamPaper(testName + "0", 5); //adding four records
    createExamPaper(testName + "1", 5);
    createExamPaper(testName + "2", 5);
    createExamPaper(testName + "3", 5);
    //when
    List<ExamPaper> list1 = dao.retrieveExamPaperList();
    List<ExamPaper> list2 = dao.retrieveExamPaperList(0, 3, true);  //eager = true
    List<ExamPaper> list3 = dao.retrieveExamPaperList(3, 3, true);
    List<ExamPaper> list4 = dao.retrieveExamPaperList(0, 10, true);
    List<ExamPaper> list5 = dao.retrieveExamPaperList(1, 2, true);
    List<ExamPaper> list6 = dao.retrieveExamPaperList(4, 6, true);
    List<ExamPaper> list7 = dao.retrieveExamPaperList(3, 1, true);
    //then
    assertEquals(4, list1.size());
    assertEquals(3, list2.size());
    assertEquals(1, list3.size());  //fails: actual = 3
    assertEquals(4, list4.size());  //fails: actual = 10
    assertEquals(2, list5.size());
    assertEquals(0, list6.size());  //fails: actual = 6
    assertEquals(1, list7.size());
  }


----------------------- DAO ---------------------
  public List<ExamPaper> retrieveExamPaperList() {
    return retrieveExamPaperList(0, Integer.MAX_VALUE);
  }
 
  public List<ExamPaper> retrieveExamPaperList(int startPosition, int maxResult) {
    return retrieveExamPaperList(startPosition, maxResult, false);
  }
 
  public List<ExamPaper> retrieveExamPaperList(int startPosition, int maxResult, boolean eager) {
    List<ExamPaper> result = null;
    EntityManager em = createEntityManager();
    try {
      String queryString = eager ? ExamPaper.QUERY_SELECT_ALL_EAGER : ExamPaper.QUERY_SELECT_ALL;
      TypedQuery<ExamPaper> query = em.createQuery(queryString, ExamPaper.class);
      query.setFirstResult(startPosition);
      query.setMaxResults(maxResult);
      result = query.getResultList();
    } finally {
      em.close();
    }
    log.trace("ExamPapers retrieved: [startPosition={}, maxResult={}, count={}]", new Object[]{startPosition, maxResult, result.size()});
    return result;
  }
----------------  ENTITY ----------------------------

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

  ... ...
}

--
Regards,
Warren Tang