关闭连接：java中的下一个

Closed Connection: next in java

提问by ErrorNotFoundException

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

我有 ResultSet 方法，我正在最后块中关闭连接：

 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?

我做错了什么？

采纳答案by Luiggi Mendoza

You're returning a ResultSetfor 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 finallyis always called, even if you return something in the tryor catchcode block, refer to Does finally always execute in Java?

您正在返回 aResultSet以备将来使用，但在使用它之后您将关闭连接，因此您无法检索数据，因为资源已经关闭。注意finally总是被调用，即使你在try或catch代码块中返回了一些东西，参考在 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 PreparedStatementand the ResultSetassociated with the current Connection) because, as noted in the link before, finallyblock is alwaysexecuted 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.

1. 打开连接
2. 准备一份声明
3. 获取结果集
4. 返回结果集
5. 关闭连接（这可能会关闭相关的资源，即它可能会关闭PreparedStatement和ResultSet与当前 相关的Connection），因为，如之前的链接中所述，至少在 JVM 崩溃或您使用 手动完成应用程序时，finally块总是会被执行System.exit。
6. 使用封闭的ResultSet. 由于上一步，它已关闭。

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

一个可能的解决方案是您的countdrcountcr方法和所有其他返回 a 的方法ResultSet接收Connectionas 参数，因此调用它的方法将处理连接打开和关闭。另外，请注意，static如果您在多线程环境（例如 Web 应用程序）中工作，则不应使用方法来处理数据库操作。

回答by Elliott Frisch

You're closing the underlying Connectionin your finally block... You're not closing the PreparedStatement (and you should, but you need to close that after you use your ResultSettoo). use the finally block of the caller (where you open the Connection). Also, you might want to consider using setFetchSize().

您正在关闭Connectionfinally 块中的底层代码......您没有关闭 PreparedStatement （您应该关闭它，但您也需要在使用您的之后关闭它ResultSet）。使用调用者的 finally 块（打开Connection）。此外，您可能需要考虑使用setFetchSize()。

回答by CodeChimp

You cannot close a Connectionthen use the ResultSet. You have to finish using the ResultSetfirst, then close the Connectionsometime after. The normal pattern is to finish your work with the ResultSetfirst, usually in a "Data Access Object", and return some encapsulated representation of the data as an object.

您不能关闭Connection然后使用ResultSet. 您必须使用完ResultSet第一个，然后在Connection一段时间后关闭。通常的模式是用第ResultSet一个完成您的工作，通常在“数据访问对象”中，并将数据的一些封装表示作为对象返回。

回答by Nathan Hughes

Luiggi's answer is correct but it seems like what the OP didn't understand was why closing the connection prevented the ResultSet from working, since the code got the ResultSet before the connection closed.

Luiggi 的回答是正确的，但似乎 OP 不明白为什么关闭连接会阻止 ResultSet 工作，因为代码在连接关闭之前获得了 ResultSet。

There's a popular misunderstanding that a ResultSet must be some kind of data-holding object that you can use to pass stuff around in. It isn't. it's just a reference to a database cursor, it hasn't actually fetched the data for a row until you call next()on it. It needs a live connection in order to work. You need to unpack your results from the query into a collection (usually a list) before you close the connection.

有一个普遍的误解，即 ResultSet 必须是某种数据保存对象，您可以使用它来传递内容。它不是。它只是对数据库游标的引用，在您调用next()它之前，它实际上并未获取一行的数据。它需要实时连接才能工作。在关闭连接之前，您需要将查询结果解压缩到一个集合（通常是一个列表）中。

BTW, don't add parameters to your SQL with string concatenation, it opens you up to SQL injection (and also handles quoting the parameters is a pain). You can add ?to your SQL and add values for the parameters by calling methods on the preparedStatement.

顺便说一句，不要使用字符串连接向 SQL 添加参数，它会打开 SQL 注入（并且处理引用参数很痛苦）。您可以?通过调用 PreparedStatement 上的方法添加到您的 SQL 并为参数添加值。

If you use Spring JDBC it will handle all the tedious JDBC stuff for you (including closing everything that needs to be closed), and all you have to handle is implementing a RowMapper to describe how to move data from the ResultSet into the collection.

如果您使用 Spring JDBC，它将为您处理所有繁琐的 JDBC 内容（包括关闭所有需要关闭的内容），您所要做的就是实现一个 RowMapper 来描述如何将数据从 ResultSet 移动到集合中。

回答by lrathod

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

我认为您的查询需要很长时间才能执行并被驱动程序/tomcat 级别终止。

Check you application context xml file for parameter removeAbandonedTimeoutvalue.

检查您的应用程序上下文 xml 文件中的参数removeAbandonedTimeout值。

removeAbandonedTimeout=300

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.

意味着，如果任何查询运行超过 300 秒，JDBC 驱动程序将关闭。这样做是为了避免连接池“泄漏”。要解决此问题，您可以将值设置为更高的数字。

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

可以在此处找到有关此参数和其他相关参数的更多信息

回答by vrlakshmi

If u tryied to close the connection inside the while block that time also u can get this kind of exception...so close the connection after the while block

如果你当时试图关闭 while 块内的连接，你也会得到这种异常......所以在 while 块之后关闭连接

package com.literals;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataBaseDemo {

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("driver is loading...........");
            Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:mytest","SYSTEM","murali");
            System.out.println("connection is established");
            Statement st=con.createStatement();
            System.out.println("statement is created");

            ResultSet rs=st.executeQuery("select * from student");
            while(rs.next()){
                System.out.println(rs.getString(1)+"          "+rs.getInt(2)+"          "+rs.getString(3)+"");
                con.close();

            }


        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }





    }

}

回答by Jaime Gabriel Valerio

Like say above: 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.

就像上面说的：另外，请注意，如果您在多线程环境（例如 Web 应用程序）中工作，则不应使用静态方法来处理数据库操作。

That really help.

那真的很有帮助。