Java Try / Try-with-resources 和 Connection、Statement 和 ResultSet 关闭

Try / Try-with-resources and Connection, Statement and ResultSet closing

提问by José D.

I have recently having some discussions with my professor about how to handle the basic jdbc connection scheme. Suppose we want to execute two queries, this is what he proposes

我最近和我的教授讨论了如何处理基本的 jdbc 连接方案。假设我们要执行两个查询，这就是他提出的

public void doQueries() throws MyException{
    Connection con = null;
    try {
        con = DriverManager.getConnection(dataSource);
        PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
        PreparedStatement s2 = con.prepareStatement(selectSqlQuery);

        // Set the parameters of the PreparedStatements and maybe do other things

        s1.executeUpdate();
        ResultSet rs = s2.executeQuery();

        rs.close();
        s2.close();
        s1.close();
    } catch (SQLException e) {
        throw new MyException(e);
    } finally {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e2) {
            // Can't really do anything
        }
    }
}

I don't like this approach, and I have two questions about it:

我不喜欢这种方法，我有两个问题：

1.A) I think that, if any exception is thrown where we do 'other things', or in the line rs.close()or s2.close()then s1wouldn't have been closed when the method ends. Am I right about that?

1.A），我认为，如果有异常被抛出，我们做“其他的事情”，或者在该行rs.close()还是s2.close()那么s1就不会被关闭的方法结束时。我是对的吗？

1.B) The professor keeps asking me to explicitly close the ResultSet (even when the Statement documentation makes clear that it will close the ResultSet) She says that Sun recommends it. Is there any reason to do so?

1.B) 教授一直要求我明确关闭 ResultSet（即使 Statement 文档明确表示它将关闭 ResultSet）她说 Sun 推荐它。有什么理由这样做吗？

Now this is what I think is the correct code for the same thing:

现在这是我认为是同一件事的正确代码：

public void doQueries() throws MyException{
    Connection con = null;
    PreparedStatement s1 = null;
    PreparedStatement s2 = null;
    try {
        con = DriverManager.getConnection(dataSource);
        s1 = con.prepareStatement(updateSqlQuery);
        s2 = con.prepareStatement(selectSqlQuery);

        // Set the parameters of the PreparedStatements and maybe do other things

        s1.executeUpdate();
        ResultSet rs = s2.executeQuery();

    } catch (SQLException e) {
        throw new MyException(e);
    } finally {
        try {
            if (s2 != null) {
                s2.close();
            }
        } catch (SQLException e3) {
            // Can't do nothing
        }
        try {
            if (s1 != null) {
                s1.close();
            }
        } catch (SQLException e3) {
            // Can't do nothing
        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e2) {
            // Can't do nothing
        }
    }
}

2.A) Is this code correct? (Is it guaranteed that all will be closed when the method ends?)

2.A) 此代码正确吗？（是否保证方法结束时全部关闭？）

2.B) This is very large and verbose (and it gets worse if there are more Statements) Is there any shorter or more elegant way to do this without using try-with-resources?

2.B）这是非常大和冗长的（如果有更多的语句会变得更糟）有没有更短或更优雅的方法来做到这一点而不使用try-with-resources？

Finally this is the code I like the most

最后这是我最喜欢的代码

public void doQueries() throws MyException{
    try (Connection con = DriverManager.getConnection(dataSource);
         PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
         PreparedStatement s2 = con.prepareStatement(selectSqlQuery))
    {

        // Set the parameters of the PreparedStatements and maybe do other things

        s1.executeUpdate();
        ResultSet rs = s2.executeQuery();

    } catch (SQLException e) {
        throw new MyException(e);
    }
}

3) Is this code correct? I think my professor doesn't like this way because there is no explicit close of the ResultSet, but she has told me that she is fine with it as long as in the documentation it is clear that all is closed. Can you give any link to the official documentation with a similar example, or based in the documentation show that there is are no problems with this code?

3）这个代码正确吗？我认为我的教授不喜欢这种方式，因为 ResultSet 没有明确的关闭，但她告诉我，只要在文档中很清楚一切都已关闭，她就可以接受。您能否提供任何带有类似示例的官方文档的链接，或者根据文档表明此代码没有问题？

回答by Martin Serrano

This is indeed the primary motivation for try-with-resources. See the Java tutorialsas reference. Your professor is out-of-date. If you want to deal with the result set issue you can always enclose it in another try-with-resources statement.

