Joining on a Collection relationship such as a onetomany will not work with pagination. first and max results limit the rows returned and will give you incomplete data when joining makes it so there is more than one row for an entity. They should not be used together Best regards
Chris
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]
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;
... ...
}
|