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

Connection, Resultset and statement

Ram Krish
Greenhorn

Joined: Nov 30, 2004
Posts: 23
Hi,

We close the resultset and statement first before closing the connection. What will happen if we close the connection alone? will the resultset and statement be closed automatically?

Thanks in advance.


Ram


Thanks,<br />Ram<br />SCEA-1,SCBCD,SCJP 1.4
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Ram,
Anything preventing you from trying it out and seeing for yourself?

If you are using Oracle, then as far as I know, closing the Connection will not close the associated, open Statements and ResultSets.

Good Luck,
Avi.
Ram Krish
Greenhorn

Joined: Nov 30, 2004
Posts: 23
Hi,

I tried using the resultset after closing the connection, this throwed me an exception (java.sql.SQLException: Resultset is closed).

When i tried to use the Statement i got java.sql.SQLException: Invalid Handle.

When i tried to use the connection itself i got java.sql.SQLException: General error.


Thanks
Ram
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
Hope this info is available from JDBC http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/basic.htm#1006509


Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
Ram Krish
Greenhorn

Joined: Nov 30, 2004
Posts: 23
Hi,

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.

Ram
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
Axel Janssen
Ranch Hand

Joined: Jan 08, 2001
Posts: 2164
Ram,

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 ]
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
axel,

I will coincide vth u
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Connection, Resultset and statement