小贝子编程

尽管存在关闭语句，但数据库连接未关闭



我在关闭数据库连接时遇到了一个问题。我得到以下错误

=================================================

267137 [http-8080-Processor23] FATAL com.abc.r2.dwrclasses.DBUtilities  - Failed to get a connection from DataSource JNDI/TPXSRUDB
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at com.abc.r2.dwrclasses.DBUtilities.getNativeConnection(DBUtilities.java:110)
at com.abc.r2.dwrclasses.ProcedureCaller.callProcedure(ProcedureCaller.java:81)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:740)
at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:744)
at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:593)
at org.directwebremoting.dwrp.BaseCallHandler.handle(BaseCallHandler.java:90)
at org.directwebremoting.servlet.UrlProcessor.handle(UrlProcessor.java:120)
at org.directwebremoting.servlet.DwrServlet.doPost(DwrServlet.java:141)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.oracle.determinations.web.platform.util.CharsetFilter.doFilter(CharsetFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:756)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
... 42 more

====================================================

我已经编写了一个DBUtilities.java文件，该文件使用JNDI获取连接并返回连接对象。然后，这个连接对象在其他java类中用于查询数据库(调用过程)，完成后关闭。然而，在一定数量的成功连接之后，它给出了上述错误。下面是我在课堂上写的代码。谁能告诉我代码出了什么问题，或者数据库有问题吗。我正在使用oracle10g。

================================================

package com.abc.r2.dwrclasses;

/*
* Utilites.java
*
* Created on 08 July 2004, 11:55
*/

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import java.util.*;
import org.apache.log4j.Logger;
import org.apache.log4j.NDC;
import org.apache.commons.dbutils.DbUtils;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;

