• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Closing a db connection

 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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.
 
maha anna
Ranch Hand
Posts: 1467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chris,
Please post your code so that we can tr to help.
regds
maha anna
 
Chris Klu
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nevermind my previous questions. I have figured out what was wrong.
Thanks maha
 
maha anna
Ranch Hand
Posts: 1467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just curious. What was wrong?
regds
maha anna
 
eric moon
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Chris Klu
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 247
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Tim Holloway
Saloon Keeper
Pie
Posts: 17995
47
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic