Whether closing the Connection tidies up the ResultSet and Statement by closing them or simply leaves them in an invalid state is up to the Driver, I suspect.
What is specified by the JDBC API is that you are not allowed to use them after closing their Connection. Nor may you use the ResultSet after closing its Statement.
Note: when using a connection pool, calling close() on the Connection typically returns it to the pool, leaving it open. You shouldn't count on this by using an open Statement/ResultSet since some other thread may get the Connection and interfere or the pool may decide it's "too old" and really close it. [ January 31, 2005: Message edited by: David Harkness ]
Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Joined: Aug 07, 2003
One refinement I'd make to that linked page is about closing the objects. You need to catch SQLException to make sure everything gets closed. This is the common pattern.This way, if closing the result set fails -- which is highly unlikely but possible -- you will still close the statement. Of course, I guess it's even possible it could throw a NullPointerException if there's a bug in the driver. Perhaps the closing of the statement should be a finally on the try that closes the result set, or maybe that's just going overboard.
Joined: Nov 30, 2004
I visited the URL.... Found this info...
You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close() method of the ResultSet and Statement classes. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.
Why is that the finalizer methods not implemented in the drivers?
Moreover how is the connection retained in the connection pool? how is it able to get back to the pool? how is this behaviour implemented?
Thanks in advance.
Joined: Aug 07, 2003
The reason to not depend on the finalizer is that you never know how long it will take before the objects are garbage collected. Since the result set and statement hang on to limited resources in the database, you want to close them as soon as you can.
Think of them as milk or something else you need to keep cold: you take it out of the refrigerator, pour yourself a glass, and put it back in the fridge right away. In the same way, you get the ResultSet, pull the data out into whatever objects you want to use to store it, and then close it right away.
When someone builds a connection pool library, they typically take the real Connection object retrieved from the driver when opening it and wrap it with a proxy. The proxy (look up the Proxy pattern) sends most method calls to the real Connection, like createStatement and such. But when you call close() on the proxy, instead of closing the Connection, it just returns it to the pool.
This makes your code totally oblivious to the fact that a pool is used at all. You get a DataSource and ask it for a Connection. You use the Connection as you normally would, and then "close" it. That the DS gives you a proxy from the pool and returns it to the pool instead of really opening and closing a connection doesn't matter to your code.
Older pools forced you to code to their API, using things like "getPool().getConnection()" and "getPool().release(conn)". This coupled your code to the particular pool. If you wanted to use a different (or no) pool, you had to rewrite a lot of code.
It is driver dependent. You may check the driver documentation. Info should be there if closing connection closes Statement and Resultsets created with this connection, too.
You can't rely on finalizer method. You don't know when it will run. I've read in Ted Newards Effective Enterprise Java that in a gc cycle not all objects eligible for garbage collection are really garbage collected. Some small objects might remain in memory. This has to do with optimization of garbage collector process. And from the VM standpoint ResultSets and Statements might be small object, cause they are thin wrappers around C++-objects (dependant on type of driver). No garbage collection, no finalizer call. So ResultSets and Statements stay open, which is not good.
You have basically the same issue with swt-objects or LotusDomino objects. You have to close them explicitly, too. They are JNI-wrappers around C/C++ functionality also. Both provide functionality to close/invalidate the objects created with the one whose close method you call.
this is at least how I understand it. Correct me if I am wrong.
Axel [ February 02, 2005: Message edited by: Axel Janssen ]