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: Connection Query 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 ]
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.
Joined: Aug 14, 2001
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 ]
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.
Hi, 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 Regards, Aashish Kaushik