java 重用语句和结果集是否会从其先前的使用中释放资源？或者我是否必须在重用之前明确关闭它们？

Does reusing a Statement and Resultset release resources from its previous usage? Or do I have to explicitly close them before reuse?

提问by heisenbergman

Sample code:

示例代码：

        aStmt = aConn.prepareStatement(aQuery);
        aRset = aStmt.executeQuery(cQuery);

        while (cRset.next()) {
            //stuff to determine value of parm1

            aStmt.setString(1, parm1);                
            aRset = aStmt.executeQuery(); 

            //more stuff
        }

Do I have to close aStmt and aRset after every loop inside the while statement? Or will reusing them in the succeeding loops release the memory/resources used from the previous loops?

我是否必须在 while 语句中的每个循环之后关闭 aStmt 和 aRset？或者在后续循环中重用它们会释放之前循环使用的内存/资源吗？

回答by Mark Rotteveel

The behavior of resultsets and (prepared) statements is explicitly documented in the Java API. I suggest that you read the actual documentation (and the JDBC spec) to get the details.

结果集和（准备好的）语句的行为在 Java API 中明确记录。我建议您阅读实际文档（和 JDBC 规范）以获取详细信息。

The StatementAPI says:

该StatementAPI说：

By default, only one ResultSetobject per Statementobject can be open at the same time. Therefore, if the reading of one ResultSetobject is interleaved with the reading of another, each must have been generated by different Statementobjects. All execution methods in the Statementinterface implicitly close a statment's current ResultSetobject if an open one exists.

默认情况下，ResultSet每个Statement对象只能同时打开一个对象。因此，如果一个ResultSet对象的读取与另一个对象的读取交织在一起，则每个Statement对象都必须由不同的对象生成。如果存在打开的对象，则接口中的所有执行方法都会Statement隐式关闭语句的当前ResultSet对象。

(emphasis mine).

（强调我的）。

In your specific code, when you call aStmt.executeQuery(), the old ResultSetassigned to aRsetis implicitly closed by the driver. That said, it would be better to explicitly close it yourself (or use Java 7 try-with-resources), to prevent you from forgetting to close the ResultSetin the last iteration through the loop.

在您的特定代码中，当您调用 时aStmt.executeQuery()，旧ResultSet分配给aRset驱动程序隐式关闭。也就是说，最好自己明确关闭它（或使用 Java 7 try-with-resources），以防止您忘记ResultSet在循环的最后一次迭代中关闭它。

Now to the PreparedStatement: When you prepare a statement (in general, implementation can vary), the query is sent to the server for compilation. On execution the parameters for that specific execution is sent to the server. Calling close()on aStmtwould result in the prepared statement being deallocated on the server, that is clearly NOTwhat you want here as you want to re-use the statement with different values for its parameter.

现在到PreparedStatement：当您准备一条语句时（通常，实现可能会有所不同），查询将发送到服务器进行编译。在执行时，该特定执行的参数被发送到服务器。调用close()上aStmt会导致准备好的声明中所释放的服务器，这显然是在不只要你想重新使用的语句与它的参数不同的值，你想要的这里。

So in short

所以简而言之

1. Closing ResultSetis not technically necessary here (except for the last ResultSetcreated), but it is better to do it explicitly
2. You should only close the PreparedStatementwhen you are done with it.

1. ResultSet在这里关闭在技​​术上不是必需的（最后ResultSet创建的除外），但最好显式执行
2. 您应该只在PreparedStatement完成后关闭它。

Using try-with-resourcesis one way to remove part of the confusion on these issues, as your code will automatically release resources when it is done with it (at the end of the scope of use):

使用try-with-resources是消除对这些问题的部分混淆的一种方法，因为您的代码将在完成后（在使用范围结束时）自动释放资源：

try (
    ResultSet cRset = cStmt.executeQuery(cQuery);
    PreparedStatement aStmt = aConn.prepareStatement(aQuery);
) {
    while (cRset.next()) {
        //stuff to determine value of parm1

        aStmt.setString(1, parm1);                
        try (ResultSet aRset = aStmt.executeQuery()) {
            //more stuff
        }
    }
}

At the end of this piece of code all JDBC resources are correctly closed (in the right order, even if exceptions occurred etc)

在这段代码的末尾，所有 JDBC 资源都正确关闭（按正确的顺序，即使发生异常等）

回答by Uwe Plonus

No, you may not close the ResultSetand Statementinside the whileloop.

不，您不能关闭循环内的ResultSet和。Statementwhile

You have to close them after the loop.

您必须在循环后关闭它们。

Also if you want to reuse the PreparedStatementthen you may not close it until you're ready with your processing.

此外，如果您想重用 ，PreparedStatement那么在您准备好处理之前您可能不会关闭它。

Best rule is to close such resources in the same block as they're created. In your case the best thing to do is to close the resources in a finallyblock after catching the SQLException.

最好的规则是在创建它们的同一块中关闭这些资源。在您的情况下，最好的做法是finally在捕获SQLException.

E.g.

例如

try {
    aStmt = aConn.prepareStatement(aQuery);
    cRset = cStmt.executeQuery(cQuery);

    while (cRset.next()) {
        //stuff to determine value of parm1

        aStmt.setString(1, parm1);
        try {
            aRset = aStmt.executeQuery();
        } finally {
            aRset.close();
        }

        //more stuff
    }
} catch (SQLException ex) {
    // Do error handling
} finally {
    // Close Resultset
}

In Java 7 you can use try with resources.

在 Java 7 中，您可以将 try 与资源一起使用。

回答by Evgeniy Dorofeev

PreparedStatement API: A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

PreparedStatement API：SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次有效地执行此语句。

But you cannot reuse a ResultSet object. When you call executeQuery on a PreparedStatement object the second time a new ResultSet is created, if you do not close the previous ResultSet you are risking to get a resource leak.

但是您不能重用 ResultSet 对象。当您在第二次创建新 ResultSet 时对 PreparedStatement 对象调用 executeQuery 时，如果您不关闭前一个 ResultSet，您将面临资源泄漏的风险。