aspose file tools*
The moose likes JSP and the fly likes JSP - Database Pagination Implementation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "JSP - Database Pagination Implementation" Watch "JSP - Database Pagination Implementation" New topic
Author

JSP - Database Pagination Implementation

Vishwa Kumba
Ranch Hand

Joined: Aug 27, 2003
Posts: 1064
Hi,
I am not sure if my question is relevant to the JDBC forum or JSP forum. But I am posting it in this forum, as I believe this would have been implemented by my fellow JSP developers hanging out here.

We are required to retrieve rows from database and display it in JSPs with pagination support.(something like 50 items per page). The number of records returned could be different each time, the user selects a different filter criteria.

I am planning to use a ResultSet and store it in the User's Session/State data(Actually PageFlow in Weblogic). As each Pageflow(servlet) instance is unique to a user, I guess there wouldn't be any thread-safety issues. I believe ResultSet has an open connection to the database and I can iterate over it multiple times, either forwards or backwards.


Would like to know, it there is a better approach than this.


thanks,
Vishwa
Vishwa Kumba
Ranch Hand

Joined: Aug 27, 2003
Posts: 1064
I think another approach would be to open and close the resultset everytime, the user clicks on the NEXT/PREV button in my delegator class.
(the servlet controller calls the delegator class for the next set of records to be displayed)

I store only the current position in the session and the number of items to be retrieved and call the delegator class which runs the SQL Query again and retrieves a result set, gets the next set of object to be displayed in the UI and closes the result set.

Everytime I calculate the row position to move the cursor from the in the opened ResultSet, using the JDBC API call.

ResultSet.absolute(position);

How does this sound?
Nitin Singh
Greenhorn

Joined: Oct 14, 2004
Posts: 24
I would prefer scrollable resutset feature of JDBC 2.0 (your way). Its a clean way of doing pagination, I have used it at couple of place in my application and it works perfectly fine.
Other trick works with Oracle using rownum.
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Originally posted by Vishwa Kumba:

I am planning to use a ResultSet and store it in the User's Session/State data(Actually PageFlow in Weblogic).


It is almost always a bad idea to store resultsets and other database related resources in session etc because the database resouces stay busy for longer than necessary, in certain cases the may not get closed properly, and the session attributes may not get removed properly.
In almost all cases, it would be better to populate a Java data structure with the data in the result set, and then do whatever you want with it.

The following thread Stan James discusses three different approaches to paging -


http://www.coderanch.com/t/359232/Servlets/java/displaying-rows-per-page

cheers.


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Vishwa Kumba
Ranch Hand

Joined: Aug 27, 2003
Posts: 1064
Thanks Nitin and Sonny,
Stan's 3 approaches provided me the required information.
My approch(see my second post above) is the same as Stan's Approach1.

What I initially meant was approach3. I agree that holding ResultSet's is the Session is not a good idea.

Regards,
Vishwa
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JSP - Database Pagination Implementation
 
Similar Threads
PAGINATION
Pagination
Pagination
Pagination
Pagination