欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

怎样实现关闭connection时自动关闭Statement和ResultSet

程序员文章站 2022-07-13 13:38:34
...

关闭数据连接时, 一定也要关闭Statement和ResultSet, 不然在并发量较大的时候可能导致内存泄漏. (如果是用tomcat自带的数据源实现, 则可以通过添加interceptor实现自动关闭statement.)


但是, 关闭Statement和ResultSet是乏味的工作.


例如下面的代码:

    @Test
    public void testConnection() throw Exception{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DsUtil.getConnection();
            ps = conn.prepareStatement("select now()");
            rs = ps.executeQuery();
            //do something
        }finally {
            //DsUtil是一个工具类, 就不贴代码了, 你懂的.
            DsUtil.close(rs);
            DsUtil.close(ps);
            DsUtil.close(conn);
        }
    }

上面的代码只有一个Statement和一个ResultSet, 关闭一下也不算太麻烦, 所以你可能觉得笔者没有必要写这篇文章.


但是, 如果有多个Statement和ResultSet呢?


考虑如下代码:

    @Test
    public void testConnection() throws Exception {
        Connection conn = null;
        PreparedStatement psFoo = null;
        ResultSet rsFoo = null;
        PreparedStatement psBar = null;
        ResultSet rsBar = null;
        try {
            conn = DsUtil.getConnection();
            psFoo = conn.prepareStatement("....");
            rsFoo = psFoo.executeQuery();
            psBar = conn.prepareStatement("....");
            rsBar = psBar.executeQuery();
            //do something
        } finally {
            DsUtil.close(rsFoo);
            DsUtil.close(psFoo);
            DsUtil.close(rsBar);
            DsUtil.close(psBar);
            DsUtil.close(conn);
        }
    }

上面的代码有两个statement和result, 关起来就不那么令人愉快了.


大多数程序员肯定是不喜欢关闭多个statement和result的. 我见到过偷懒的程序用这种写法来规避关闭多个statement的问题.

    @Test
    public void testConnectionNotGood() throws Exception {
        Connection conn = null;
        PreparedStatement psFoo = null;
        ResultSet rsFoo = null;
        try {
            conn = DsUtil.getConnection();
            psFoo = conn.prepareStatement("....");
            rsFoo = psFoo.executeQuery();
            //do something            
            psFoo = conn.prepareStatement("....");
            rsFoo = psFoo.executeQuery();
            //do something
        } finally {
            DsUtil.close(rsFoo);
            DsUtil.close(psFoo);
            DsUtil.close(conn);
        }
    }

上面这断代码的不妥之处, 是Statement和ResultSet被重用了. 实际上创建了两个Statement和Result, 但最后只关闭了一个. 这样显然是不对的, 属于鸵鸟政策, 没解决实际问题.


那么, 有没有办法实现一个自定义的Connection, 使得程序员不需要手动关闭Statement和Result, 并支持Statement/PrepareStatement/CallableStatement呢?

 

==================分割线========================

 简单地说:

    我们希望connection持有statement的软引用, 而statement又持有resultset的软引用, 并分别重写connection和statement的close方法, 在关闭之前先关闭软引用中的对象.

详细地说:
    1. 创建一个ResultSetStatementAwareConnection.

        该自定义Connection会记住所有的Statement/PreparedStatement/CallableStatement实例.
    2. 创建一个ResultSetAwareStatement, 记住所有Statement.
    3. 创建一个ResultSetAwarePreparedStatement, 记住所有的PreparedStatement.

    4. 创建一个ResultSetAwareCallableStatement, 记住所有的CallableStatement.

 

先说ResultSetStatementAwareConnection的实现.

class ResultSetStatementAwareConnection implements Connection {
    private Map> openStatements = new ConcurrentHashMap>(3);
    protected Connection underlyingConnection;

    public ResultSetStatementAwareConnection(Connection conn) {
        this.underlyingConnection = conn;
    }

    private void addToOpenStatement(Statement statement) {
        openStatements.put(DsUtil.getIdentityHexString(statement), new SoftReference(statement));
    }

    public void close() throws SQLException {
        try {
            closeOpenStatements();
        } finally {
            closeUnderlyingConnection();
        }
    }