/**
* Abstract class containing useful database methods.
* @author Rajan Panchal
*/
public abstract class DBUtilities {
private static final Logger log = Logger.getLogger(DBUtilities.class);
private static Context initCtx; // Initial context
private static Context envCtx;  // Environment context
static ResourceBundle rb;
static String appServer;
DataSource ds;
// Only get the JNDI context once, it's expensive.
/* Get the JNDI context depending upon the configuration stored in appconfiguration.properties file in configuration folder */
static {
try {
rb = ResourceBundle.getBundle("configuration.queries");
appServer = rb.getString("APP_SERVER");
log.debug("Application Server: "+appServer);
if(appServer.equalsIgnoreCase("TOMCAT")){        
initCtx = new InitialContext();        
envCtx  = (Context)initCtx.lookup("java:/comp/env");
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
Hashtable env = new Hashtable();
env.put("java.naming.factory.initial","com.ibm.websphere.naming.WsnInitialContextFactory");
System.out.println("INITIAL_CONTEXT_FACTORY: com.ibm.websphere.naming.WsnInitialContextFactory");
initCtx = new InitialContext(env);
}
} catch (javax.naming.NamingException e) {
log.fatal("Failed to get JNDI context on "+appServer, e);
throw new RuntimeException(e);
}
}
/**
*  Get a JDBC connection
* @param jndiName the JNDI name of the datasource
* @return a Connection to the datasource
*/
public static Connection getConnection(String jndiName) {
int maxtry = 10;
int DBConnectTry = 0;
while( DBConnectTry++ < maxtry){
log.info("trying to connect:"+DBConnectTry);
try {
if(appServer.equalsIgnoreCase("TOMCAT")){               
DataSource ds = (DataSource)envCtx.lookup(jndiName);
log.debug("Returning connection object for "+appServer);
return ds.getConnection();
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
DataSource ds = (DataSource)initCtx.lookup(jndiName);
log.debug("Returning connection object for "+appServer);
return ds.getConnection();
}
} catch (javax.naming.NamingException e) {
log.fatal("JNDI lookup  failed for DataSource "+jndiName, e);
throw new RuntimeException(e);
} catch (SQLException e) {
log.fatal("Failed to get a connection from DataSource "+jndiName, e);
log.info("Wait for 1 sec...");
Long WaitTime = System.currentTimeMillis()+1000L;
while(System.currentTimeMillis()<WaitTime){
}
log.info("Wait Over retrying...");            
}
}
return null;
}
/* This method returns the native connection for executing the pl/sql  procedure with array descriptors.*/    
public static Connection getNativeConnection(String jndiName) {
int maxtry = 10;
int DBConnectTry = 0;
Connection conn;
while( DBConnectTry++ < maxtry){
log.info("trying to connect:"+DBConnectTry);
try {
if(appServer.equalsIgnoreCase("TOMCAT")){
DataSource ds = (DataSource)envCtx.lookup(jndiName);
conn = ds.getConnection();
if (conn instanceof org.apache.commons.dbcp.DelegatingConnection)      { 
log.debug("detected apache commons dbcp datasource");          
conn = ((org.apache.commons.dbcp.DelegatingConnection) conn).getInnermostDelegate();      
}
return conn;
}else if(appServer.equalsIgnoreCase("WEBSPHERE")){
DataSource ds = (DataSource)initCtx.lookup(jndiName);
conn = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)ds.getConnection());                   
return conn;
}
} catch (javax.naming.NamingException e) {
log.fatal("JNDI lookup  failed for DataSource "+jndiName, e);
throw new RuntimeException(e);
} catch (SQLException e) {
log.fatal("Failed to get a connection from DataSource "+jndiName, e);
log.info("Wait for 1 sec...");
Long WaitTime = System.currentTimeMillis()+1000L;
while(System.currentTimeMillis()<WaitTime){
}
log.info("Wait Over retrying...");            
}
}
return null;
}
/**
* Utility method to close a connection, and/or a statement, and/or a resultset
* @param con Connection, or null
* @param stmt Statement, or null
* @param rs ResultSet, or null
*/
public static void cleanUp(Connection con, Statement stmt, ResultSet rs) {
try {
if (rs != null) {
while (rs.next()) // Drain the resultset.
;
rs.close();
}
if (stmt != null)
stmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
log.error("Failed to close connection", e);
}
}
/**
* Convert a ResultSet to a List of Maps
* This enables the results to be processed
* using the JSTL forEach tag.
* @param rs the ResultSet to convert
* @throws SQLException may be thrown by ResultSet operations
* @return the converted ResultSet
*/
@SuppressWarnings("unchecked")
public static List convertResultSet(ResultSet rs) throws SQLException {
try {
NDC.push("convertResultSet");
log.debug("start");
// Get column names
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
log.debug("Column Count is "+numberOfColumns);
String[] names = new String[numberOfColumns];
int[] types = new int[numberOfColumns];
for (int i = 0; i < names.length; ++i) {
names[i] = rsmd.getColumnName(i+1);
types[i] = rsmd.getColumnType(i+1);
log.debug("  Name="+names[i]);
}
List l = new LinkedList();
// Loop through the rows
while (rs.next()) {
log.debug("Processing row "+l.size());
Map m = new HashMap();
// Loop through the columns
for (int i = 0; i < names.length; ++i) {
Object o;
switch(types[i]) {
case Types.DATE:
o = rs.getTimestamp(i+1);
break;
default:
o = rs.getString(i+1);
}
log.debug("  "+names[i]+"='"+o+"'");
/*
if (o == null)
o = "&nbsp;";
*/
m.put(names[i], o);
}
// Add the map to the list
l.add(m);
log.debug("done");
}
return l;
} finally {
NDC.pop();
}
}
@SuppressWarnings("unchecked")
public static void formatDates(List rows, java.text.SimpleDateFormat fmt) {
Iterator it1 = rows.iterator();
while (it1.hasNext()) {
Map m = (Map)it1.next();
List names = new LinkedList();
Iterator it2 = m.entrySet().iterator();
while (it2.hasNext()) {
Map.Entry e = (Map.Entry)it2.next();
if (e.getValue() instanceof Timestamp) {
names.add(e.getKey());
}
}
it2 = names.iterator();
while(it2.hasNext()) {
String name = (String)it2.next();
Timestamp t = (Timestamp) m.get(name);
String s = fmt.format(t);
m.put(name,s);
}
}
}
/**
* Generate dummy results as a List of Maps
*
* @param columns A string array containing the required columns names
* @param rows    The number of rows to generate
* @return The data as a List of Maps
*/
@SuppressWarnings("unchecked")
public static List generateTestData(String[] columns, int rows) {
try {
NDC.push("generateTestData");
List l = new LinkedList();
for (int row = 1; row <= rows; ++row) {
Map m = new HashMap();
// Loop through the columns
for (int i = 0; i < columns.length; ++i) {
String s = columns[i]+" "+row;
m.put(columns[i], s);
}
// Add the map to the list
l.add(m);
}
return l;
} finally {
NDC.pop();
}
}

