java - "Too Many Connections"错误，即使 dataSource.getActive() 返回 0？

Question

我非常确定我正在关闭 finally block 中的 con、rs、stmt。但似乎在尝试执行更新的第 4 次尝试时出现“太多连接”错误。

public boolean setUserSettings(UserSettings userSettings)
{
    ResultSet rs = null;
    Connection con = null;
    PreparedStatement stmt = null;
    String _query;

    try
    {
        System.out.println("total current connections: " + dataSource.getActive());
        con = dataSource.getConnection();
        _query = Util.readIn("/sql/select/selectUserSettingsCount.sql", MySqlPersistence.class);
        stmt = con.prepareStatement(_query);
        stmt.setString(1, userSettings.user);

        rs = stmt.executeQuery();
        boolean found = rs.next() && rs.getInt(1) == 1;

        if (!found)
        {
            _query = Util.readIn("/sql/insert/setUserSettings.sql", MySqlPersistence.class);
            stmt = con.prepareStatement(_query);
            stmt.setString(1, userSettings.user);
            stmt.setBoolean(2, userSettings.isActive);
            stmt.setString(3, userSettings.phone);
            stmt.setString(4, userSettings.phone2);
            stmt.setString(5, userSettings.phone3);
            stmt.setString(6, userSettings.contact_strategy);
        }
        else
        {               
            _query = Util.readIn("/sql/update/updateUserSettings.sql", MySqlPersistence.class);
            stmt = con.prepareStatement(_query);
            stmt.setString(1, userSettings.phone);
            stmt.setString(2, userSettings.phone2);
            stmt.setString(3, userSettings.phone3);
            stmt.setString(4, userSettings.user);
        }

        int success = stmt.executeUpdate();
        if (found && success == 1)
        {
            return true;
        }
        else
        {                
            return false;
        }
    }
    catch (Exception e)
    {            
        e.printStackTrace();
        return false;
    }
    finally
    {
        try { if (rs != null) System.out.println("closing rs"); rs.close(); } catch (SQLException e) { }
        try { if (stmt != null) System.out.println("closing stmt"); stmt.close(); } catch (SQLException e) { }
        try { if (con != null) System.out.println("closing con"); con.close(); } catch (SQLException e) { }
    }
}

Answer 1

您为每个方法调用创建了 2 个语句，但只关闭了 1 个。在您进入第一个大的 if block 之前，您应该关闭现有的语句。

更新:

经过进一步思考，我注意到您的代码中存在另一个错误。这种模式被打破了:

if (con != null) System.out.println("closing con"); con.close();

事实上，这是一个很好的例子，说明了为什么您应该始终在 if block 中使用大括号。重新格式化，这段代码是:

if (con != null) 
    System.out.println("closing con"); 
con.close();

然而，您真正想要的是:

if (con != null) {
    System.out.println("closing con"); 
    con.close();
}