数据库连接泄漏

Question

最近，我不得不测量我们软件的一些SQL请求所花费的时间。为此，我决定采用天真的方法，并在查询中包含对System.nanoTime()的调用。在这样做时，我发现了一个类(SqlSuggest)，它在4个非常相似的方法中包含了4个非常相似的查询。我认为重构和重组公共部分是个好主意。

这造成了连接泄漏，连接不再关闭。我收回重构，但我仍然想知道我做错了什么。

SqlSuggest类的第一个版本有4个方法( getSuggestListByItems、getDisplayValueByItems、getSuggestListByListID和getDisplayValueByListeID )。这些方法中的每一个都通过另一个类: DBAccess打开并关闭连接、语句和DBAccess(在最后块中)。

该类的第二个版本有相同的4个方法，除了打开和关闭连接之外，它们调用两个方法中的一个取决于它们是需要一个结果还是一个列表。

这两个方法(executeQueryGetString和executeQueryGetListOfStringArray)分别声明连接、语句和ResultSet，然后调用一个方法:打开连接的executeQuery，创建语句并返回ResultSet。

然后，第二层方法从ResultSet中提取数据并关闭所有数据。

我猜我错了，认为在方法中声明连接、语句和ResultSet将允许我从相同的方法关闭它们。

下面是旧类(简化)：

public class SqlSuggest {

    private final static Logger LOGGER = LogManager.getLogger();

    public List<String[]> getSuggestListByItems(String codeSite) {
        List<String[]> suggestList = new ArrayList<>();
        String query = "SELECT iDcode, designation FROM Mytable WHERE codeSite = " + codeSite; // Simplified query building
        DBAccess accesBD = new DBAccess();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = accesBD.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                suggestList.add(new String[]{rs.getString(1), rs.getString(2)});
            }
        } catch (NamingException | SQLException ex) {
            LOGGER.error("", ex);
        } finally {
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }
        return suggestList;
    }

    public String getDisplayValueByItems(String table, String codeSite) {
        String displayValue = null;
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building
        DBAccess accesBD = new DBAccess();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = accesBD.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                displayValue = rs.getString(1);
            }
        } catch (NamingException | SQLException ex) {
            LOGGER.error("", ex);

        } finally {
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }

        return displayValue;
    }

    public List<String[]> getSuggestListByListID(String listeID, String table, String codeSite) {
        List<String[]> suggestList = new ArrayList<>();
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building involving listID
        DBAccess accesBD = new DBAccess();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = accesBD.getConnection();

            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                suggestList.add(new String[]{rs.getString(1), rs.getString(2)});
            }
        } catch (NamingException | SQLException ex) {
            LOGGER.error("", ex);
        } finally {
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }
        return suggestList;
    }

    public String getDisplayValueByListeID(String listeID, String table, String codeSite) {
        String displayValue = null;        
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building involving listID
        DBAccess accesBD = new DBAccess();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = accesBD.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                displayValue = rs.getString(2);
            }
        } catch (NamingException | SQLException ex) {
            LOGGER.error("", ex);
        } finally {
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }


        return displayValue;
    }
}

下面是新的类(有漏洞的类)：

public class SqlSuggest {

    private static final Logger LOGGER = LogManager.getLogger();

    public List<String[]> getSuggestListByItems(String codeSite) {
        List<String[]> suggestList;
        String query = "SELECT iDcode, designation FROM Mytable WHERE codeSite = " + codeSite; // Simplified query building
        suggestList = executeQueryGetListOfStringArray(query);
        return suggestList;
    }

    public String getDisplayValueByItems(String table, String codeSite) {
        String displayValue = null;
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building
        int columnToFetch = 1;
        displayValue = executeQueryGetString(query, columnToFetch);
        return displayValue;
    }

    public List<String[]> getSuggestListByListID(String listeID, String table, String codeSite) {
        List<String[]> suggestList = new ArrayList<>();
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building involving listID
        suggestList = executeQueryGetListOfStringArray(query);
        return suggestList;
    }

    public String getDisplayValueByListeID(String listeID, String table, String codeSite) {
        String displayValue = null;
        String query = "SELECT iDcode, designation FROM " + table + " WHERE codeSite = " + codeSite; // Different query building involving listID
        int columnToFetch = 2;
        displayValue = executeQueryGetString(query, columnToFetch);
        return displayValue;
    }

    private String executeQueryGetString(String query, int columnToFetch){
        String result = null;
        Statement stmt = null;
        Connection conn = null;
        ResultSet rs = executeQuery(query, stmt, conn);
        try{
            if (rs.next()) {
                result = rs.getString(columnToFetch);
            }
        } catch (SQLException ex) {
            LOGGER.error("", ex);
        } finally {
            DBAccess accesBD = new DBAccess();
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }
        return result;
    }

    private List<String[]> executeQueryGetListOfStringArray(String query){
        List<String[]> result = new ArrayList<>();
        Statement stmt = null;
        Connection conn = null;
        ResultSet rs = executeQuery(query, stmt, conn);
        try{
            while (rs.next()) {
                result.add(new String[]{rs.getString(1), rs.getString(2)});
            }
        } catch (SQLException ex) {
            LOGGER.error("", ex);
        } finally {
            DBAccess accesBD = new DBAccess();
            accesBD.closeResultSet(rs);
            accesBD.closeStatement(stmt);
            accesBD.closeConnection(conn);
        }
        return result;
    }

    private ResultSet executeQuery(String query, Statement stmt, Connection conn){
        DBAccess accesBD = new DBAccess();
        ResultSet rs = null;
        try {
            conn = accesBD.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);   
        } catch (NamingException | SQLException ex) {
            LOGGER.error("", ex);
        }
        return rs;
    }
}

下面是实际打开和关闭连接的类(没有以任何方式更改)：

    public class DBAccess {

    private final static Logger LOGGER = LogManager.getLogger();

    public void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                LOGGER.error("", e);
            }
        }
    }

    public void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                LOGGER.error("", e);
            }
        }
    }

    public void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                LOGGER.error("", e);
            }
        }
    }

    public Connection getConnection() throws SQLException, NamingException {
        Connection cnx = null;
        PropertiesManager manager = PropertiesDelegate.getPropertiesManager();
        String jndi = manager.getProperty("datasource.name", "QuartisWeb-PU");
        Context ctx = null;
        DataSource dataSource = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup(jndi);
        } catch (NamingException ex) {
            try {
                dataSource = (DataSource) ctx.lookup("java:comp/env/" + jndi);
            } catch (NamingException ex1) {
                LOGGER.error("", ex1);
            }
        }
        if (dataSource != null) {
            cnx = dataSource.getConnection();
        }
        return cnx;
    }

}

请不要理会这些参数在查询中没有正确设置的事实，我知道应该这样做。

Answer 1

当您从executeQueryGetString()调用executeQuery()时，conn和stmt的值将在executeQuery()中更改，但返回时null将保留在executeQueryGetString()中。因此，在finally of executeQueryGetString()，对accesBD.closeConnection(conn)的调用实际上是在执行accesBD.closeConnection(null)