ResultSet persistance with a Stateful Session Bean

[tylerblack]

JBoss 4.0

Like many others, I've been trying to persist a ResultSet accross multiple method calls to a Stateful Session Bean. The old problem of only wanting to display a page of results at a time, and calling the Stateful Session Bean to get the next/previous page.

Of course, the CacheConnectionManager is closing the open connection when the method that initially opened it returns. I realize after some reading that this is an anit-pattern and will not scale well. However, what are the alternatives?

I could use a stateless bean and make calls to the database passing limit and offset values, however this doesn't scale well with larger results and calls will get slower the further the user travels (with a greater offset). Granted, this may scale better than what I'm trying to do with persisting ResultSets.

Is there not a better solution?

Thanks,

Tyler

[adrian.brock]

To keep connections open across requests you need to use
BMT and unshared connections with an external transaction.

You are correct this does not scale. Each client has there own unshared connection.
You will quickly run out of connections as you add more clients.

This is true for any design that links an open connection to a client across
invocations.
e.g. what happens when the client goes to lunch instead of clicking "next page"

The usual technique is to either use page/size (direct support in MYSQL)
or store the primary key of the last value which you can use for subsequent queries:
When the user clicks next, you add
AND pk > pkOfLastEntryOnPreviousPage
to the query.

[tylerblack]

Thanks for the reply.

We're using PostgreSQL as our database, so limit/offset it is! We're also sorting the results returned by something other than the primary key, so the pk really has no relevance in the context that the results are being returned. However, that's a neat idea, and than you for it.

Cheers,

Tyler

[adrian.brock]

That can be done with UNIONs

... AND orderby1 = lastValue1 AND orderby2 = lastValue2 AND pk > lastPK
UNION
... AND orderby1 = lastValue1 AND orderby2 > lastValue2
UNION
... AND orderby1 > lastValue1

Or if UNIONs don't perform very well on your DB you can brute force it
running them as individual queries until the page is full.