pagination -> in hibernate or in JSF

golly97 · **Joined:** Wed Feb 15, 2006 2:51 pm **Posts:** 20

Hello,

I am in the middle of redesigning an enterprise app.
One of the problems we had in our last design is that we used datasets, so a lot of the data was cached there for using up a lot of resources + refreshing other datasets when inserting to another one… all this extra caching..

In the redesign I am using jsf for the presentation layer, spring and hibernate for o/r.

I am looking at 2 options

Either putting the pagination in the jsf , this would mean I would have to load and cache the data on any give on occasion. Good thing, I think is that I don’t hit the db so much.

Or use Hibernate to use pagination, but this would mean that I am going back and forth to the database to get new quires (I am not worried about db connections, we have unlimited + I will use pooling)

Can any body speak to which method would be better? In terms of resources, speed...any problems....etc

Thank you for you answers

kgignatyev · **Posted:** Thu Feb 16, 2006 2:51 pm

If H returned set is not that big and pagination is mostly for UI convenience sake, then I would do pagination on UI side ( JSF, Tapestry or something else). If the result sets are really huge than I would suggest cooperation between UI data model and H pagination abilities.

Rauf · **Joined:** Fri Dec 09, 2005 4:57 am **Posts:** 7

I am using JSF and Hibernate combo. Although my database has very few records at present, but it is expected to grow. That is why I prefer the H pagination using setFirstResult and setMaxResults over the pagination in JSF.

In order to construct numbered pages on the UI side, I need to know the total number of records returned by a query. One way to do that is by putting the session result in a List by using the following:

Code:

List list = session.createQuery(query).list();

and then do a count on the list. However, this defeats the purpose as the list is stored in the memory.

Is there a way to find out number of records in a query without executing it through the session? I know it is an oxymoronic questions, I thought I would ask it anyway :-)

Thanks

Rauf

Rauf · **Joined:** Fri Dec 09, 2005 4:57 am **Posts:** 7

Nevermind, I found some ideas on pagination in the following post:

http://forum.hibernate.org/viewtopic.php?t=951369

Rauf

golly97 · **Joined:** Wed Feb 15, 2006 2:51 pm **Posts:** 20

Rauf wrote:

Nevermind, I found some ideas on pagination in the following post:

http://forum.hibernate.org/viewtopic.php?t=951369

Rauf

Thanks for the post,
One of my questions was, what's the best way of getting results count with out loading all the records? This is perfect!!

I think what i will do is create a class where if max records is more then 500 then I will handle it at the hibernate level, else I will handle it with JSF controls

Thanks to all

andnaess wrote:

I use MySQL, and the scroll solution is a very bad idea on MySQL, I found that it was very slow. There is a much cleaner and more elegant approach, use a projection:

criteria.setProjection(Projections.rowCount());
Integer count = (Integer) criteria.uniqueResult();
itemCount = count.intValue();
criteria.setProjection(null);
criteria.setResultTransformer(Criteria.ROOT_ENTITY);

Notice the setProjection(null) and setResultTransformer which set the criteria query back to how it was.

(I do this before I set the max results).

This of course only works if you use the Criteria API, but I find that this API is quite useful for the typical cases where I need pagination anyway.[/code]

When I tested this solution against the scrolling solution (on mysql, which doesn't have cursors), I found that the scroll solution used the same amount of time as when I simply called list(), i.e. queried for all matching projects. The rowCount solution given here simply executes a COUNT(*) so it's much faster.

[/quote]