[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] JOIN FETCH when Query.setFirstResult(startPosition) is big
|
Actually join fetching will work with pagination, but you need to understand
that your are paging the relationship rows, not just the root rows.
EclipseLink will automatically handle the incomplete data in the first and
last rows by fetching them separately.
Because you are join fetching questions, you total rows is the number of
questions, not the number of papers.
You should also consider using batch fetch with IN instead of join fetch.
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
>
>
> On 2011-11-03, at 12:01 AM, Warren Tang <warren.c.tang@xxxxxxxxx> wrote:
>
>> 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
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog: http://java-persistence-performance.blogspot.com/ Java Persistence
Performance
--
View this message in context: http://old.nabble.com/JOIN-FETCH-when-Query.setFirstResult%28startPosition%29-is-big-tp32770466p32811586.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
