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.
posted 15 years ago
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.
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.
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 -