jQuery in Action, 2nd edition*
The moose likes Beginning Java and the fly likes close sql connection in finally block Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "close sql connection in finally block" Watch "close sql connection in finally block" New topic
Author

close sql connection in finally block

Dean WinchesterD
Greenhorn

Joined: May 06, 2011
Posts: 13
Hi

I've been advised to close all sql connections in a finally block but what is the point of using the finally block if you need to catch another exception in it?

let's say we have:



Am I getting this right? What's the point of having a try/catch block if you need to do it all over again in the finally part?

I just don't understand why my original code was 'bad'. I'd rather not ask him right away and get some perspective on this.

Original:


Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36486
    
  16
It's very similar to a ReaderBut you should consider whether it is better to keep Connections open and cache them, rather than opening and closing them. I don't know about that latter point, which might be better answered on our databases forum. I shall keep this topic here for the time being, however.
Dean WinchesterD
Greenhorn

Joined: May 06, 2011
Posts: 13
Oh, I've always been told to place the finally block after the catch.

Your reply makes much more sense, thanks alot.

I'll see how it goes from here.


I've talked about them about always opening and closing the connections. It seemed rather ineffecient to me but they did not want me to change it. Might be worth some more research though.

Dean
Carey Brown
Ranch Hand

Joined: Nov 19, 2001
Posts: 170

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19543
    
  16

Except conn.close() can throw an SQLException of its own. That's why Dean had the initial try-catch inside the finally block.

I always use the form Campbell used*. No need to check for null or anything. The only drawback is that if conn.close() throws an SQLException this will discard any SQLException thrown by the rest of the try block, but that's something I'm willing to live with, as I've never had a close() operation on any connection / stream / Reader / Writer throw an exception.

* In fact, I think I taught him this form just a few weeks ago
Well, not completely true: http://www.coderanch.com/t/536851/java/java/finally-close. I just showed him that a null-check is unnecessary.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36486
    
  16
Rob Spoor wrote: . . . I always use the form Campbell used*. . . ..
I was taught a different form, but a few weeks ago when Rob pointed out that form is better.
Dean WinchesterD
Greenhorn

Joined: May 06, 2011
Posts: 13


Where sqlHelper is an instance of a class with all static sql methods.

Does this look clean/alright?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19543
    
  16

That's the general approach which you can use for your helper class, java.sql.Connection, java.io.Reader, java.io.Writer, InputStream, OutputStream, and basically anything you should close after use.
Dean WinchesterD
Greenhorn

Joined: May 06, 2011
Posts: 13
Thanks everyone, this was very helpful.

Dean
Matthew Brown
Bartender

Joined: Apr 06, 2010
Posts: 4240
    
    7

Dean WinchesterD wrote:Where sqlHelper is an instance of a class with all static sql methods.

If they are all static method you should just be using the class name to access them, not an instance.
Dean WinchesterD
Greenhorn

Joined: May 06, 2011
Posts: 13
Matthew Brown wrote:
Dean WinchesterD wrote:Where sqlHelper is an instance of a class with all static sql methods.

If they are all static method you should just be using the class name to access them, not an instance.


My bad, they are not static at all. Only the instance is.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: close sql connection in finally block
 
Similar Threads
finally block in Exceptions
Close if SQLException Occured?
Exception Handling-Purpose of finally in the try-catch construct
insert query not executing..
try catch finally sequence