How do I implement pagination in Hibernate efficiently?

[recoil]

Hi,
What is the best way to implement pagination of query results using Hibernate?

I tried using the proxy parameter in the hibernate xml class definition files but proxies only work with load and invoke queries and getHibernateTemplate does not implement the invoke commands (only find), so what do I do? Can I use the same session as getHibernateTemplate and call the invoke command directly?



Thanks in advance.

[smccrory]

Most examples I've seen use Hibernate's Query or Criteria APIs for pagination.

[Martin Kersten]

Check the hibernate forum. Pagination is a hot topic over there. Another idea is to get the book: 'Hibernate in Action'. Great book!

[irbouho]

I found this blog entry (http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#pagination) very informative. With some coding (take a look at HibernateTemplate), I am sure you could very easily implement Spring / Hibernate pagination helper methods

[Martin Kersten]

Using criteria the code looks like this:

Criteria criteria=session.createCriteria(Item.class);
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(100);
criteria.setMaxResults(50);
List pageResults=criteria.list();

Basically you just need to establish an order of the query results. (remember to ensure a distinct result set) and set the index of the first result and the number of maximum results. In the above sample you will receive the third page (each consisting of 50 items) sorted by names.

Use the HibernateCallback interface for instance and use the HibnerateTemplate.execute(callback) method.

[ryan.tyer]

I found this blog entry (http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#pagination) very informative.

Perhaps a couple methods for pagination should be added to HibernateTemplate along with a Page helper class (as per the blog entry). It could probably be reworked to return Lists instead of the Page object, but I do like the additional functionality you could provide from such a Page. The addition to the api would be somthing along the lines of:

Page getPage(Query query, int pageSize, int pageNum) //this could also return a List
List getAllPages(Query query, int pageSize) //returns a List of all the pages

Thoughts?
Ryan

[recoil]

Hi,
I found the Criteria class along with the HibernateCallback and Page wrapper (from blog entry) adequate for my needs.
Thanks for the help!

Should the Page wrapper class have a function
int getNumPages(Query query, int pageSize)?

[ryan.tyer]

Correct me if I'm wrong, but if you have the following code, wouldn't you lose the transactionality applied via Interceptor since the query is actually executed outside of the target class?


Page findStuffAsPage(int pageSize, int pageNum){
return new Page(getHibernateTemplate().createQuery(getSession (), "from Cat c"), pageSize, pageNum);
}

Page Class from http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#pagination
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;
}

}



R

[Martin Kersten]

Correct me if I'm wrong, but if you have the following code, wouldn't you lose the transactionality applied via Interceptor since the query is actually executed outside of the target class?

Depends on. As long as the transaction is spanning over the whole findStuffAsPage method, everything will be fine.
For example:

findStuffAsPage is advised and a declarative transaction is attached, the following fluff is likely to happen:

<Transaction.begin>
call createQuery
create new Page
return page
<Transaction.end>

The question remains, when the query is executed. As long as the query is executed during the creation of the Page object (!Bad style!), it will work fine. So I would prefer a factory method on that anyway.

But I do not get what the really enhancement is the Page class will provide. You know setting first result and result count isn't a big deal anyway. Maybe adding an over all count + incooperate a table support would do it. But I don't know.... .

[irbouho]

Just a small addition :)
Page class implementation looks quiet simple, it should work fine as long as the whole call to findStuffAsPage is spanned in a Transaction. But you still need to pay attention to lazy intialization.

[snpe]

I think that ScrollableResults is better for paging .
ScrollableResults in hibernate 2.1.6 is good

regards

[Martin Kersten]

I think that ScrollableResults is better for paging .
ScrollableResults in hibernate 2.1.6 is good

Scrollable results may have some drawbacks since the connection has to remain open. There is no single fetch of database datas. It's more like have an opened connection. So the scrolling and stuff has to happen inside a transaction anyways.

If you know the page to show, I wouldn't go for scrollable results then. But maybe I am missing an important information here.

[ryan.tyer]

