Hello,
I am using a criteria query with order by for pagination with a page size of 10.
For example, for a getResultList() of 20 rows the:
The first page displays 10 results (setFirstResult(0) , set MaxResults(10))
The second page displays the remaining 10 results (setFirstResult(10) , set MaxResults(10))
The problem is that the second page contains also some results from the first page.
After checking the generated SQL I noticed that the order by is missing from the outer query.
Query q=…
cq.orderBy(entityManager.getCriteriaBuilder().desc(…)
query.setFirstResult(paginate.getFirst()).setMaxResults(paginate.getRows());
result = query.getResultList();
First page generated SQL:
select
*
from
( select
usernotifi0_.USER_NOTIFICATION_CASE_ID as USER_NOTIFICATION_1_47_,
usernotifi0_.ARCHIVED as ARCHIVED2_47_,
usernotifi0_.NOTIFICATION_CASE_ID as NOTIFICATION_CASE_4_47_,
usernotifi0_.READ as READ3_47_,
usernotifi0_.USER_INFORMATION_ID as USER_INFORMATION_I5_47_
from
USER_NOTIFICATION_CASE usernotifi0_
inner join
NOTIFICATION_CASE notificati1_
on usernotifi0_.NOTIFICATION_CASE_ID=notificati1_.NOTIFICATION_CASE_ID
inner join
COUNTRY country3_
on notificati1_.MAIN_MEMBER_STATE=country3_.COUNTRY_KEY
where
usernotifi0_.USER_INFORMATION_ID=161
and usernotifi0_.ARCHIVED=0
and notificati1_.NOTIFICATION_STATUS<>‘TRASH’
order by
country3_.COUNTRY_KEY desc )
where
rownum <= 10
Second page generated SQL:
select *
from
( select
row_.*,
rownum rownum_
from
( select
usernotifi0_.USER_NOTIFICATION_CASE_ID as USER_NOTIFICATION_1_47_,
usernotifi0_.ARCHIVED as ARCHIVED2_47_,
usernotifi0_.NOTIFICATION_CASE_ID as NOTIFICATION_CASE_4_47_,
usernotifi0_.READ as READ3_47_,
usernotifi0_.USER_INFORMATION_ID as USER_INFORMATION_I5_47_
from
USER_NOTIFICATION_CASE usernotifi0_
inner join
NOTIFICATION_CASE notificati1_
on usernotifi0_.NOTIFICATION_CASE_ID=notificati1_.NOTIFICATION_CASE_ID
inner join
COUNTRY country3_
on notificati1_.MAIN_MEMBER_STATE=country3_.COUNTRY_KEY
where
usernotifi0_.USER_INFORMATION_ID=161
and usernotifi0_.ARCHIVED=0
and notificati1_.NOTIFICATION_STATUS<>‘TRASH’
order by
country3_.COUNTRY_KEY desc ) row_
where
rownum <= 20
)
where
rownum_ > 10