persistent resultsets after connection is closed

[Marc E]

NOTE: I first posted this to comp.lang.java.databases but then saw that list
doesn't get much action. Please excuse the crosspost.

Now, then:

We typically keep our database methods in classes separate from our business
logic, and normally when we run, say, "getMyQueryStuff(blah)" that method
converts the resultset to a bean using apache commons DBUtils. But
sometimes, we just want to work with the resultset directly, as in returning
the resultset itself to the calling class; problem is, as far as I can tell,
you then can't close your connection pool in the database method, so that
connection is now left unreturned to the pool. Sure, the calling class can
close the resultset when it's finished with it, but that doesn't act on the
connection.

So...how do you usually deal with this? Are there any packages out there
that basicallly take a resultset and decouple it from the connection so that
the connection can be returned to the pool and the resultset can be returned
to the caller? Or are there better ways?

Thanks a lot.

 

[Bjorn Abelli]

NOTE: I first posted this to comp.lang.java.databases
but then saw that list doesn't get much action.
Please excuse the crosspost.

So, I'll give you the same comments here as in c.l.j.databases.. ;-)

IMHO I still believe that is a better newsgroup to post such questions in...

We typically keep our database methods in classes separate
from our business logic, and normally when we run, say,
"getMyQueryStuff(blah)" that method converts the resultset
to a bean using apache commons DBUtils.

That would be the common approach, to form the results into a "disconnected"
collection, not necessarily "beans" though.

But sometimes, we just want to work with the resultset
directly, as in returning the resultset itself to the
calling class; problem is, as far as I can tell, you
then can't close your connection pool in the database
method, so that connection is now left unreturned to the
pool. Sure, the calling class can close the resultset
when it's finished with it, but that doesn't act on the
connection.

The problem is rather that the ResultSet itself is more of a "cursor"
connected to the result still in the database. Hence, you can't close the
connection without also closing the cursor.

So...how do you usually deal with this?

As you already have done... ;-)

Though I try to design another layer between the business logic and the
layer where the actual Connections and ResultSets appear, i.e. to put all
relevant info into other types of collections to work with in the business
layer (Hashtables, ArrayLists, etc).

Are there any packages out there that basicallly take a resultset and
decouple it from
the connection so that the connection can be returned
to the pool and the resultset can be returned to the caller? Or are there
better ways?

I'm not exactly sure what you're looking for, but you could look into if
your drivers support RowSets.

http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/RowSet.html

...and possibly especially CachedRowsets

http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

// Bjorn A