java Java中如何正确清理JDBC资源？

How to properly clean up JDBC resources in Java?

提问by Alexander Rosemann

What is considered best practices when cleaning up JDBC resources and why? I kept the example short, thus just the cleaning up of the ResultSet.

清理 JDBC 资源时哪些被认为是最佳实践？为什么？我保持示例简短，因此只是清理 ResultSet。

finally
{
  if(rs != null)
    try{ rs.close(); } catch(SQLException ignored) {}
}

versus

相对

finally
{
  try{ rs.close(); } catch(Exception ignored) {}
}

Personally I favour the second option since it is a bit shorter. Any input on this is much appreciated.

我个人更喜欢第二种选择，因为它有点短。对此的任何投入都非常感谢。

回答by André

Nowadays JDK 7 gives you the easiest option to clean up resources:

现在 JDK 7 为您提供了最简单的资源清理选项：

String query = "select COF_NAME, PRICE from COFFEES";
try (Statement stmt = con.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        float price = rs.getFloat("PRICE");
        System.out.println(coffeeName + ", "  + price);
    }
}

The try statement ensures that each resource is closed at the end of the statement. See http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

try 语句确保每个资源在语句结束时关闭。请参阅http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

回答by Adam Paynter

As others have pointed out, JDBC resources (statements, result sets, etc...) are rarely null. If they are, you have biggerissues on your hands than NullPointerExceptions. In that regard, the NullPointerExceptions will help alert you to severe problems with your JDBC driver. The typical checking for nullbefore calling close()would silently hide the problem if your JDBC driver was, in fact, providing you with nullreferences.

正如其他人指出的那样，JDBC 资源（语句、结果集等）很少null。如果是，那么您手上的问题比s更大NullPointerException。在这方面，NullPointerExceptions 将有助于提醒您注意 JDBC 驱动程序的严重问题。如果您的 JDBC 驱动程序实际上为您提供了引用，则null调用前的典型检查close()会默默地隐藏问题null。

As well, not all JDBC drivers follow the specification precisely. For example, some drivers will not automatically close a ResultSetwhen it's associated Statementis closed. Therefore, you have to ensure that youexplicitly close both the ResultSetandits Statement(sigh).

同样，并非所有 JDBC 驱动程序都严格遵循规范。例如，某些驱动程序ResultSet在关联Statement关闭时不会自动关闭。因此，你必须确保你明确地密切双方ResultSet和其Statement（叹气）。

In practice, I have found this technique useful (although its not the prettiest):

在实践中，我发现这种技术很有用（虽然它不是最漂亮的）：

PreparedStatement statement = connection.prepareStatement("...");
try {
    ResultSet results = statement.executeQuery();
    try {
        while (results.next()) {
            // ...
        }
    } finally {
        results.close();
    }
} finally {
    statement.close();
}

This technique guarantees that every close()statement is executed, starting with the ResultSetand working its way outward. NullPointerExceptions are still thrown should the driver provide you with nullreferences, but I allow this for the reasons explained at the beginning. SQLExceptions are still thrown if any of the close()statements fail (I consider this a good thing - I want to know if something is going wrong).

这种技术保证每条close()语句都被执行，从 开始ResultSet并向外工作。NullPointerException如果驱动程序为您提供null参考，s 仍然会被抛出，但出于开头解释的原因，我允许这样做。SQLException如果任何close()语句失败，s 仍然会被抛出（我认为这是一件好事 - 我想知道是否有问题）。

回答by Andreas Dolk

I see no problem with your second (uncommon) version.

我认为您的第二个（不常见）版本没有问题。

• usually, rs will not be null, so an NPE will occur in rare cases. So I see no performance problem here.
• both version behave exactly the same in case of rs = null

• 通常， rs 不会null，因此在极少数情况下会发生 NPE。所以我在这里看不到性能问题。
• 在以下情况下，两个版本的行为完全相同 rs = null

The only disadvantage - if we have more then one resource to close, then we'd have to add one try/catch for each resource, if we want to close as many resources as possible. Otherwise, we'd enter the catch clause with the first nulland that could cause undiscored leaks.

唯一的缺点 - 如果我们有多个资源要关闭，那么我们必须为每个资源添加一个 try/catch，如果我们想关闭尽可能多的资源。否则，我们将使用第一个进入 catch 子句，null这可能导致未发现的泄漏。

So it would look like that:

所以它看起来像这样：

finally {
   try{rs.close();  }catch(Exception ignored){}
   try{stmt.close();}catch(Exception ignored){}
   try{conn.close();}catch(Exception ignored){}
}

... which is still readable and understandable. But, according to never change a common pattern- I'd stick to the old-fashioned way of testing nullfirst and catching SQLExceptionwhile closing.

