wood burning stoves 2.0*
The moose likes JDBC and the fly likes consuquences of not closing ResultSet? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "consuquences of not closing ResultSet?" Watch "consuquences of not closing ResultSet?" New topic
Author

consuquences of not closing ResultSet?

Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
I have a class that establishes database connection, executes SQL query and returns ResultSet:

Since I return ResultSet and for it to get returned do not close ResultSet, what are consequences if any if I don't do rs.close()? Will ResultSet get collected by garbage collector since I call static method within a class?
thanks,
Alex


All right brain, you don't like me and I don't like you, but let's just do this one thing so I can get back to killing you with beer.<br /> <br />- Homer Simpson
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
In your method you are closing the Statement and Connection objects. Both of those actions will implicitly close the ResultSet object. So your method, as is, will just return a closed ResultSet.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
true. But can I not close Statement, ResultSet [and] Connection?
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
As was pointed out, you will be returning a closed resultset due to your closing the other objects. Some people don't close the statement, returning the resultset. However; this is flawed, as garbage collection on the statement before you use the resultset will sometimes occur - this will also close/garbage collect the resultset - giving a hard to reproduce problem.
It is best to return something other than ResultSet. See a discussion around this in this same forum at:
http://www.coderanch.com/t/300365/JDBC/java/disconnected-resultset
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60053
    
  65

Passing database objects (like ResultSet) around is an extremely poor practice. You should extract the data you need form the result set into value objects (or whatever makes sense for your app) and close all database objects as quickly as possible to free up the database resources.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jeffrey Hunter
Ranch Hand

Joined: Apr 16, 2004
Posts: 305
You may want to look at CachedRowSet implementation. This is not part of the standard API, but it is freely downloadable and it allows you to work with a "closed" ResultSet, which means you no longer need an open DB connection to process the ResultSet. So essentially, you get the data from the DB, populate the CachedRowSet, then you get out.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
With Oracle at least, there are some pretty severe limitations on the CachedRowSet, making it not generally applicable. The following is from the Oracle 9i docs:
CachedRowSet Constraints
All the constraints which apply to updatable result set are applicable here, except serialization, since OracleCachedRowSet is serializable. The SQL query has the following constraints:
-References only a single table in the database
-Contain no join operations
-Selects the primary key of the table it references
In addition, a SQL query should also satisfy the conditions below if inserts are to be performed:
-Selects all of the non-nullable columns in the underlying table
-Selects all columns that do not have a default value
Note: The CachedRowSet cannot hold a large quantity of data
since all the data is cached in memory.
Jeffrey Hunter
Ranch Hand

Joined: Apr 16, 2004
Posts: 305
Originally posted by Marshall B Thompson:
With Oracle at least, there are some pretty severe limitations on the CachedRowSet, making it not generally applicable. The following is from the Oracle 9i docs:
CachedRowSet Constraints
All the constraints which apply to updatable result set are applicable here, except serialization, since OracleCachedRowSet is serializable. The SQL query has the following constraints:
-References only a single table in the database
-Contain no join operations
-Selects the primary key of the table it references
In addition, a SQL query should also satisfy the conditions below if inserts are to be performed:
-Selects all of the non-nullable columns in the underlying table
-Selects all columns that do not have a default value
Note: The CachedRowSet cannot hold a large quantity of data
since all the data is cached in memory.

This may be true about OracleCachedRowSet, but I'm referring to the JDBC implementation, which is free of any of these constraints.
Alex Kravets
Ranch Hand

Joined: Jan 24, 2001
Posts: 476
thanks everyone. I guess, I'll do as suggested. But I never liked third-party anything (CachedRowSet), so will do it manually.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Here's my understanding of CachedRowSet with Oracle. Other db's may not have the same limitations as mentioned in this thread. Oracle's JDBC drivers return an OracleCachedRowset which is a subclass of CachedRowSet (jdbc standard). You can work with them using the default casting of CachedRowSet, but under the covers, it is an OracleCachedRowSet. Thus, the CachedRowSet, when used with oracle (9i), has the same limitations. Any oracle guru's out there - do I understand it correctly?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: consuquences of not closing ResultSet?
 
Similar Threads
Problem with preapred Statement
Problem in getting the data from DB using servlets
To Get the Number of Matches Found in the Database
Database access, potential threading problem?
Array Index Out Of Bounds Exception: 0 ( Mistake with My MySQL "count"?)