JDBC Connection Pooling issue

[Prabhudatta Choudhury]

I created a connection pooling class where I have all the necessary getconnection and releaseconnection Methods. I'm trying to reuse the same connection object in through out my project. So, I created an instance variable of the Connection in the inherited class and used that in all the methods in that calss. But when I tried to run the same in Tomcat 6 its throwing some error regarding release connection.

20 Apr 2012 10:47:14,832 ERROR [http-8000-5] releaseConnection (ConnectionPoolCustom2.java:260) - 24134853/2/jdbc:oracle:thin:@172.23.76.110:1521:Prabhudatta/PRDEVUSER The connection oracle.jdbc.driver.OracleConnection@1c6a99d just released was not created by the pool object

ConnectionPoolCustom2.java

package com.first;
public class ConnectionPoolCustom2{

public synchronized Connection getConnection() throws SQLException {
		Connection connection = null;

		if (!availableConnections.isEmpty()) {

			// We remove the first object from the list...kinda pop
			connection = (Connection) availableConnections.remove(0);

			if (!connection.isClosed()) {// && connection.isValid(timeoutSecs))
				// {

				// Set the autocommit of the connection to false. Implies that
				// the connection should explicitly take care of performing the
				// commit or rollback.
				connection.setAutoCommit(false);

				// add to the unavailable list
				unavailableConnections.add(connection);

			} // end if
			else {
				connection = null;
				cat.error(toString()
						+ " released a closed connection from the pool");
			}
		}

		if (connection == null) {

			// get the total number of connections created by the pool
			int numberOfConnections = unavailableConnections.size()
					+ availableConnections.size();
			if (cat.isTraceEnabled()) {
				cat
						.trace(toString()
								+ " Connection object not available in pool, trying to create new..."
								+ "(current total number of conn = "
								+ numberOfConnections
								+ ",  availableConnections.size() = "
								+ availableConnections.size() + ")");
			}

			if (maxDBConnections > numberOfConnections) {

				connection = createConnection();

				// Set the autocommit of the connection to false. Implies that
				// the connection should explicitly take care of performing the
				// commit or rollback.
				connection.setAutoCommit(false);

				// add to unaivalable conn
				unavailableConnections.add(connection);

			} else {
				cat
						.error(toString()
								+ " Maximum connections already in use, cant create more");
			}
		}
		// We will remove it prior to production release if required -
		// This to make it obvious if we have connection leak issues.
		if (cat.isTraceEnabled()) {
			cat.trace("connection = " + connection + "," + toString()
					+ " #unavailable:" + unavailableConnections.size()
					+ ", #available:" + availableConnections.size());
			cat.trace("unavailableConnections=" + unavailableConnections);
			cat.trace("availableConnections=" + availableConnections);
		}

		return connection;

	}

public synchronized void releaseConnection(final Connection aConn) {
		
		//cat.debug(unavailableConnections);
		cat.debug(aConn);
		
		if (aConn != null) {

			// Before releasing the connection, rollback all the changes
			// made.
			try {

				// rollback - just in case
				aConn.rollback();
			} catch (SQLException sqle) {

				cat.error(toString()
						+ " Error rolling back conn being released", sqle);
			}

			// check that the conn being release is already there in
			// unavailable
			// list
			// it should be - if not, it means a bug
			if (unavailableConnections.remove(aConn)) {

				// Push the released connection onto the connection pool
				// stack
				availableConnections.add(aConn);
			} else {

				cat.error(toString() + " The connection " + aConn
						+ " just released was not created by "
						+ "the pool object");
			}

		} // end if

		// We will remove it prior to production release if required -
		// This to make it obvious if we have connection leak issues.
		if (cat.isTraceEnabled()) {
			cat.trace(toString() + " #unavailable:"
					+ unavailableConnections.size() + ", #available:"
					+ availableConnections.size());

			cat.trace("unavailableConnections=" + unavailableConnections);
			cat.trace("availableConnections=" + availableConnections);
		}
	}





}




Package com.someother;
Public class testSomeOtherConnection{

Connection cSomeOther;
public void test()
{

//checking if the connection is null

try
	{
		if(cSomeOther==null||cSomeOther.isClosed())
    	{
    		cSomeOther=getConnection();    		
    	}
catch(Exception e)
	{
		
		cat.fatal("Exception in getBusinessDate , rolling back transaction :");
		try{
			cSomeOther.rollback();
		}catch(SQLException sqle){
			cat.fatal("Exception while retrieval of Date, rolling back transaction :");
		}
		
		
	}

	finally
	{
		try{
			if(cSomeOther!=null && !cSomeOther.isClosed()){
				releaseConnection(cSomeOther);
				cSomeOther=null;
			}
		}catch(SQLException sqle){
			cat.error("Exception while Closing Database Resources :");
			
		}
	}
	cat.info("Exiting");
	
	
}

}

}

[Wendy L Gibbons]

thanks for trying but our tags here inthe ranch use [ ] brackets not < >, so I have fixed them for you

[Prabaharan Gopalan]

What exactly is the error you're getting? Were you able to execute statements through the connection acquired from the pool?