Hibernate and Oracle pagination

Hibernate and Oracle pagination gotcha If you're a Hibenate user and are looking to support paging of results (for a web table or whatever), you've probably stumbled across this pagination class on the Hibernate blog : http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#pagination . For your convenience, I'll post their helper class here.

public class Page {
private List results;
private int pageSize;
private int page;

public Page(Query query, int page, int pageSize) {

this.page = page;
this.pageSize = pageSize;
results = query.setFirstResult(page * pageSize)
.setMaxResults(pageSize+1)
.list();
}

public boolean isNextPage() {
return results.size() > pageSize;
}

public boolean isPreviousPage() {
return page > 0;
}

public List getList() {
return isNextPage() ?
results.subList(0, pageSize-1) :
results;
}
}


public Page getPosts(int page) {
return new Page(
session.createQuery("from Posts p order by p.date desc")
page,
40
);
}

I don't want to be accused of plagiarism, so if anyone has a problem let me know and I'll gladly remove the above code from my blog.

Moving on, the author states that the basic requirement is to be able to page through data without fetching all the rows. While many applications would like to display the total number of rows in a table displaying paged data, this helper Page class intentionally does not issue a separate count query.

The Page class above looks nice and elegant where the number of rows retrieved is one more than the page size (See the constructor of the Page class). The idea behind retrieving the extra row being that it enables us to determine if we're at the last page of the paged dataset by comparing the resultset size with the user specified page size. So consider a query that would return 19 rows. If the user specifies a page size of 10, then the max results is set to 11 and when the user is on the first page, 11 records are retrieved and the test results.size() > pageSize returns 'true' indicating that we're not yet on the last page. When the user goes to the second page, only 9 rows are returned and the test results.size() > pageSize returns 'false' indicating we're at the last page.

Simple enough, so whats the problem you ask?

Hibernate genarates an SQL that uses inline views and the Oracle pseudo column ROWNUM to limit the number of rows retrieved. The problem using this technique is that first n rows retrieved by Oracle are not necessarily the same as the first n rows of a n+1 fetch. As a result the same records may be displayed on the first and second page.

I have a "Lane" class that has a many-to-one association with a "Location" class and I've mapped this to my Oracle schema using Hibernate.

In the screenshot below, the left window shows the results of the query generated by hibernate when fetching the first 10 rows (page one). Notice the order of the name column (which is a unique column) values. In the middle window you see the results of execution with max results = 11 (ie page_size + 1). Notice that the 11th row is not the last record so the results of returning the first 10 records using setMaxResults(10) and setMaxResults(11) are different. The rightmost window shows the results of the query used to display the second page (using Oracle dialect). You'll see that lane with name "17" also appears on the second page.


I don't believe this is an Oracle bug since it does not guarantee the return of rows in the same order in the absence of a sort criteria on a unique column. In this case there was an order by on a non unique field (state) so Oracle happens to return the rows in a different order probably due to the fact that the number of rows to be returned in each case was different. Had there been a secondary order by clause on a unique key as a tie breaker, (like lane_id), the ordering is the same. So one could always add the primary key field or unique field to the HQL to resolve this. For example :

"from Posts p order by p.date desc, p.id asc"

There's another issue with Hibernates getLimitString() string generated with the Oracle9Dialect. I am using Oracle 9. In the screenshot below, the queries do not fetch the extra record in each page. The left window shows the results of the first page. Note that the first page has a lane with name "9". The middle pane shows the results of the third page using sql generated by the OracleDialect. The results look fine. The right most pane shows the results of the query generated by the Oracle9Dialect. This should be the dialect that I should be using since I'm on Oracle 9, however you'll notice that lane with name "9" appears on page 3 as well.


So be careful when you chose an Oracle dialect! I had created a Hibernate issue HHH-1381 in Jan 2006 but haven't seen any activity. But the fact that is hasn't been rejected gives me some hope. Ofcouse I wouldn't be surprised if that issue mysteriously gets rejected later today. Joking :)