• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

consuquences of not closing ResultSet?

 
Alex Kravets
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Blake Minghelli
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Alex Kravets
Ranch Hand
Posts: 476
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
true. But can I not close Statement, ResultSet [and] Connection?
 
Marshall B Thompson
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64718
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeffrey Hunter
Ranch Hand
Posts: 305
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 305
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 476
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic