wood burning stoves 2.0*
The moose likes JDBC and the fly likes Please help clarify this! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Please help clarify this!" Watch "Please help clarify this!" New topic
Author

Please help clarify this!

Pat Wallwork
Ranch Hand

Joined: Sep 23, 2001
Posts: 72
Hi,
I need to understand what is happening as this is driving me nuts. I make fairly heavy use of a database so I wrote a DBUtils class to handle a lot of my repetitive tasks.
One of the methods is getConnection() which simply returns a Connection object. In that method I use the try/catch/finally block, to try and get a connection, catch any error that may occur, and finally close the connection. The part that puzzles me is I return the Connection object, and then close it in the finally section. Why does this not kill the Connection in the page that called that method? My understanding is that the finally statement executes even when you have used the return statement. Is it possible that my method getConnection() actually passes a copy of the Connection object, and that the finally statement kills the original object? You wouldn't believe how much this has been bugging me trying to understand this! I want to make sure connections are closed so I use finally in the DBUtils to do that, plus the page that calls the method getConnection() also uses con.close() to close with as well...so it does seem like I have 2 connection objects with one method call!
Secondly, in my DBUtils class I have another method that handles pagination for me. Any page I have that makes a database query simply passes the ResultSet to my DBUtils.pagination(ResultSet rs) method and it does the work of setting up the paging. This method has no database connection and just works off the ResultSet...and it works very well! Does the ResultSet maintain some kind of internal connection to the database when it's originally created? When I pass the rs to the pagination() method, is it really a copy of the rs or the actual one?
Ok, I'm done for now! Sorry for the long post but I would really like to gain some insight on how this works. I'm not comfortable with the fact that it just does work but I don't completely understand why!
Thanks in advance for your insight,
-Pat
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Can you post just the end of the try and the catch/finally part?
Mixing return statements and try/catch/finally becomes non-deterministic and can lead to danger.
No-one ever believes me so one day I'll go find the test I wrote, but mixing the two can make the code behave in ways you didn't intend or expect...
Dave
Pat Wallwork
Ranch Hand

Joined: Sep 23, 2001
Posts: 72
Thanks for the reply David,
I finally found some information in a book I had and it solved my questions. I removed the finally statement from my method getConnection() as it's not needed and in fact, it is questionable that it was even working for me, since the finally statement should have closed the connection right after it was returned.
Like I said, I knew it was working but didn't understand why it was...and that was driving me nuts!
-Pat
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Originally posted by Pat Wallwork:
Hi,
One of the methods is getConnection() which simply returns a Connection object. In that method I use the try/catch/finally block, to try and get a connection, catch any error that may occur, and finally close the connection. The part that puzzles me is I return the Connection object, and then close it in the finally section. Why does this not kill the Connection in the page that called that method? My understanding is that the finally statement executes even when you have used the return statement. Is it possible that my method getConnection() actually passes a copy of the Connection object, and that the finally statement kills the original object? You wouldn't believe how much this has been bugging me trying to understand this! I want to make sure connections are closed so I use finally in the DBUtils to do that, plus the page that calls the method getConnection() also uses con.close() to close with as well...so it does seem like I have 2 connection objects with one method call!

As David posted, you can get some weird results when you mix finallys and returns. However, that usually happens when you have a return statement inside a catch or finally block.
It's hard to tell what is occuring without seeing any code, but here are some comments. What do you mean by "kill?" The close() method does not invalidate the reference. That is, suppose you have

This code will return a valid Connection reference. The connection will be closed, but the Connection object still exists, and the caller will get a non-null reference to the Connection.
However, removing the finally block as you said in another post, is the correct thing to do. If you have a method that hands out Connection objects, you don't want that same method to close the Connection. That method has no indication when the caller is finished with the Connection! It should be up to the user of the Connection to close the Connection. In fact, you don't want to close the Connection UNTIL you are done with any ResultSet, and Statement that was created with the Connection. Closing the Connection is supposed to close ResultSets and Statements as well. Thus, it should be the responsibility of the user of the Connection/Statement/ResultSet to close those objects when they are no longer needed.
You can put some utility methods in your DBUtils to do this. You would have three methods that look something like this:

with similar methods for Statement and ResultSet. This won't guarantee that the objects will be closed, you must rely on the user for that. However, it will make it a little easier to close the objects. (Otherwise, you end up littering your jdbc code with try-catch blocks every time you want to close something. This way it's a simple line of code: DBUtil.close(conn).

