Closed Connection: next in java

Question

I have ResultSet Methods which I am closing the Connection in a finallly Block:

 public static ResultSet countdrcountcr(String vforacid) throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        try {

            conn = db.getDbConnection();
            String sql = "SELECT NVL (SUM (DECODE (part_tran_type, 'D', 1, 0)), 0), "
                    + " NVL (SUM (DECODE (part_tran_type, 'C', 1, 0)), 0) "
                    + " FROM tbaadm.htd WHERE acid IN (SELECT acid "
                    + " FROM tbaadm.gam WHERE foracid = '" + vforacid + "') "
                    + " AND tran_date >= '22-NOV-2013'  AND tran_date <= '30-NOV-2013' "
                    + " AND pstd_flg = 'Y' AND del_flg != 'Y'";
            PreparedStatement ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            return rs;
        } finally {
            conn.close();
        }
    }

But I am getting the error :

edit The whole ErrorTrace

Exception in thread "main" java.sql.SQLException: Closed Connection: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181)
at statement.Statement.main(Statement.java:34)
Java Result: 1

What am I not doing right?

Answer 1

You're returning a ResultSet for future use but after using it you're closing the connection, so you have no way to retrieve the data since the resource is already closed. Note that finally is always called, even if you return something in the try or catch code block, refer to Does finally always execute in Java?

In detail, this is the problem:

1. Open the connection
2. Prepare a statement
3. Get the result set
4. Return the result set
5. Close the connection (that may close the associated resources i.e. it may close the PreparedStatement and the ResultSet associated with the current Connection) because, as noted in the link before, finally block is always executed at least that the JVM crashes or you manually finish the application using System.exit.
6. Using a closed ResultSet. It is closed due to the previous step.

A possible solution would be that your countdrcountcr method and all other methods that return a ResultSet receive the Connection as parameter, so the method that calls it will handle the connection opening and closing. Also, take note that you should not use static methods to handle your database operations if you're working in a multi threaded environment e.g. a web application.

Answer 2

I think your query is taking a long time to execute and getting terminated by the driver/tomcat level.

Check you application context xml file for parameter removeAbandonedTimeout value.

removeAbandonedTimeout=300

means, if any query running for more than 300 seconds will be close by the JDBC driver. This is done to avoid connection pool "leak". To fix this you can set the value with some higher number.

More info about this param and other related parameters can be found here