Java Database Connectivity (JDBC)

I am facing a problem in closing database connections. I am getting the below error:

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

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


I have written a DBUtilities.java class which gets the connection using JNDI and returns the connection object. This connection object is then used in other java classes for quering database (calling procedures) and then closed when done. However, after some amount of successful connection, its giving the above error. The code written in my class is given below. Can anyone please let me know whats wrong in the code or is there a problem with the database. I am using oracle 10g. Please note I am doing connection.close() in the class in which I am using the connection


DBUtilities.java
==============
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;

}






}

Edited by: RP on Oct 3, 2012 10:49 AM