我一直在体验(我认为)连接池的问题.
具体来说,我的日志显示消息:
org.apache.tomcat.dbcp.pool2.impl.DefaultPooledObject$AbandonedObjectCreatedException: Pooled object created [time] by the following code has not been returned to the pool
我已经检查了日志显示的堆栈跟踪中列出的方法,但找不到罪魁祸首(我总是在每个方法结束时关闭ResultSet,PreparedStatement和Connection).
我有一个执行两个查询的方法,也许我没有正确执行它.
它的布局如下:
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
PreparedStatement rowsPs = null;
ResultSet rs = null;
ResultSet rowsRs = null;
String query = "SELECT SQL_CALC_FOUND_ROWS ...";
String totalRowsQuery = "SELECT FOUND_ROWS() AS RowCount";
try {
ps = connection.prepareStatement(query);
[set ps params]
rs = ps.executeQuery();
[process rs]
rowsPs = connection.prepareStatement(totalRowsQuery);
rowsRs = rowsPs.executeQuery();
[process rowsRs]
} catch (SQLException e) {
[handle e]
} finally {
DBUtil.closeResultSet(rs);
[close rowsRs]
[close ps]
[close rowsPs]
[close connection]
}
其中DBUtils方法的示例是:
public static void closeResultSet(ResultSet rs)
{
try
{
if (rs != null)
rs.close();
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
这种方法的总体布局是否正常?我应该以不同方式处理连接吗?或者是导致错误记录的其他方法?
谢谢.
附加信息
我也得到一个SQLException:
java.sql.SQLException: Connection com.mysql.jdbc.JDBC4Connection@[some number] is closed
在该行:rowsPs = connection.prepareStatement(totalRowsQuery);
意思是在之前的某个地方,连接已关闭.
我没有在任何地方明确关闭连接.
是否有可能某个其他数据访问方法在某种程度上关闭此方法中的连接? (pool.getConnection()调用dataSource.getConnection())
更新:
我已尝试按建议使用try-with-resources,但问题仍然存在.
上面第一个代码段中引用的ConnectionPool类:
public class ConnectionPool
{
private static ConnectionPool pool = null;
private static DataSource dataSource = null;
public synchronized static ConnectionPool getInstance()
{
if ( pool == null ) {
pool = new ConnectionPool();
}
return pool;
}
private ConnectionPool()
{
try {
InitialContext ic = new InitialContext();
dataSource = (DataSource)
ic.lookup([jdbc/dbName]);
}
catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection()
{
try {
return dataSource.getConnection();
}
catch (SQLException sqle) {
sqle.printStackTrace();
return null;
}
}
public void freeConnection(Connection c)
{
try {
c.close();
}
catch (SQLException sqle) {
sqle.printStackTrace();
}
}
}
更多来源:
我的池资源元素:
logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000"
removeAbandonedOnBorrow="true"
removeAbandonedTimeout="60" type="javax.sql.DataSource"
testWhileIdle="true" testOnBorrow="true"
validationQuery="SELECT 1 AS dbcp_connection_test"/>
更新:
我打开了慢查询日志,但是,尽管再次抛出异常,慢查询日志不会记录任何内容(查询时间不超过10秒).
所以它似乎并非查询花费的时间超过60秒.
仍然不确定是什么导致了这一点.