• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP - Database Pagination Implementation

 
Vishwa Kumba
Ranch Hand
Posts: 1066
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1066
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vishwa Kumba
Ranch Hand
Posts: 1066
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic