Can I get the opinion of the group on the current best way to handle a Java Web Application that wants to display Pageable, Sortable tables with very large data sets?

My current strategy is:

1) do a "select count(1) from tableA" to get the total # of records. Could be over 1,000,000.

2) do a "select * from tableA order by name" to get the data for the table.

3) Display the first 100 records and ignore the rest.

4) When the user hits the Next button run the query again, loop through the first 100 records just to get to record #101, display records 101 through 200, ignore the rest.

5) When the user asks to re-sort the table run the query again sorted on the new column. Go back to step #3.

Is there a better way using Hibernate correctly vs. these direct SQL calls I'm making?

Thanks!

Might be simpler to use

query.setFirstResult(index);
query.setMaxResults(count);

What anar wrote can also be found in the documentation.

11.4.1.5. Pagination
http://www.hibernate.org/hib_docs/v3/re ... pagination


Moreover, there is an article in the wiki:

Pagination
http://www.hibernate.org/314.html

paginationCode
http://www.hibernate.org/248.html


There is also an article in the Hibernate blog:
http://blog.hibernate.org/cgi-bin/blosx ... ation.html


And finally you can find several posting using the forum search (term: "pagination").

I think that should be enough. ;)

Best regards
Sven

Thank you. I got this all working but in order to get the total number of pages I'm doing a select count(1) from tableA first. And since my organization requires no raw sql this means a whole extra stored procedure.

Is there anyway to write just 1 stored procedure that returns the first result set with just the count, and the 2nd result set with all the data? Can hibernate handle the multiple result sets from JDBC?

How would I explain in the mapping doc to store the count in one var called count and store the rest as objects in a list?

The issues with 2 stored proecures becomes serious when the logic to do the where clause gets complex. We have to duplicate this code and then remember to make changes in two places.

Thanks.

-Andrew
Shopzilla.com
http://losangeles.craigslist.org/sof/94384708.html