...这仍然是可读和可以理解的。但是，根据永远不会改变一个共同的模式- 我会坚持老式的方法，null先测试，然后SQLException在关闭时捕捉。

回答by dogbane

I tend to use the following approach. I think it is good to check for nullbecause it shows your intent i.e. that you do realise that these objects could be null in rare cases. (A null check is also faster than the creation of a NullPointerException.) I also think it is good to log the exceptions, instead of swallowing them. In the cases where closefails, I want to know about it and have it in my log files.

我倾向于使用以下方法。我认为最好检查一下，null因为它显示了您的意图，即您确实意识到这些对象在极少数情况下可能为空。（空检查也比创建一个更快NullPointerException。）我也认为记录异常而不是吞下它们是好的。在close失败的情况下，我想了解它并将其保存在我的日志文件中。

finally {            
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                 LOG.warn("Failed to close rs", e);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) { 
                 LOG.warn("Failed to close st", e);     
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                 LOG.warn("Failed to close conn", e);
                }
            }
        }

If you are going to be doing this frequently, instead of copying and pasting this code over and over again, create a utility class with static methods to close the ResultSet, Statement and Connection.

如果您要经常这样做，不要一遍又一遍地复制和粘贴此代码，而是创建一个带有静态方法的实用程序类来关闭 ResultSet、Statement 和 Connection。

With DBUtilsyou can perform this cleanup quite concisely as follows:

使用DBUtils，您可以非常简洁地执行此清理，如下所示：

 finally {            
            DBUtils.closeQuietly(rs);
            DBUtils.closeQuietly(st);
            DBUtils.closeQuietly(conn);            
        }

回答by Vickie

public static void close(Statement... statements) {
        for (Statement stmt : statements) {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se) {
            }// nothing we can do
        }
    }

    public static void close(Connection conn) {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException se) {
        }// nothing we can do
    }
public static void close(ResultSet rs) {
        try {
            if (rs != null) 
                rs.close();
        } catch (SQLException se) {
        }// nothing we can do
}

回答by André

This is my approach for JDK 6. If you have JDK 7+ you better use the approach I describe here https://stackoverflow.com/a/9200053/259237

这是我对 JDK 6 的方法。如果你有 JDK 7+，你最好使用我在这里描述的方法https://stackoverflow.com/a/9200053/259237

private void querySomething() {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        // get connection
        // prepare statement
        // execute query
        // and so on
    } catch (SQLException e) {
        throw new MyException("Error while talking to database", e);
    } finally {
        close(connection, statement, rs);
    }
}

// useful because you probably have more than one method interacting with database
public static void close (Connection connection, Statement statement, ResultSet rs) {
    if (rs != null) {
        try { rs.close(); } catch (Exception e) { _logger.warning(e.toString()); }
    }
    if (statement != null) {
        try { statement.close(); } catch (Exception e) { _logger.warning(e.toString()); }
    }
    if (connection != null) {
        try { connection.close(); } catch (Exception e) { _logger.warning(e.toString()); }
    }
}

• It's short.
• It defines a close method that can be statically imported.
• It avoids empty catch blocks.
• It handles any SQLException that may occur (even in getConnection or close methods).
• It's null-safe.

• 它很短。
• 它定义了一个可以静态导入的 close 方法。
• 它避免了空的 catch 块。
• 它处理可能发生的任何 SQLException（即使在 getConnection 或 close 方法中）。
• 它是空安全的。

回答by André

protected void closeAll(){
        closeResultSet();
        closeStatement();
        closeConnection();
    }   

protected void closeConnection(){
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                /*Logger*/
            }
            connection = null;
        }
    }


    protected void closeStatement() {
        if (stmt != null) {
            try {
                ocstmt.close();
            } catch (SQLException e) {
                /*Logger*/
            }
            ocstmt = null;
        }
    }


    protected void closeResultSet() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                /*Logger*/
            }
            rs = null;
        }
    }

回答by Fortyrunner

If you are writing a long running application you should consider connection pooling.

如果您正在编写一个长时间运行的应用程序，您应该考虑连接池。

The Apache DBCP project does a lot of this work for you. You could also look at something like Spring JDBC or Hibernate as well.

Apache DBCP 项目为您做了很多这样的工作。您还可以查看 Spring JDBC 或 Hibernate 之类的东西。

The Spring stuff uses object pooling and adds some really nice methods for abstracting away JDBC nastiness.

Spring 的东西使用对象池并添加了一些非常好的方法来抽象掉 JDBC 肮脏的东西。

回答by khachik

ResultSet rs = //initialize here
try {
  // do stuff here
} finally {
  try { rs.close(); }
  catch(SQLException ignored) {}
}