• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Connection, Resultset and statement

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hope this info is available from JDBC http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/basic.htm#1006509
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 2166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
axel,

I will coincide vth u
 
reply
    Bookmark Topic Watch Topic
  • New Topic