这确实是尝试资源的主要动机。请参阅 Java教程作为参考。你的教授已经过时了。如果您想处理结果集问题，您始终可以将其包含在另一个 try-with-resources 语句中。

回答by Nathan Hughes

The fun thing about JDBC code is that you're coding to a spec where it's not always clear how compliant your implementation is. There are a lot of different databases and drivers and some drivers are better-behaved than others. That tends to make people err on the side of caution, recommending things like closing everything explicitly. You could be ok with closing only the connection here. Closing the resultSet just to be on the safe side is hard to argue with. You don't indicate what database or driver you're using here, i wouldn't want to hardcode in assumptions about the driver that might not be valid for some implementation.

JDBC 代码的有趣之处在于，您正在根据规范进行编码，而在该规范中，您的实现的合规性并不总是很清楚。有很多不同的数据库和驱动程序，有些驱动程序比其他驱动程序表现得更好。这往往会使人们在谨慎方面犯错误，建议明确关闭所有内容。您可以只关闭此处的连接。关闭 resultSet 只是为了安全起见是很难争论的。您没有在此处指明您使用的是什么数据库或驱动程序，我不想对可能对某些实现无效的驱动程序进行硬编码。

Closing things in sequence does leave you open to problems where an exception can get thrown and cause some of the closes to be skipped. You're right to be concerned about that.

按顺序关闭事物确实会让您面临可能引发异常并导致跳过某些关闭的问题。你有这种担心是对的。

Be aware this is a toy example. Most real code uses a connection pool, where calling the close method doesn't actually close the connection, instead it returns the connection to the pool. So resources may not get closed once you use a pool. If you want to change this code to use a connection pool then you'll have to go back and close the statements at least.

请注意，这是一个玩具示例。大多数实际代码使用连接池，其中调用 close 方法实际上并没有关闭连接，而是将连接返回到池中。因此，一旦使用池，资源可能不会关闭。如果您想更改此代码以使用连接池，那么您至少必须返回并关闭这些语句。

Also if you're objecting to the verbosity of this, the answer to that is to hide the code in reusable utilities that use strategies, resultSet mappers, prepared statement setters, etc. This has all been done before, of course; you'll be on the road to reinventing Spring JDBC.

此外，如果您反对冗长，那么答案是将代码隐藏在使用策略、结果集映射器、准备好的语句设置器等的可重用实用程序中。您将踏上重塑 Spring JDBC 的道路。

Speaking of which: Spring JDBC closes everything explicitly (probably because it needs to work with as many drivers as possible, and doesn't want to cause problems due to some driver's not being well-behaved).

说起来：Spring JDBC 明确地关闭了一切（可能是因为它需要与尽可能多的驱动程序一起工作，并且不想因为某些驱动程序的行为不端而导致问题）。

回答by nbsp

You could make a util class to handle closing of these resources. i.e FYI i just ignored SQLExceptions from trying to close resources in the util class, but you could as verywell log or collect and throw them once you are done closing the resources in the collection depending on your needs

您可以创建一个 util 类来处理这些资源的关闭。即仅供参考，我只是在尝试关闭 util 类中的资源时忽略了 SQLExceptions，但是您也可以根据需要在完成关闭集合中的资源后记录或收集并抛出它们

public class DBUtil {
public static void closeConnections(Connection ...connections){
    if(connections != null ){
        for(Connection conn : connections){
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException ignored) {
                    //ignored
                }
            }
        }
    }
}

public static void closeResultSets(ResultSet ...resultSets){
    if(resultSets != null ){
        for(ResultSet rs: resultSets){
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException ignored) {
                    //ignored
                }
            }
        }
    }
}

public static void closeStatements(Statement ...statements){
    if(statements != null){
        for(Statement statement : statements){
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException ignored) {
                    //ignored
                }
            }
        }
    }
}

}

}

and then just call it from you method:

然后从你的方法中调用它：

    public void doQueries() throws MyException {
    Connection con = null;
    try {
        con = DriverManager.getConnection(dataSource);
        PreparedStatement s1 = null;
        PreparedStatement s2 = null;
        try {
            s1 = con.prepareStatement(updateSqlQuery);
            s2 = con.prepareStatement(selectSqlQuery);

            // Set the parameters of the PreparedStatements and maybe do other things
            s1.executeUpdate();
            ResultSet rs = null;
            try {
                rs = s2.executeQuery();
            } finally {
                DBUtil.closeResultSets(rs);
            }
        } finally {
            DBUtil.closeStatements(s2, s1);
        }

    } catch (SQLException e) {
        throw new MyException(e);
    } finally {
        DBUtil.closeConnections(con);
    }
}

