Thanks for the reply. I'll try to avoid that, though I don't quite
understand for now.
On 11/3/2011 8:06 PM, Christopher Delahunt wrote:
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;
... ...
}
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
|
