File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Passing ResultSet around Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Passing ResultSet around" Watch "Passing ResultSet around" New topic

Passing ResultSet around

Ben Wood
Ranch Hand

Joined: Aug 14, 2001
Posts: 342
Hi. I'm trying to query various databases and want to use the ResultSet in a different class to where the query is done. Most books show the following in the same class:
Loop through ResultSet
Close ResultSet
Close connection
But this is too simple for my needs. I have a class which displays a ResultSet in a JTable. So, I have a method in my JDBC connection wrapper class which returns the ResultSet object for the GUI class to display....but how and where can I close the resultset and connection, as the return statement has to come last in the method?

If I try and close the ResultSet in a finally block then the application complains that the DB has been closed before the data has been accessed!?
Any advice much appreciated. I am new to JDBC!
[ March 13, 2003: Message edited by: Ben Wood ]

SCJP 1.4,
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
This is poor architecture.
think model-view-controller.
you currently have the resultset in the model. create your own container & copy the resultset into that container.
Ben Wood
Ranch Hand

Joined: Aug 14, 2001
Posts: 342
Sorry Simon, I don't understand what you mean by model-view-controller - I am not a trained programmer as Java is a small aspect of my work, so this code is basically lifted from books. Could you please expand upon what you mean in practical terms?
I already have a JTable based GUI container called ResultsPane that I copy the ResultSet to.
Thanks for your help.
[ March 13, 2003: Message edited by: Ben Wood ]
[ March 13, 2003: Message edited by: Ben Wood ]
Tina Coleman
Ranch Hand

Joined: Dec 12, 2001
Posts: 150
Simon is suggesting decoupling your UI from any knowledge that it's working with a database - in your case, by leaving the ResultSet/connection/statement open so that you can return your resultset, you are effectively making the UI responsible for knowing that it has to close the ResultSet to free up a database connection. Bad idea. Instead, Simon suggests copying the resultSet data into some container that your application's GUI can use, letting you close the resultSet/connection/statement in the appropriate place while still having access to your data.
As a sample of how tying the GUI to the DB can burn you, I'm working to revamp an application whose GUI makes direct DB calls. Problem is, some of our clients have firewalls that block traffic on port 3306, the port our DB uses. So, we're revamping the application to communicate with a servlet to get its data. The DB access is, unfortunately, coupled so much with the GUI that we're having to do a near total rewrite. Had it been appropriately initially architected, this change wouldn't be such a big deal.
Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
In simpler terms, do something like the following:
Ben Wood
Ranch Hand

Joined: Aug 14, 2001
Posts: 342
Thanks guys, I think I get it now. Much appreciated.
Aashish Kaushik

Joined: Mar 20, 2003
Posts: 4
Better Still try DAO (Database Access object) for
abstracting and encapsulating database access.
This will Provide you a central line of control for all your database access and will sure relieve you of the mistakes you have made e.g where to close the ResultSet
Aashish Kaushik
Madhan Ramalingam

Joined: Aug 22, 2002
Posts: 7
CachedRowSet implementtion form sun can be used to cache the resultsets...
I agree. Here's the link:
subject: Passing ResultSet around
It's not a secret anymore!