JSP, session, 'caching'

[Rico]

I'm working on this JSP page that's supposed to display data retrieved
from a database let's say 20 rows at a time. Currently, when we need the
x'th set of 20 rows, what's being done is to select the top 20 rows that
are not in the first (x-1) set of 20 rows.

Something tells me that somebody is just being naive here because a lot of
data is being requested for the purpose of being thrown away. I'm thinking
that it would be better if we could read the data into memory, suitable
data structures, and extract the rows from there as the user navigates
from one set/page of 20 rows to another.

However, the user would also be able to make modifications to the data. I
can either update the modified rows in the data structures accordingly or
throw everything away and refresh the data structures with the latest
contents of the database.

I'll be looking up some example of how to use what I think should be
'session' scope to achieve what I described. Suggestions welcome though.

I'm mainly wondering if there could be any tools or libraries that would
ease the implementation of this kind of, how to call it... caching? that
I am considering?

Thanks.

Rico.

 

[John C. Bollinger]

I'm working on this JSP page that's supposed to display data retrieved
from a database let's say 20 rows at a time. Currently, when we need the
x'th set of 20 rows, what's being done is to select the top 20 rows that
are not in the first (x-1) set of 20 rows.

Something tells me that somebody is just being naive here because a lot of
data is being requested for the purpose of being thrown away. I'm thinking
that it would be better if we could read the data into memory, suitable
data structures, and extract the rows from there as the user navigates
from one set/page of 20 rows to another.

However, the user would also be able to make modifications to the data. I
can either update the modified rows in the data structures accordingly or
throw everything away and refresh the data structures with the latest
contents of the database.

I'll be looking up some example of how to use what I think should be
'session' scope to achieve what I described. Suggestions welcome though.

I'm mainly wondering if there could be any tools or libraries that would
ease the implementation of this kind of, how to call it... caching? that
I am considering?

You could select all the rows into a scrollable, updatable ResultSet,
then hold the ResultSet in the session across requests. There are all
sorts of gotchas and caveats there, especially in the area of
scalability, but if you only need to support a handful of concurrent
users then it could be made to work without too much hassle. It has the
advantage of being able to cache unread results _in the DB_ rather than
in memory, supports user modifications in a straightforward way, and
only requires one query.

HOWEVER, if your DB schema is such that you can SELECT exactly the rows
you want each time (without too much hassle), then that is a much better
solution. It sounds from your comments like that's not what you're
doing now, but I'm not entirely confident about that.


John Bollinger
(e-mail address removed)

 

[Rico]

You could select all the rows into a scrollable, updatable ResultSet,
then hold the ResultSet in the session across requests. There are all
sorts of gotchas and caveats there, especially in the area of
scalability, but if you only need to support a handful of concurrent
users then it could be made to work without too much hassle. It has the
advantage of being able to cache unread results _in the DB_ rather than
in memory, supports user modifications in a straightforward way, and
only requires one query.

Thanks John. I've certainly taken note of the idea. Currently I am
working on getting the actual db connection mechanism right.
I'm going through tons of code where they were still doing:

Class.forname("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection(url, "", "");

Yet when the site was slow their brilliant idea was that they should
remove all the debug System.out.println() statements from the JSPs, or
that we should not have Enterprise Manager or SQL Query Analyzer open
on the server because upon closing them they saw that the speed was back
to normal.

Anyway, I am thrilled with having finally managed to get my hands dirty
with c3p0; even more thrilled by the results I'm observing

HOWEVER, if your DB schema is such that you can SELECT exactly the rows
you want each time (without too much hassle), then that is a much better
solution. It sounds from your comments like that's not what you're
doing now, but I'm not entirely confident about that.

Also to bear in mind for future endeavours.

Rico.

 

[Rico]

You could select all the rows into a scrollable, updatable ResultSet,
then hold the ResultSet in the session across requests. There are all
sorts of gotchas and caveats there, especially in the area of
scalability, but if you only need to support a handful of concurrent
users then it could be made to work without too much hassle. It has the
advantage of being able to cache unread results _in the DB_ rather than
in memory, supports user modifications in a straightforward way, and
only requires one query.

Amongst those gotchas and caveats, would there be a requirement to provide
the client with a close() method that they have to call for the bean to
know to release the connection?
Or is there a mechanism for when the session expires?
E.g, I am using a ConnectionPool and performing JNDI lookups by making use
of Tomcat's "context". So, upon the session expiring would the context
implementation be able to free the resources for reuse?

Thanks.

Rico.

 

[John C. Bollinger]

Amongst those gotchas and caveats, would there be a requirement to provide
the client with a close() method that they have to call for the bean to
know to release the connection?

That's not required, and since you can't safely assume that the client
will _use_ such a feature, it may not be worth your effort to code it.

Or is there a mechanism for when the session expires?

Yes, there is. The Servlet API contains interfaces HttpSessionListener
and HttpSessionBindingListener. The former is for receiving
notification when sessions are created and destroyed, and the latter is
for receiving notice when objects (attributes) are bound to or unbound
from a session. You can create an implementation of one of these and
configure your webapp's deployment descriptor to declare it as a
listener for the webapp. The container will automatically instantiate
the class for you and register the instance to receive the appropriate
events. Of the two relevant listeners, you will probably find the
latter easier to use.

You may also want to note several things:

() a Statement may only have one open ResultSet at a time. If you use a
Statement to execute a new query (or an update, I believe) then any
existing ResultSet associated with that Statement is closed.

() Closing a Statement automatically closes any ResultSet associated
with it, but the reverse is not true.

() A Connection may have multiple open Statements associated with it.
Closing a Connection probably causes all of them to close, but the API
docs (v. 1.4) do not guarantee that.

E.g, I am using a ConnectionPool and performing JNDI lookups by making use
of Tomcat's "context". So, upon the session expiring would the context
implementation be able to free the resources for reuse?

The context no responsibility anything like that. It is simply a naming
and directory provider. The ConnectionPool is the object that you might
imagine would have that responsibility, but it doesn't have enough
information to know when you are done with a particular Connection. It
is your responsibility to inform it by releasing the Connection back to
the pool when you're done with it. You can approach that problem by use
of one of the listeners described above.


John Bollinger
(e-mail address removed)