回答by Armand

This is what I find to be the best solution for handling resources like JDBC. This method provides an immutable function, by leveraging final variables, and only declaring and assigning those variables if they are needed, it is very CPU efficient, and guarantees in all cases, that all resources that are assigned and opened are closed regardless of the state of exceptions. The technique you are using leaves gaps that can result in resource leaks if not carefully implemented to address all scenarios. This technique does not allow for a resource leak provided the pattern is always followed:
1) assign resource
2) try
3) use resource
4) finally close resource

我认为这是处理 JDBC 等资源的最佳解决方案。这种方法提供了一个不可变的功能，通过利用最终变量，并且只在需要时声明和分配这些变量，它非常高效 CPU，并保证在所有情况下，无论状态如何，所有分配和打开的资源都被关闭的例外。您使用的技术会留下一些空白，如果不小心实施以解决所有情况，可能会导致资源泄漏。如果始终遵循以下模式，则此技术不允许资源泄漏：
1) 分配资源
2) 尝试
3) 使用资源
4) 最后关闭资源

public void doQueries() throws MyException {
   try {
      final Connection con = DriverManager.getConnection(dataSource);
      try {
         final PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
         try {

            // Set the parameters of the PreparedStatements and maybe do other things

            s1.executeUpdate();

         } finally {
            try { s1.close(); } catch (SQLException e) {}
         }

         final PreparedStatement s2 = con.prepareStatement(selectSqlQuery);
         try {

            // Set the parameters of the PreparedStatements and maybe do other things

            final ResultSet rs = s2.executeQuery();
            try {

               // Do something with rs

            } finally {
               try { rs.close(); } catch (SQLException e) {}
            }
         } finally {
            try { s2.close(); } catch (SQLException e) {}
         }
      } finally {
         try { con.close(); } catch (SQLException e) {}
      }
   } catch (SQLException e) {
      throw new MyException(e);
   }
}

With Java 7, you can leverage the new try -with-resources to simplify this even more: The new try -with-resources follows the above logic flow, in that it will guarantee all resources include in the with resources block that are assigned get closed. any exception thrown in the with resources block will the thrown, but those assigned resources will still be closed. This code is much simplified and looks like this:

使用 Java 7，您可以利用新的 try -with-resources 进一步简化此操作：新的 try -with-resources 遵循上述逻辑流程，因为它将保证包含在 with resources 块中的所有资源都被分配了 get关闭。在 with resources 块中抛出的任何异常都将被抛出，但那些分配的资源仍将被关闭。这段代码非常简化，如下所示：

public void doQueries() throws MyException {
   try (
      final Connection con = DriverManager.getConnection(dataSource);
      final PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
      final PreparedStatement s2 = con.prepareStatement(selectSqlQuery);
      final ResultSet rs = s2.executeQuery()) {

      s1.executeUpdate();

         // Do something with rs

   } catch (SQLException e) {
      throw new MyException(e);
   }
}

[EDIT]: moved rs assignment into the resources block to show the simplest implementation. In practice, this simple solution does not really work, as this is not efficient. A connection shouldbe reused, since establishing the connection is a very costly operation. Additionally, this simple example does not assign query parameters to the prepared statement. Care should be taken to handle these scenarios, as the the resource block shouldonly include assignment statements. To depict this, I have also added another example

