aspose file tools*
The moose likes Servlets and the fly likes Closing a db connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Closing a db connection" Watch "Closing a db connection" New topic
Author

Closing a db connection

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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).]
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
"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.

Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
Chris,
Please post your code so that we can tr to help.
regds
maha anna
Chris Klu
Greenhorn

Joined: Jan 03, 2001
Posts: 18
Nevermind my previous questions. I have figured out what was wrong.
Thanks maha
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
Just curious. What was wrong?
regds
maha anna
eric moon
Ranch Hand

Joined: Nov 26, 2000
Posts: 133
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>
Chris Klu
Greenhorn

Joined: Jan 03, 2001
Posts: 18
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.
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
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?
Chris Klu
Greenhorn

Joined: Jan 03, 2001
Posts: 18
Maha I wrote some code to test again. Here it is:

Here is the output:
The db connection is closed.
Product ID: test1
Product ID: test2
Chris Klu
Greenhorn

Joined: Jan 03, 2001
Posts: 18
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.
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
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
Chris Klu
Greenhorn

Joined: Jan 03, 2001
Posts: 18
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).]
chris paredes
Greenhorn

Joined: May 07, 2001
Posts: 9
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.
Gerry Giese
Ranch Hand

Joined: Aug 02, 2001
Posts: 247
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
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16145
    
  21

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.


Customer surveys are for companies who didn't pay proper attention to begin with.
 
wood burning stoves
 
subject: Closing a db connection