Re: [eclipselink-users] JOIN FETCH when Query.setFirstResult(startPositi

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

I'm using JPA2.0, EL2.3, Oracle 11g.

Below is the log for the select statements (an ExamPaper has many choice questions):

[EL Fine]: 2011-11-03 11:20:21.028--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT ID, NAME FROM EXAMPAPER
[EL Fine]: 2011-11-03 11:20:31.237--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID)) ORDER BY t1.ID ASC) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [3, 0]
[EL Fine]: 2011-11-03 11:56:38.289--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [6, 3]
[EL Fine]: 2011-11-03 11:56:38.302--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [10, 0]
[EL Fine]: 2011-11-03 11:56:38.309--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [3, 1]
[EL Fine]: 2011-11-03 11:56:47.137--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [10, 4]
[EL Fine]: 2011-11-03 11:56:47.144--ServerSession(1715671248)--Connection(2001133431)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, t1.NAME AS a2, t0.ID AS a3, t0.SERIAL AS a4, t0.TITLE AS a5 FROM CHOICEQUESTION t0, EXAMPAPER_CHOICEQUESTION t2, EXAMPAPER t1 WHERE ((t2.ExamPaper_ID = t1.ID) AND (t0.ID = t2.questions_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?
    bind => [4, 3]

Regards,
Warren Tang

On 11/3/2011 11:53 AM, Warren Tang wrote:

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