[编辑]：将 rs 分配移动到资源块中以显示最简单的实现。在实践中，这个简单的解决方案实际上并不奏效，因为这效率不高。连接应该被重用，因为建立连接是一个非常昂贵的操作。此外，这个简单的示例没有将查询参数分配给准备好的语句。应注意处理这些情况，因为资源块应仅包含赋值语句。为了描述这一点，我还添加了另一个示例

   public void doQueries() throws MyException {

      final String updateSqlQuery = "select @@servername";
      final String selecSqlQuery  = "select * from mytable where col1 = ? and col2 > ?";
      final Object[] queryParams  = {"somevalue", 1};

      try (final Connection con = DriverManager.getConnection(dataSource);
         final PreparedStatement s1 = newPreparedStatement(con, updateSqlQuery);
         final PreparedStatement s2 = newPreparedStatement(con, selectSqlQuery, queryParams);
         final ResultSet rs = s2.executeQuery()) {

         s1.executeUpdate();

         while (!rs.next()) {
            // do something with the db record.
         }
      } catch (SQLException e) {
         throw new MyException(e);
      }
   }

   private static PreparedStatement newPreparedStatement(Connection con, String sql, Object... args) throws SQLException
   {
      final PreparedStatement stmt = con.prepareStatement(sql);
      for (int i = 0; i < args.length; i++)
         stmt.setObject(i, args[i]);
      return stmt;
   }

回答by Basil Bourque

tl;dr

tl;博士

• In theory closing the statement closes the result set.
• In practice, some faulty JDBC driver implementations failed to do so, notoriously. Thus the advice from your instructor that she learned from the School Of Hard Knocks. Unless you are familiar with every implementation of every JDBC driverthat might be deployed for your app, use try-with-resourcesto auto-close every level of your JDBCwork such as statements and result sets.

• 理论上，关闭语句会关闭结果集。
• 在实践中，众所周知，一些有缺陷的 JDBC 驱动程序实现未能做到这一点。因此，您的导师的建议是她从 Hard Knocks 学校学到的。除非您熟悉可能为您的应用程序部署的每个JDBC 驱动程序的每个实现，否则请使用try-with-resources自动关闭JDBC工作的每个级别，例如语句和结果集。

Use try-with-resources syntax

使用 try-with-resources 语法

None of your code is fullyusing try-with-resources. In try-with-resources syntax, you declare and instantiate your Connection, PreparedStatement, and ResultSetin parentheses, before the braces. See Tutorial by Oracle.

您的代码都没有完全使用try-with-resources。在尝试与-资源的语法，声明和实例化Connection，PreparedStatement和ResultSet括号，前括号。请参阅Oracle 教程。

While your ResultSetis not being explicitly closed in your last code example, it shouldbe closed indirectly when its statement is closed. But as discussed below, it might notbe closed because of faulty JDBC driver.

虽然您ResultSet在上一个代码示例中没有被显式关闭，但它应该在其语句关闭时间接关闭。但如下所述，它可能不会因为 JDBC 驱动程序错误而关闭。

AutoCloseable

AutoCloseable

Any such objects implementing AutoCloseablewill automatically have their closemethod invoked. So no need for those finallyclauses.

任何此类实现的对象 AutoCloseable都会自动close调用它们的方法。所以不需要这些finally条款。

For the Humanities-majors reading this, yes, the Java team misspelled “closable”.

对于阅读本文的人文专业，是的，Java 团队拼错了“可关闭”。

How do you know which objects are auto-closable and which are not? Look at their class documentation to see if it declares AutoCloseableas a super-interface. Conversely, see the JavaDoc page for AutoCloseablefor a list of all the bundled sub-interfaces and implementing classes (dozens actually).

你怎么知道哪些对象是可自动关闭的，哪些不是？查看他们的类文档，看看它是否声明AutoCloseable为超级接口。相反，请参阅JavaDoc 页面以AutoCloseable获取所有捆绑子接口和实现类的列表（实际上有几十个）。

For example, for SQL work, we see that Connection, Statement, PreparedStatement, ResultSet, and RowSetare all auto-closable but DataSourceis not. This makes sense, as DataSourcestores data about potential resources (database connections) but is not itself a resource. A DataSourceis never “open” so no need to close.

例如，对于SQL工作中，我们看到Connection，Statement，PreparedStatement，ResultSet，和RowSet都自动关闭的，但DataSource并非如此。这是有道理的，因为DataSource存储有关潜在资源（数据库连接）的数据，但本身不是资源。ADataSource永远不会“打开”，因此无需关闭。

See Oracle Tutorial, The try-with-resources Statement.

请参阅 Oracle 教程try-with-resources 语句。

Code example

代码示例

Your last code example is getting close to good, but should have wrapped ResultSetin a try-with-resources statement to get automatically closed.

您的最后一个代码示例接近良好，但应该包含ResultSet在 try-with-resources 语句中以自动关闭。

To quote ResultSetJavaDoc:

引用ResultSetJavaDoc：

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

