Sorry if this had been brought up before.. I did some Google Searches and just didn't find anything (maybe I didn't use the right search terms). Regardless, I had the following issue this morning. When I was trying to do Pagination on an Oracle database the following was my code:
Code:
criteria = session.createCriteria(object.getClass());
criteria.setFirstResult(start);
criteria.setMaxResults(maxResults);
returnList = criteria.list();
This produces (shortened) SQL similar to the following (INVOICE_PUBS is obviously my table):
Code:
select * from ( select row_.*, rownum rownum_ from ( select * from INVOICE_PUBS this_ ) row_ ) where rownum_ <= 35 and rownum_ > 30
This worked on one database and not on another. I have no idea why it worked on the one database because it shouldn't have. You MUST put an Order clause in the criteria for the pagination to work properly:
Code:
criteria.addOrder(Order.asc("theId"));
This will correctly produce the following SQL with the Order By clause
Code:
select * from ( select row_.*, rownum rownum_ from ( select * from INVOICE_PUBS this_ order by this_.INVC_PUBS_ID asc ) row_ where rownum <= 35) where rownum_ > 30
My recommendation would be that if there isn't an "Order By" clause already in the criteria that Hibernate use the "id" (it should know what the Id is I'd think).
Anyway, this tied up my morning and with the help of my DBA was able to resolve the issue.
Thomas
Login
FAQ
Search