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?
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:
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
.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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With