/**
* retrieves just the error code from an oracle message
*/
public static String parseOracleError(String errCode, String errMsg) {
int start = 0;
int end = 0;
start = errMsg.indexOf(errCode);
end = errMsg.indexOf(":", start);
return errMsg.substring(start, end);
}
/**Close a <code>Connection</code>, <code>Statement</code> and
* <code>ResultSet</code> cleanly.
*/
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
DbUtils.close(rs);
DbUtils.close(stmt);
DbUtils.close(conn);
} catch (SQLException sqlEx) {
log.error("Failed to close database objects cleanly.", sqlEx);
}
}
/**Close a <code>Connection</code> and <code>Statement</code> cleanly.
*/
public static void close(Connection conn, Statement stmt) {
try {
DbUtils.close(stmt);
DbUtils.close(conn);
} catch (SQLException sqlEx) {
log.error("Failed to close database objects cleanly.", sqlEx);
}
}
/**Close a <code>Connection</code> and <code>Statement</code> quietly.
*/
public static void closeQuietly(Connection conn, Statement stmt) {
DbUtils.closeQuietly(stmt);
DbUtils.closeQuietly(conn);
}
/**Close a <code>Connection</code>, <code>Statement</code> and 
* a <code>ResultSet</code> quietly.
*/
public static void closeQuietly(Connection conn, Statement stmt, ResultSet rst) {
DbUtils.closeQuietly(rst);
DbUtils.closeQuietly(stmt);
DbUtils.closeQuietly(conn);
}
}

问题可能出现在cleanup代码中，其中没有finally块。

如果ResultSet或Statement清理中的任何一个抛出异常，则不会发生con.close()。

尝试类似(未测试)：

public static void cleanUp(Connection con, Statement stmt, ResultSet rs) {
try {
if (rs != null) {
while (rs.next()) // Drain the resultset.
;
rs.close();
}
if (stmt != null)
stmt.close();
} catch (SQLException e) {
log.error("Failed to close statement", e);
}
finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
log.error("Failed to close connection", e);
} 
}
}

其他地方也可能出现类似的问题。

也有可能您实际上并不需要这里的一些其他代码。我不认为在关闭结果集之前"耗尽"结果集有什么好处，关闭语句也会关闭结果集。

如果您的cleanUp方法试图提前关闭ResultSet和/或Statement，那么它可能无法关闭给定的SQLConnection。例如，如果关闭ResultSet失败并抛出SQLException，它将直接跳到catch块，甚至不尝试关闭连接。在日志中关闭连接时是否有错误？

为了避免这个问题，要么提供专门的清理方法，只尝试关闭一件事，要么将每次关闭某事的尝试都封装在自己的try/catch块中。同样的建议适用于各种关闭和关闭静音方法。

//编辑

此代码部分很可能有害。在getNativeConnection(字符串jndiName)中：

DataSource ds = (DataSource)envCtx.lookup(jndiName);
conn = ds.getConnection();
if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) { 
log.debug("detected apache commons dbcp datasource");          
conn = ((org.apache.commons.dbcp.DelegatingConnection)conn).getInnermostDelegate();      
}

DelegatingConnection似乎是一个处理连接池的代理。当您打开"本机"连接时，DelegatingConnection代理不会在连接关闭时收到通知，也无法将其返回到池中。这样，池中就会充满已关闭的连接，直到达到限制为止。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium