aspose file tools*
The moose likes JDBC and the fly likes Closing Database Resources Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing Database Resources" Watch "Closing Database Resources" New topic
Author

Closing Database Resources

Pranav Raulkar
Ranch Hand

Joined: Apr 20, 2011
Posts: 73

Hi All,

Wanted your opinions on the code below for closing JDBC resources. PROS / CONS

Habeeb Shaikh
Ranch Hand

Joined: Nov 23, 2008
Posts: 48
Hi, I think its good idea that whenever we done with our db work, we should closed it or set null.It is a memory consuming resources.Set manually null is a good practice.It make free your memory whenever daemon thread runs.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Habeeb Shaikh wrote:Hi, I think its good idea that whenever we done with our db work, we should close it or set null....

It should have been "we should close it and set it to null". Closing is the more important part. Setting to null is probably optional, at least if you keep the reference in a local variable, as it will fall out of scope and therefore be eligible for garbage collection anyway.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Pranav, a few notes on the method:

1) I'd probably create several overloaded "close" methods, one for each type of JDBC object. That way I wouldn't have to cast and would get a compiler warning if I tried to pass a wrong object to the method.

2) The catch block should only catch SQLException errors, not Exception ones, especially as you aren't passing it to the caller. Also, I'd make sure to write the exception into a log. Depending on your exact settings, the System.err might end up in a log, though using some logging framework would be more robust.

3) The finally block should be removed. It does not do any harm, but it also does not achieve anything. The dbResource variable will fall out of scope immediately after the finally block executes anyway. Moreover, you should understand that the dbResource variable contains a copy of the reference that was passed to the method by the caller, by setting dbResource to null, the other reference(s) are not nulled; if you understand this, you'll also understand why the finally block is useless.

Other than this, I think it is OK; I've got similar methods in my project.
Pranav Raulkar
Ranch Hand

Joined: Apr 20, 2011
Posts: 73

HI Martin, thanks for the detailed comments.

I'd probably create several overloaded "close" methods

The reason I wrote this method was exactly yo avoid this.
That way I wouldn't have to cast and would get a compiler warning if I tried to pass a wrong object to the method

Since I'm accepting Object in my method, why would compiler flash a warning? Even if one passed another object type, it would get filtered out in the if conditions.
The catch block should only catch SQLException errors

Agree. Actually this is just a sample I wrote in hurry
Moreover, you should understand that the dbResource variable contains a copy of the reference

Objetcs are passed by reference. No such thing as copy of reference?
Ivan Jozsef Balazs
Rancher

Joined: May 22, 2012
Posts: 877
    
    5
>>I'd probably create several overloaded "close" methods
>The reason I wrote this method was exactly yo avoid this.

Interface Closeable might come in useful.


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Ivan Jozsef Balazs wrote:>>I'd probably create several overloaded "close" methods
>The reason I wrote this method was exactly yo avoid this.

Interface Closeable might come in useful.

However, that is not feasible for classes you don't have any control over (such as interfaces that are part of an API).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Pranav Raulkar wrote:Since I'm accepting Object in my method, why would compiler flash a warning? Even if one passed another object type, it would get filtered out in the if conditions.

If you by mistake call close() over a wrong variable (say, a String), you don't get compiler error or warning. A casual look at the code might lead you to believe that you're closing your resources while you in fact aren't.

You might throw an exception from within your method when an unhandled type gets passed (I my opinion, not doing so is a bug), but this means that if you do the mistake I've mentioned, the mistake gets reported at runtime.

Having several overloaded methods would reveal similar mistakes at compile time (or, in any modern IDE, at the time you wrote it). I always favor compile time checks to runtime checks, for reasons I've explained here. Admittedly, this is a subjective matter, but my feelings are pretty strong on this one.

Objetcs are passed by reference. No such thing as copy of reference?

The exact statement would be that references are passed by value. This is an important distinction; assigning null to a method parameter does not alter the value of a variable (reference) passed to that method in any way.
Ivan Jozsef Balazs
Rancher

Joined: May 22, 2012
Posts: 877
    
    5
Martin Vajsar wrote:
However, that is not feasible for classes you don't have any control over (such as interfaces that are part of an API).


It is however useful, if you are using such a version of the API, where the interfaces have been retrofitted to extend Closeable or AutoCloseable.



Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Are such retrofitted interfaces usable with Java 6? That would be news to me, and indeed useful.

If there are available in Java 7 only (which I have thought up to now is the case), I'd certainly use the try-with-resources construct and wouldn't need my own close() method like this.
Ivan Jozsef Balazs
Rancher

Joined: May 22, 2012
Posts: 877
    
    5
Martin Vajsar wrote:Are such retrofitted interfaces usable with Java 6? T


The Closeable interface was introduced with 1.5 and first applied to java.io.* stuff.
For example Connection was retrofitted to extend it in with Java v1.7.
However a given concrete implementing class fabricated for say Java v1.5 might or might not extend it.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Ivan Jozsef Balazs wrote:For example Connection was retrofitted to extend it in with Java v1.7.
However a given concrete implementing class fabricated for say Java v1.5 might or might not extend it.

Even if the concrete class would implement it, I couldn't utilize that fact, since all my variables are declared using the proper JDBC API interface. I couldn't pass them to a close(Closeable closeable) method. So no luck with Java 6.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19762
    
  20

Ivan Jozsef Balazs wrote:The Closeable interface was introduced with 1.5 and first applied to java.io.* stuff.
For example Connection was retrofitted to extend it in with Java v1.7.

Closeable defines one method close() that can throw an IOException. This makes the interface unsuitable for Connection etc. That's why Java 7 introduced interface AutoCloseable with method close() that can throw any Exception. Closeable extends AutoCloseable.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
 
subject: Closing Database Resources