Secondly, in my DBUtils class I have another method that handles pagination for me. Any page I have that makes a database query simply passes the ResultSet to my DBUtils.pagination(ResultSet rs) method and it does the work of setting up the paging. This method has no database connection and just works off the ResultSet...and it works very well! Does the ResultSet maintain some kind of internal connection to the database when it's originally created? When I pass the rs to the pagination() method, is it really a copy of the rs or the actual one?

Yes, the ResultSet maintains a connection to the database, because the ResultSet does not necessarily retrieve all the data at once. Suppose you had a query that returned a million rows; the ResultSet is not returned with all millions rows in memory. As you page through the results, the ResultSet may need to retrieve more data from the database.
When you pass ANY reference to a method, a copy of the reference is passed to the method. That means that while the thread of execution is inside the method, there are two copies of the reference to the original object: the original reference, and the copy inside the method. Using that reference you can call any of the methods of the object to which it refers. Once execution leaves the message, the copy of the reference is out of scope and can no longer be used.
Pat Wallwork
Ranch Hand

Joined: Sep 23, 2001
Posts: 72
Kevin,
I really appreciate the time you took to write your response...it cleared up a lot for me!
I was having a hard time trying to explain what I was concerned about, which basically boils down to whether I needed to close connections both in my method that creates one, and the page that calls that method. My primary concern was unknowingly leaving connections open that were not needed. Your explanation of the 'reference' clears up how this works now.
This does cause me to ask one other thing though and that is why I would define my own close() method. Normally, what I currently do with any page (servlet, jsp's) that calls my getConnection() method I close the connection, statement, and resultSet in a try/catch block after I am done with it, like this:

Would it then be better for me to define my own close() method in my DBUtils class instead?
As for the ResultSet part, I believe from what your telling me that I don't have to worry about closing it in my pagination(rs) method now as I am only passing the reference of the rs to it, right? The pagination() method returns a String and then that reference to the rs is now out of scope. So in the original page that called the method pagination(rs) the very next line I can do rs.close(); con.close(); ps.close(); and hopefully all database objects are properly closed and my code can carry on.
That makes sense to me! I hope I have it right!
Thanks again,
-Pat
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Originally posted by Pat Wallwork:

This does cause me to ask one other thing though and that is why I would define my own close() method. Normally, what I currently do with any page (servlet, jsp's) that calls my getConnection() method I close the connection, statement, and resultSet in a try/catch block after I am done with it, like this:

Would it then be better for me to define my own close() method in my DBUtils class instead?

In my opinion, yes. For two reasons. First, if you have several pages that access the database, then you end up with try-catch blocks everywhere you want to close something. By putting that code into your DBUtils class, you just need a single line of code to close something.
Second, the code above is fine, and will probably work 99.99999% of the time. But there is the very very small chance that the rs.close() call will throw an exception, and thus the ps.close() and con.close() calls will be skipped. And now you have an unclosed connection. By putting three close() methods into your DBUtils class, you can ensure that even if close(ResultSet) throws an exception, the close(Connection) will still get called.

As for the ResultSet part, I believe from what your telling me that I don't have to worry about closing it in my pagination(rs) method now as I am only passing the reference of the rs to it, right? The pagination() method returns a String and then that reference to the rs is now out of scope. So in the original page that called the method pagination(rs) the very next line I can do rs.close(); con.close(); ps.close(); and hopefully all database objects are properly closed and my code can carry on.

Yes, the pagination() method should almost certainly NOT be the method to close the ResultSet. Because the ResultSet reference is passed to the pagination() method, that method has no information about whether the ResultSet may be needed elsewhere by the caller of the method. The class or method that controls the connection should be responsible for closing resources.
Pat Wallwork
Ranch Hand

Joined: Sep 23, 2001
Posts: 72
Kevin,
Again, thank you very much. I will add the close() method to my DBUtils class now. You pointed out another thing I never thought about in that I can avoid the try/catch blocks in my jsp pages this way which will cut down on the code there...and that's a good thing
I'm really grateful for your insight. I had concerns about writing code that wasn't properly closing connections and now understand exactly how and when to do it.
Originally what started this was a systems administrator who accused me of leaving them open in my code causing all db connections to be handed out over a period of time. I denied this as I knew I carefully was closing them but got to wondering if I only thought I was closing them and actually was leaving some open. Armed with my newly aquired understanding, I'm prepared to play a game of 'not my fault, must be yours...' with the sys admin!
Thanks again,
-Pat
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Please help clarify this!