java - 仅在 finally block 中关闭 JDBC 连接？

Question

为什么数据库连接经常在两个位置关闭，一个是在使用后直接关闭，另一个是在 finally block 中使用 null 检查以防止它们被关闭两次。使用 finally block 还不够吗？ finally block 将在每种情况下执行。

这里是Apache-Tomcat JNDI Datasource HOW-TO的官方例子.他们在那里指出，在任何情况下都必须关闭连接。我想知道为什么使用 finally block 是不够的，因为主 try {} block 末尾的关闭命令似乎是多余的。

    Connection conn = null;
    Statement stmt = null; // Or PreparedStatement if needed
    ResultSet rs = null;
    try
    {
        conn = ... get connection from connection pool ...
        stmt = conn.createStatement("select ...");
        rs = stmt.executeQuery();
        ... iterate through the result set ...
        rs.close ();
        rs = null;
        stmt.close ();
        stmt = null;
        conn.close (); // Return to connection pool
        conn = null; // Make sure we don't close it twice
    }
    catch (SQLException e)
    {
        ... deal with errors ...
    }
    finally
    {
        // Always make sure result sets and statements are closed,
        // and the connection is returned to the pool
        if (rs != null)
        {
            try
            {
                rs.close ();
            }
            catch (SQLException ignore)
            {
            }
            rs = null;
        }
        if (stmt != null)
        {
            try
            {
                stmt.close ();
            }
            catch (SQLException ignore)
            {
            }
            stmt = null;
        }
        if (conn != null)
        {
            try
            {
                conn.close ();
            }
            catch (SQLException ignore)
            {
            }
            conn = null;
        }
    }

我想写得更短:

    Connection conn = null;
    Statement stmt = null; // Or PreparedStatement if needed
    ResultSet rs = null;
    try
    {
        conn = ... get connection from connection pool ...
        stmt = conn.createStatement ("select ...");
        rs = stmt.executeQuery();
        ... iterate through the result set ...
    }
    catch (SQLException e)
    {
        // ... deal with errors ...
    }
    finally
    {
        // Always make sure result sets and statements are closed,
        // and the connection is returned to the pool
        try
        {
            if (rs != null)
                rs.close ();
            if (stmt != null)
                stmt.close ();
            if (conn != null)
                conn.close ();
        }
        catch (SQLException ignore)
        {
        }
    }

Answer 1

你问得好——“官方例子”我也看不懂。 finally block 肯定够了。

但是，您的代码有更严重的错误，即如果 rs.close() 会抛出异常，您将有 stmt 和 conn 泄漏，并且您d 也默默地忽略该异常。那是你不应该做的事情。从 Java 7 开始，使用 try-with-resources 构造是首选方法，但如果你不能去那里，至少要分别处理每个可能的异常(rs、stmt、conn)，这样它们就不会导致彼此泄漏。

例如 Apache commons DbUtils 有 closeQuietly()只是为了这个目的，因为它曾经是一个常见的场景。我个人会去像 Spring JDBCTemplate 这样的地方它在幕后做这类事情。

编辑:try-with-resources 由 Oracle 解释 here .简而言之，您可以这样做:

try (Connection conn = yourCodeToGetConnection();
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query)) {
    while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");

        System.out.println(coffeeName + ", " + supplierID + ", " + 
                               price + ", " + sales + ", " + total);
    }
} catch (SQLException ex) {
    // log, report or raise
}

其中 try 语句自动处理 conn、stmt 和 rs 在所有情况下按顺序关闭(与你陈述他们)。您仍然需要自己处理可能的异常。