I have servlet which queries a database through a non-servlet object. The object assigns the query to a ResultSet. Also, the object has a method that turns that ResultSet to a html table. I have a few questions about what happening. After I assign the ResultSet can I close the db connection and still access the ResultSet values? I tried to test this by calling con.close() and then accessing ResultSet. It worked, so I checked to see if the connection had actually closed. Apparently, even after I called close() it's still open. Why is the connection not closing? Is it necessary to explicitly close the db connection? If so, when and how should I close it? Will putting close() in finalize() work?
[This message has been edited by Chris K (edited January 04, 2001).]
"Chris K", The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please choose a new name which meets the requirements. Thanks.
Yes, I'm curious too. I have developed just such a method, but I'm not quite sure what to do if the result set contains more elements than I want to display in my HTML table. It sounds like you are maybe dealing with the same problem?? Otherwise, you can just create the table and close the connection, no problem, right?? But if you've only displayed part of the result set, should you close the connection or not??? I would think you'd need to keep it open to get access the the rest of the result set, or is this not the case? thanks!
<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>"Those who cast the votes decide nothing. Those who count the<BR>votes decide<BR>everything." <BR> -Joseph Stalin<HR></BLOCKQUOTE>
My test for the connection was incorrect. A book led me to use:
I guess this just tests if the Connection object exists not whether the db connection is still open. Instead of the above is used:
This is what I really wanted and works as expected. Eric: My object queries the database, assigns the results to a ResultSet and then, closes the db connection. After this, another method uses the ResultSet to make the html table. So it appears that you can close db connection before using the ResultSet.
Chris, Are you sure? Because, if we close a dbConnection, we can't perform any operation on the underlying database resources like ResultSet or Statement which are created from this connection. When I read your very first post itself, I thought your assumption about this concept may not be correct. Because of that only I was curious, how it would have worked. After your 2nd post I got a clear idea of what you are saying. In order to verify and check what you said, I just wrote a small program. In this java program,when I close the db connection, before looping Resultset object, when ran the program, I got a message as ' Your program performed an illegal operation....etc etc. and it will shut down' . In order to double check it, I commented the connection.close() statement, and the program happily ran and printed out the first col of of 'System' Table in my database. Could you verify this please?
Maha, I used the code you posted. Of course I made changes to the parameters. I uncommented the connection.close() statement. It works. Perhaps the fact that we are using different types of databases is effecting how the ResultSet is populated.
Chris, I copied your program and tested with Access database. Could not succeed. The output was 'DbConnection closed' alone and the same old 'Your program has performed an illegal operation...it will shut down' message came up. I looked into the API and everywhere for .close() method(Connection,Statement,ResultSet)it says, But it did not clearly say what is meant by database and JDBC resources. As you said, may be the implementation of all these interfaces by different drivers are different. I try to go to Sun's site and see if there is any solid information about this. Meanwhile if others have any information, please do post them here. regds maha anna
Maha, Thanks for your help. I have not been able to find any documentation to answer our question. I believe that the driver I am using (org.gjt.mm.mysql) is not following the correct API. It's actually convenient to be able to close the DB, then get the ResultSet later. However, I doubt that my code will be portable if I continue what I have been doing. So, I'm going to start closing the connection after get the results. You have probably saved me some later grief. Chris [This message has been edited by Chris Klu (edited January 08, 2001).]
I tried this code too. If you close the connection but not the statement, the result set is still available. If you close the statement though, you won't have access to the result set. Maybe someone with a little more knowledge can expound on why this is. I'm using SQL Server 2000 if it makes a difference. I don't see why it should though.
Chris Klu: getting back to your original post, I'm curious to know if you're in a high-traffic environment. I'm mid-range I guess, but what I always do (using the MVC pattern) in my plain-java model classes is unpack the ResultSet immediately and repackage the data into a collection (typically a List) with each row stored as it's own collection (typically a Hash). I then return that object and it makes things easier on logic code or presentation code needing to use the data, and is much more reusable. It makes for a little overhead, but not too much, and it allows you to kill off the RS, stmt, and conn. after the data is repackaged. Comments anyone? Am I brilliant or just off my rocker (a little of both, I hope) for doing this?
CJP (Certifiable Java Programmer), AMSE (Anti-Microsoft Software Engineer)
Author of Posts in the Saloon
Unless you're explicitly promised it's safe, I'd never dispose of superior objects before I was done with inferior ones. You could see strange side-effects like incomplete data because "x" many rows were prefetched, but when they're used up, the connection was gone so the next batch couldn't be fetched. There are internal references to the superior objects (rs.getStatement, statement.getConnection() will retrieve them), so they don't actually go away, but closing them prematurely may still cause problems. I'm pretty paranoid - I do it Gerry's way.
Sources may include data from the Fakebook Research Foundation with support from Gargle University