Paging Resultsets for a search JSP page

[Oliver Ng]

Hi,
i was wondering if anyone had opininos on paging resultsets and if its more beneficial to query the database upon each page reload, or store a resultset in a sessionvariable and merely reference that. This is for a search type page (imagine google).
i can imaging storing a reference variable to be a very wasteful operation, and how would i be able to track if a user isn't using the sessionvariable anymore?
i can also see a DB connection being a problem itself as it would involve many many connections to the database.
so which would you choose and/or recommend?
i've read this post and am interested in how this method would work:

mveitas
Member # 17216
posted July 18, 2001 10:53
Another approach, although more compilcated is to store the ResultSet and therefore you will have access to the cursor behind the scenes. We have implemented this successfully, storing the connection, statement, and result set in the user session. When the user closes the browser we use javascript to catch this and send a POST request to our servlet to clean up the db connection info associated with this "query".
On a side note, you might want to play with the fetch size to get optimal throughput on your system.
Matt

[ May 12, 2003: Message edited by: Oliver Ng ]

[Matthew Payne]

I like a approach that favors re-usability.
Check out the Display Taglib
The taglib impements paging and sorting.
Unfortunately the taglib does not directly support resultsets.
To quickly get around that, you could use the commons.beantutils to translate a resultset to a list.
use....>
import org.apache.commons.beanutils.RowSetDynaClass;
In your DAO layout you might have something like this --->

conn = ds.getConnection();
String sql = "SELECT user_id, date_taken, concat(first, ' ', last) as name, title, res.item_id, date_returned, res_id"
+ " FROM reservation_log res, people peo, items it"
+ " where res.user_id = peo.person_id and it.item_id = res.item_id";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
RowSetDynaClass rsdc = new RowSetDynaClass(rs);
close(rs);
List rows = rsdc.getRows();
/* expose this object to some context (session/application/request or put into cache */
request.setAttribute("RESERVATIONLOGS", rows);
-------------------------------------------->
The usage of the display tag might look like this -->
<display:table width="85%" name="RESERVATIONLOGS" scope="request" requestURI="/dvd/do/ReservationLog/list" pagesize="5" >
<display:column title="Index" value="5" />
<display:column property="res_id" title="ID" sort="true"/>
<display:column property="title" />
<display:column property="name" />
<display:columnAddition property="Index">
<bean:write name="current_row" property="name"/>

<a href="javascript:editReportCategory('dsf');">edit</a> |
<a href="javascript:removeReportCategory('ada');">delete</a>
</display:columnAddition>
<display:setProperty name="sort.behavior" value="list" />
<display:setProperty name="paging.banner.include_first_last" value="true" />
</display:table>
[ May 12, 2003: Message edited by: Matthew Payne ]