We are using Hibernate to handle pagination of a data set.
Query q = session.createQuery(query);
q.setMaxResults(maxRecordsPerPage);
q.setFirstResult(((pageNum - 1) * maxRecordsPerPage));
The paging is working correctly, except that it isn't ordering the results correctly. We have been troubleshooting this issue for about a day and a half and have tracked it down to a possible problem with the SQL that Hibernate is generating.
We gave the SQL that was generated to our DBA to look at and he came back with the following taken from the
IBM DB2 manual.
Chapter 2 -> Expressions -> OLAP functions (Doc Page 210, PDF Page 234)
Quote:
The ROW_NUMBER (or ROWNUMBER) function computes the sequential
row number of the row within the window defined by the ordering, starting
with 1 for the first row. If the ORDER BY clause is not specified in the
window, the row numbers are assigned to the rows in arbitrary order, as
returned by the subselect (not according to any ORDER BY clause in the
select-statement).
Our DBA added the order by clause to the rownumber() over() function and it appeared to fix our problem.
(See the SQL below) Notice the second line....
rownumber() over()
changed to
rownumber() over(order by engagement0_.StartDate asc)
Any help on this issue would be greatly appreciated. Is there something that we are overlooking? Is this a DB2Dialect problem?
---------------------------------------------------------------------------
DATABASE
Version "DB2 v8.1.0.36", "s021023", "", and FixPak "0".
HIBERNATE DIALECT
net.sf.hibernate.dialect.DB2Dialect
OUR HQL
select e from com.matrix.bo.te.Engagement e where e.clientBill.client.id = 10
OUR METHOD THAT IS RETURNING THE LIST
Code:
public List findPageList(final String query, final int pageNum, final int maxRecordsPerPage) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Query q = session.createQuery(query);
q.setMaxResults(maxRecordsPerPage);
q.setFirstResult(((pageNum - 1) * maxRecordsPerPage));
return q.list();
}
});
}
HIBERNATE GENERATED SQL Code:
select * from
( select rownumber() over() as row_,
engagement0_.Engagement_ID as Engageme1_,
engagement0_.ClientBill_ID as ClientBi2_,
engagement0_.HiringMgr_ID as HiringMg3_,
engagement0_.Consultant_ID as Consulta4_,
engagement0_.ActEndDate as ActEndDate,
engagement0_.CandIncType as CandIncT6_,
engagement0_.CandType as CandType,
engagement0_.EndDate as EndDate,
engagement0_.EngageCode as EngageCode,
engagement0_.ExpensesAllowed as Expense10_,
engagement0_.EngagementName as Engagem11_,
engagement0_.StartDate as StartDate,
engagement0_.WebTimeSheets as WebTime13_,
engagement0_.ajcEngagementName as ajcEnga14_,
engagement0_.Engagement_ID as x0_0_,
engagement0_.EngagementName as x1_0_,
engagement0_.ajcEngagementName as x2_0_,
engagement0_.StartDate as x3_0_,
engagement0_.EndDate as x4_0_
from DB2ADMIN.Engagement engagement0_,
DB2ADMIN.ClientBill clientbill1_
where (clientbill1_.Client_ID=10
and engagement0_.ClientBill_ID=clientbill1_.ClientBill_ID)
order by engagement0_.StartDate asc ) as temp_
where row_ between 30+1 and 60
THE SAME SQL SLIGHTLY MODIFIED BY OUR DBANotice the second line....
rownumber() over() changed to
rownumber() over(order by engagement0_.StartDate asc) Code:
select * from
( select rownumber() over(order by engagement0_.StartDate asc) as row_,
engagement0_.Engagement_ID as Engageme1_,
engagement0_.ClientBill_ID as ClientBi2_,
engagement0_.HiringMgr_ID as HiringMg3_,
engagement0_.Consultant_ID as Consulta4_,
engagement0_.ActEndDate as ActEndDate,
engagement0_.CandIncType as CandIncT6_,
engagement0_.CandType as CandType,
engagement0_.EndDate as EndDate,
engagement0_.EngageCode as EngageCode,
engagement0_.ExpensesAllowed as Expense10_,
engagement0_.EngagementName as Engagem11_,
engagement0_.StartDate as StartDate,
engagement0_.WebTimeSheets as WebTime13_,
engagement0_.ajcEngagementName as ajcEnga14_,
engagement0_.Engagement_ID as x0_0_,
engagement0_.EngagementName as x1_0_,
engagement0_.ajcEngagementName as x2_0_,
engagement0_.StartDate as x3_0_,
engagement0_.EndDate as x4_0_
from DB2ADMIN.Engagement engagement0_,
DB2ADMIN.ClientBill clientbill1_
where (clientbill1_.Client_ID=10
and engagement0_.ClientBill_ID=clientbill1_.ClientBill_ID)
order by engagement0_.StartDate asc ) as temp_
where row_ between 30+1 and 60
[/url]
Login
FAQ
Search