I think that ScrollableResults is better for paging .
There are a couple reasons I use paging. The first is to make it easier for the user to view a large set of data. Ideally, this wouldn't happen often (you'd be able to limit what the user needs to see more effectively), but in practice much harder to accomplish. In this case, ScrollableResults would work, pending the open connection issue Martin raised. The second reason is when you want to limit the amount of data being retrieved and transfered of the network. In my case, I have an application where the user's will be accessing it via low bandwidth connections and it is preferrable for only records x through y be retrieved. Unless I'm mistaken, in the second case, I'm not going to be able to accomplish that with a ScrollableResult

But I do not get what the really enhancement is the Page class will provide. You know setting first result and result count isn't a big deal anyway. Maybe adding an over all count + incooperate a table support would do it. But I don't know.... .

Sure it's easy to implement, but why rewrite the code over and over if you're going to be doing paging often? Other benefits I see is reusing the logic to determine the current page's location (pages ahead/behind) and a bit better readability when dealing with all the pages of a query (list of pages instead of list of lists).

R

[snpe]

You can use ScrollableResults with or without open connection - if connection is closed You simple reopen again, like this :

String queryString = ...;
int rowStart = 0;
int maxRows = 100;
databaseList = new ArrayList(); // cannot make new list always,, example
Session session = null;
try {
session = SessionFactoryUtils.getSession(dao.getSessionFacto ry(),
true);
Query q = session.createQuery(queryString);
ScrollableResults scr = q.scroll();
scr.first();
scr.scroll(rowStart);
int i = 0;
while (maxRows > i++) {
databaseList.add(scr.get(0));
if (!scr.next())
break;
}
} catch (DataAccessResourceFailureException e) {
logMessage(e);
} catch (HibernateException e) {
logMessage(e);
} catch (IllegalStateException e) {
logMessage(e);
} finally {
SessionFactoryUtils.closeSessionIfNecessary(sessio n, dao
.getSessionFactory());
}
return databaseList;

ScrollableResult is simple result - do same thing, only return rows which you want

regards

[orusso]

Hi all!!
almost 2 years old thread, but it is exacly what i am need to impliment now :rolleyes: ...

Is there something new in Spring/Hibernate that can help? :o thks!!

[therockhopper]

Since this is a Spring forum why not use a Spring solution?

Look at RefreshablePagedListHolder

[deepakgupta_72]

Since this is a Spring forum why not use a Spring solution?

Look at RefreshablePagedListHolder


can you put some more light on RefreshablePagedListHolder or any resource where i can find more information on that.

cheers

[adam_jh]

Spring does offer RefreshablePagedListHolder
http://www.springframework.org/docs/api/org/springframework/beans/support/RefreshablePagedListHolder.html
but as far as I can tell this is best for lots of data loaded at once. (see spring's countries example).

This thread is more useful in showing how to page between large (in terms of memory) result sets without loading them all at once.

I think using hibernate in the manner shown here is the only solution for this, since spring's hibernatesupport.setMaxResults etc are for the whole hibernatesupport class and not specifically for individual queries.

[s.kajla]

I have tried to access using this code. this code will give the good performence in the starting pages

public List findByQueryLimit(String whereClause, int startLimit, int noOfRecords) {
try {
String queryString = "from ZtsTicket where "+whereClause;
HibernateTemplate template=getHibernateTemplate();
int setMaxRecords = startLimit + noOfRecords + 1;
logger.info("Set Max Result "+ setMaxRecords);
template.setMaxResults(setMaxRecords);
List result = template.find(queryString);
List finalResult = new ArrayList();
int count = result.size()-startLimit;
if( count < noOfRecords) {
noOfRecords = count;
}
int i=0;
logger.info("Start Count "+startLimit + ".Records Accessed "+noOfRecords);
while (noOfRecords > i) {
logger.info("Start Count "+startLimit+i+",Rdcord "+result.get(startLimit+i));
finalResult.add(result.get(startLimit+i));
i++;
}
return finalResult;
} catch (RuntimeException re) {
log.error("find all failed", re);
throw re;
}
}