java中下一个,封闭连接：java中的下一个

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:

Open the connection

Prepare a statement

Get the result set

Return the result set

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.

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.