consuquences of not closing ResultSet?

[Alex Kravets]

I have a class that establishes database connection, executes SQL query and returns ResultSet:

import java.io.*;
import java.lang.*;
import java.util.*;
import java.sql.*;
public class DBDriver {
        public static ResultSet getConnection(String conName, String sql){
                Statement stmt  = null;
                ResultSet rs    = null;
                Connection conn = null;
                try{
                        Class.forName("simba.jdbc.SimbaDriver");
                        conn = DriverManager.getConnection("jdbc:simba://127.0.0.1/" + conName, user, pass);
                        stmt = conn.createStatement();
                        rs = stmt.executeQuery(sql);
                        stmt.close();
                }
                catch (ClassNotFoundException cnfe){
                        System.err.println("Exception while establishing connection:\n" + cnfe.toString() );
                }
                catch (SQLException sqle){
                        System.out.println("Error closing connection = " + sqle.toString());
                }
                finally{
                        try{
                                if(conn != null)
                                        conn.close();
                        }
                        catch(SQLException sqle){
                                System.out.println("Error closing connection = " + sqle.toString());
                        }
                }
                return rs;
        }
}

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]

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]

true. But can I not close Statement, ResultSet [and] Connection?

[Marshall B Thompson]

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]

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]

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]

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]

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]

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]

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?