怎样实现关闭connection时自动关闭Statement和ResultSet
关闭数据连接时, 一定也要关闭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回滚