    private void closeOpenStatements() {
        for (Map.Entry> entry : openStatements.entrySet()) {
            DsUtil.close(entry.getValue().get());
        }
        openStatements.clear();
        openStatements = null;
    }

    protected void closeUnderlyingConnection() {
        DsUtil.close(underlyingConnection);
        underlyingConnection = null;
    }

    @Override
    public Statement createStatement() throws SQLException {
        ResultSetAwareStatement statement = ResultSetAwareStatement.decorate(underlyingConnection.createStatement());
        statement.setConnection(this);
        addToOpenStatement(statement);
        return statement;
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        ResultSetAwarePreparedStatement statement = ResultSetAwarePreparedStatement.decorate(underlyingConnection.prepareStatement(sql));
        statement.setConnection(this);
        addToOpenStatement(statement);
        return statement;
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        ResultSetAwareCallableStatement statement = ResultSetAwareCallableStatement.decorate(underlyingConnection.prepareCall(sql));
        statement.setConnection(this);
        addToOpenStatement(statement);
        return statement;
    }

    @Override
    public String getCatalog() throws SQLException {
        return underlyingConnection.getCatalog();
    }

    //更多代码见附件
}

通过openStatements持有所有statement的软引用, 并且close方法中会先调用closeOpenStatements把软引用持有的statement全部关闭, 然后再通过closeUnderlyingConnection去真正关闭连接.


到现在为止, 就只需要关闭数据库连接, 不需要显式关闭statement了.


类似地, statement也可以通过这种模式来关闭resultset. 以PreparedStatement为例.

class ResultSetAwarePreparedStatement implements PreparedStatement {
    private Map> openResultSets = new ConcurrentHashMap>(3);
    private ResultSetStatementAwareConnection connection;
    private PreparedStatement underlyingPreparedStatement;

    static ResultSetAwarePreparedStatement decorate(PreparedStatement statement) {
        ResultSetAwarePreparedStatement instance = new ResultSetAwarePreparedStatement();
        instance.underlyingPreparedStatement = statement;
        return instance;
    }

    private void addToOpenResultSet(ResultSet resultSet) {
        openResultSets.put(DsUtil.getIdentityHexString(resultSet), new SoftReference(resultSet));
    }

    public void close() throws SQLException {
        try {
            closeOpenResultSets();
        } finally {
            closeUnderlyingStatement();
        }
    }

    private void closeOpenResultSets() {
        for (Map.Entry> entry : openResultSets.entrySet()) {
            DsUtil.close(entry.getValue().get());
        }
        openResultSets.clear();
        openResultSets = null;
    }

    private void closeUnderlyingStatement() {
        DsUtil.close(underlyingPreparedStatement);
        connection = null;
    }
    //更多代码见附件
}

通过openResultSets持有resultset的软引用, 并且close方法中会先调用closeOpenResultSets把软引用持有的resultset全部关闭, 然后再通过closeUnderlyingStatement去真正关闭statement.


到这里, 所有的事情就完成了. 下面举个栗子.

    @Test
    public void testConnectionNotGood() throws Exception {
        Connection conn = null;
        try {
            conn = new ResultSetStatementAwareConnection(DsUtil.getConnection());
            PreparedStatement psFoo = conn.prepareStatement("....");
            ResultSet rsFoo = psFoo.executeQuery();
            //do something
            psFoo = conn.prepareStatement("....");
            rsFoo = psFoo.executeQuery();
            //do something
            psFoo = conn.prepareStatement("....");
            rsFoo = psFoo.executeQuery();
            //do something
        } finally {
            DsUtil.close(conn);
        }
    }

我们看到PreparedStatement和ResultSet一共使用了三次, 创建了三个PreparedStatement和三个ResultSet. 但是在finally块中只显式关闭了Connection, 并没有显式关闭PreparedStatement和ResultSet.


不过放心, 虽然没有显式关闭, 但其实三个PrepareStatement和ResultSet都会被自动关闭.

本文完.

(在附件中, DsUtill并没有提供getConnection方法, 需要自行编写.)

上一篇: git 常见回退命令

下一篇: git回滚