Java - 连接关闭后无法使用 ResultSet

Java - Can't use ResultSet after connection close

提问by mlodikkal

I have a problem with closing a connection to MySQL.

我在关闭与 MySQL 的连接时遇到问题。

I'm getting the error:

我收到错误：

java.sql.SQLException: Operation not allowed after ResultSet closed

java.sql.SQLException: ResultSet 关闭后不允许操作

My code:

我的代码：

public static ResultSet sqlquery (String query)
{
 ResultSet rs=null;
 Connection connection=null;
 Statement st=null;
 try{   
     Class.forName("com.mysql.jdbc.Driver");
     connection = DriverManager.getConnection("databaseadress","username","password");
     st = connection.createStatement();  
     rs = st.executeQuery(query);

    }catch(SQLException e){System.out.println("SQL error: " + e);}
      catch(Exception e){System.out.println("Error: " + e);}
       finally {
       try{
          if(rs != null) rs.close();
          if(st!= null) st.close();
          if(connection != null)  connection.close();
  }catch(SQLException e){System.out.println("SQL error : " + e);}

    }
     return rs;
}

回答by Nathan Hughes

JDBC doesn't bring back all the results of a query in a ResultSet, because there may be too many of them to fetch them all eagerly. Instead it gives you something you can use to retrieve the results, but which goes away when the connection closes. So when you pass it back from your method after closing the database connection, nothing else can use it.

JDBC 不会带回 ResultSet 中查询的所有结果，因为它们可能太多而无法急切地获取它们。相反，它为您提供了一些您可以用来检索结果的东西，但是当连接关闭时它就会消失。因此，当您在关闭数据库连接后从您的方法中将其传回时，其他任何东西都无法使用它。

What you can do instead is to have this method use the resultSet to populate an object or a collection of objects, and pass that populated object back.

您可以做的是让此方法使用 resultSet 来填充一个对象或一组对象，然后将该填充的对象传回。

If you change your code to pass in a rowMapper (that takes a resultSet and passes back an object populated with the current row in the resultset), and use that to populate a container object that you pass back, then you'll have something as reusable as what what you've written, but which actually works because it doesn't depend on having the connection held open after the call is finished.

如果您将代码更改为传入一个 rowMapper（它接受一个 resultSet 并传回一个填充了结果集中当前行的对象），并使用它来填充您传回的容器对象，那么您将拥有以下内容可以像你写的那样重用，但实际上是有效的，因为它不依赖于在调用完成后保持连接打开。

Here's your example code rewritten to use the rowmapper, get rid of some unnecessary exception-catching, and fix a bug that will prevent the connection from getting closed in some cases:

这是您重写的示例代码以使用 rowmapper，摆脱一些不必要的异常捕获，并修复了在某些情况下会阻止连接关闭的错误：

public static List<T> sqlquery (String query, RowMapper<T> rowMapper) throws SQLException
{
    Connection connection=null;
    Statement st=null;
    ResultSet rs=null;
    // don't need Class.forName anymore with type4 driver     
    connection = DriverManager.getConnection("databaseadress","username","password");
    st = connection.createStatement();  
    rs = st.executeQuery(query);
    List<T> list = new ArrayList<T>();
    while (rs.next()) {
        list.add(rowMapper.mapRow(rs));
    }
    // don't let exception thrown on close of
    // statement or resultset prevent the
    // connection from getting closed
    if(rs != null) 
        try {rs.close()} catch (SQLException e){log.info(e);}
    if(st!= null) 
        try {st.close()} catch (SQLException e){log.info(e);}
    if(connection != null)  
        try {connection.close()} catch (SQLException e){log.info(e);}
    return list;
}

If you don't catch each exception thrown on close individually, as shown above, you risk failing to close the connection if either the statement or the resultSet throw an exception on close.

如果您没有单独捕获关闭时抛出的每个异常，如上所示，如果语句或结果集在关闭时抛出异常，则您可能无法关闭连接。

This is similar to what spring-jdbc does, it defines a RowMapperas:

这类似于 spring-jdbc 所做的，它将RowMapper定义为：

public interface RowMapper<T> {
    T mapRow(ResultSet, int rowNum) throws SQLException;
}

The next step is going to be parameterizing your queries so you don't have to surround parameter values in quotes or worry about sql injection. See this answerfor an example of how spring-jdbc handles this. The long term answer here is, it would be better to adopt spring-jdbc or something similar to it than to reinvent it piecemeal.

下一步将是参数化您的查询，这样您就不必将参数值括在引号中或担心 sql 注入。有关spring-jdbc 如何处理此问题的示例，请参阅此答案。这里的长期答案是，最好采用 spring-jdbc 或类似的东西，而不是零碎地重新发明它。

回答by Jim Garrison

This is the way JDBC works. In your code you closed the ResultSetand the Connection, after which the ResultSetis no longer usable. If you want it to be usable you must leave it (and the Connection) open.

这就是 JDBC 的工作方式。在您的代码中，您关闭了ResultSet和Connection，之后ResultSet不再可用。如果您希望它可用，则必须将其（和Connection）保持打开状态。

However, if you return the ResultSet, you should refactor your code so the calling method provides the Connection.

但是，如果您返回ResultSet，则应该重构您的代码，以便调用方法提供Connection.

回答by Paco Lagunas

Once the connection is closed you can no longer use any of the resources (statements, prepared statements, result sets), all of them are automatically closed. So you need to do all of your processing while the resources are open.

一旦连接关闭，您就不能再使用任何资源（语句、准备好的语句、结果集），它们都会自动关闭。因此，您需要在资源打开时进行所有处理。

Try filling and returning a DTO, this way you can have the data you need without keeping a connection alive.

尝试填充并返回 DTO，这样您就可以在不保持连接活动的情况下获得所需的数据。