当生成它的 Statement 对象关闭、重新执行或用于从多个结果的序列中检索下一个结果时，ResultSet 对象将自动关闭。

As your teacher has been suggesting, there have been serious flaws in some JDBC drivers that failed to live up to the promise of the JDBC spec to close the ResultSetwhen its Statementor PreparedStatementis closed. So many programmers make a habit of closing each ResultSetobject explicitly.

当你的老师已经暗示，已经有一些JDBC驱动程序没有辜负JDBC规范的承诺，关闭严重缺陷ResultSet的时候Statement或PreparedStatement已关闭。许多程序员养成了ResultSet明确关闭每个对象的习惯。

This extra duty is easier now with the try-with-resources syntax. In real work you'll likely have a try-else around all your AutoCloseableobjects such as ResultSetanyways. So my own opinion is: Why not make it a try-with-resources + else? Does not hurt, makes your code more self-documenting about your intentions, and it mighthelp if your code ever encounters one of those faulty JDBC drivers. The only cost is a pair of parens, assuming you'd have a try-catch-else in place anyways.

现在使用 try-with-resources 语法可以更轻松地完成这项额外任务。在实际工作中，您可能会对所有AutoCloseable对象进行 try-else，例如ResultSet无论如何。所以我自己的意见是：为什么不让它成为一个try-with-resources + else呢？没有伤害，使您的代码更能自我记录您的意图，如果您的代码遇到那些有问题的 JDBC 驱动程序之一，它可能会有所帮助。唯一的成本是一对 parens，假设您无论如何都有一个 try-catch-else 。

As stated in the Oracle Tutorial, multiple AutoCloseableobjects declared together will be closed in reverse order, just as we would want.

正如Oracle 教程中所述，AutoCloseable一起声明的多个对象将以相反的顺序关闭，正如我们希望的那样。

Tip: The try-with-resources syntax allows an optional semicolon on the last declared resource item. I include the semicolon as a habit because it reads well to my eye, is consistent, and facilitates cut-and-paste editing. I include it on your PreparedStatement s2line.

提示：try-with-resources 语法允许在最后声明的资源项上使用可选的分号。我把分号作为一种习惯，因为它对我的眼睛来说很好读，是一致的，并且便于剪切和粘贴编辑。我把它包括在你的PreparedStatement s2行中。

public void doQueries() throws MyException{
    // First try-with-resources.
    try ( Connection con = DriverManager.getConnection( dataSource ) ;
          PreparedStatement s1 = con.prepareStatement( updateSqlQuery ) ;
          PreparedStatement s2 = con.prepareStatement( selectSqlQuery ) ;
    ) {

        … Set parameters of PreparedStatements, etc.

        s1.executeUpdate() ;

        // Second try-with-resources, nested within first.
        try (
            ResultSet rs = s2.executeQuery() ;
        ) {
            … process ResultSet
        } catch ( SQLException e2 ) {  
            … handle exception related to ResultSet.
        }

    } catch ( SQLException e ) {  
        … handle exception related to Connection or PreparedStatements.
    }
}

I suppose there is a more elegant syntax for this kind of work that might be invented in a future programming language. But for now, we have try-with-resources, and I do use it happily. While try-with-resources is not perfectly elegant, it is a big improvement over the older syntax.

我想这种工作有一种更优雅的语法，可能会在未来的编程语言中发明。但是现在，我们有 try-with-resources，我确实很乐意使用它。虽然 try-with-resources 并不完美，但它比旧语法有了很大的改进。

By the way, Oracle recommends using a DataSourceimplementation for getting connections rather than the DriverManagerapproach seen in your code. Using DataSourcethroughout your code makes it easier to switch drivers or switch to a connection pool. See if your JDBC driver provides an implementation of DataSource.

顺便说一下，Oracle 建议使用一种DataSource实现来获取连接，而不是DriverManager在您的代码中看到的方法。DataSource在整个代码中使用可以更轻松地切换驱动程序或切换到连接池。查看您的 JDBC 驱动程序是否提供了DataSource.

Update: Java 9

更新：Java 9

Now in Java 9 you can initialize the resources beforethe try-with-resources. See this article. This flexibility may be useful in some scenarios.

现在在 Java 9 中，您可以在 try-with-resources之前初始化资源。请参阅这篇文章。这种灵活性在某些情况下可能很有用。