gistfile1.txt

package uk.co.inbrand.inbrandcore.db;


import java.net.InetAddress;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.Format;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import javax.management.InstanceAlreadyExistsException;
import javax.management.InstanceNotFoundException;
import javax.management.MBeanRegistrationException;
import javax.management.MalformedObjectNameException;
import javax.management.NotCompliantMBeanException;
import javax.sql.DataSource;

import org.apache.commons.dbcp.AbandonedConfig;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DelegatingConnection;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.Validate;
import org.apache.commons.lang.time.FastDateFormat;
import org.apache.commons.pool.KeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.xml.utils.ObjectPool;
import org.springframework.jdbc.datasource.ConnectionProxy;

import uk.co.inbrand.inbrandcore.common.IBHelper;
import uk.co.inbrand.inbrandcore.encryption.BlowfishEcb;
import uk.co.inbrand.inbrandcore.utilities.CachedTypedProperties;
import uk.co.inbrand.inbrandcore.utilities.DataConverter;
import uk.co.inbrand.inbrandcore.utilities.StringUtils;
import uk.co.inbrand.inbrandcore.jmx.ManagementRegistry;



/**
 * This class provdes run-time configurable database connection pools for an
 * arbitrary number of databases.
 *
 * A pool is defined by the following properties in the uk.co.inbrand.properties file
 * (which must be present on the Java class path):
 * <PRE>
 * jdbc&lt;pool name&gt;Driver - The JDBC driver to use (usually
 *                               oracle.jdbc.driver.OracleDriver)
 * jdbc&lt;pool name&gt;Url    - The JDBC URL for the database
 * jdbc&lt;pool name&gt;User   - The database user
 * jdbc&lt;pool name&gt;EncryptedPassword - The database user password,
 *                                          encrypted with the PasswordEncrypt
 *                                          command-line utility
 * jdbc&lt;pool name&gt;MaxConnections    - The maximum number of connections
 *                                          (optional)
 * jdbc&lt;pool name&gt;MinConnections    - The minimum number of connections
 *                                          (optional)
 * </PRE>
 *
 * Additionally, the following global property in the uk.co.inbrand.properties file
 * controls the recording of diagnostic timing information
 * <PRE>
 * connectionPoolsStoreTimingInfo - If set to <code>true</code> then timing
 *                                  information is recorded for all statements
 *                                  run through this class.
 * </PRE>
 */
@SuppressWarnings("deprecation")
public class ConnectionPool implements ConnectionPoolMBean {
	
	/**
	 * Extends the DBCP PoolableConnectionFactory to ensure that connections get expired properly
	 */
	public class CPPoolableConnectionFactory extends PoolableConnectionFactory {
		
		private Logger logger = Logger.getLogger(CPPoolableConnectionFactory.class);

		public CPPoolableConnectionFactory( ConnectionFactory connFactory,
                                                    ObjectPool pool, 
                                                    KeyedObjectPoolFactory stmtPoolFactory,
                                                    String validationQuery, 
                                                    Boolean defaultReadOnly, 
                                                    boolean defaultAutoCommit,
                                                    AbandonedConfig abandonedConfig ) {
			super(connFactory, pool, stmtPoolFactory, validationQuery, defaultReadOnly.booleanValue(),defaultAutoCommit,abandonedConfig );
		}

		public CPPoolableConnectionFactory(ConnectionFactory connFactory,
				ObjectPool pool, KeyedObjectPoolFactory stmtPoolFactory,
				String validationQuery, boolean defaultReadOnly,
				boolean defaultAutoCommit, int defaultTransactionIsolation) {
			super(connFactory, pool, stmtPoolFactory, validationQuery, defaultReadOnly,
					defaultAutoCommit, defaultTransactionIsolation);
		}

		public CPPoolableConnectionFactory(ConnectionFactory connFactory,
				ObjectPool pool, KeyedObjectPoolFactory stmtPoolFactory,
				String validationQuery, boolean defaultReadOnly,
				boolean defaultAutoCommit) {
			super(connFactory, pool, stmtPoolFactory, validationQuery, defaultReadOnly,
					defaultAutoCommit);
		}
		
		

		/**
		 * Equivalent to the destroyConnecction in the main cool
		 * to ensure the connections are removed from the various pools
		 */
		@Override
		public void destroyObject(Object obj) throws Exception {
			
			try {
			
				Connection connection = (Connection) obj;
				
				Object connectionId = getApacheDbcpIdentifier(connection);
				
		    //
		    // We should have a record of the owning pool for each connection we create.
		    // Check this is so for this connection and remove it.
		    //
		    if (null == ConnectionPool.connectionToPool.remove(connectionId)) {
		      logger.log(Level.WARN,
		                 "Could not find pool for connection " + connection);
		    }
	
		    //
		    // If the connection was in use, remove it from the mapping of connections
		    // to connection user information.
		    //
		    if (null == connectionToInfo.remove(connectionId)) {
		      logger.log(Level.WARN,
              "Could not find info for connection " + connection);
		    }
			} finally {
				//
				// This will actually close the connection, amongst other housekeeping
				//
				super.destroyObject(obj);
			}
		}
		
	}
  private static final Logger CONNECTION_POOL_LOGGER = Logger.getLogger(ConnectionPool.class);

  /**
   * Whether the class-level variables have been initialised
   */
  private static boolean initialised = false;

  /**
   * The hostname of the machine that we are running on
   */
  private static String HOSTNAME = "unknown";
  
  /**
   * The value of the timeout for establishing database connections (in seconds)
   */
  private static int TIMEOUT = 0;

  //
  // We should only have to get the hostname once, along with the timeout
  // (since it's a static field on DriverManager)
  //
  static {
    try {
      HOSTNAME = InetAddress.getLocalHost().getCanonicalHostName();
    } catch(Exception e) {
      CONNECTION_POOL_LOGGER.error("Could not get hostname for connection pool");
    }
    
    try {
      CachedTypedProperties props = CachedTypedProperties.getInstance("uk.co.inbrand.properties");
      TIMEOUT = props.getIntProperty("connectionTimeoutInSec", TIMEOUT);
      DriverManager.setLoginTimeout(TIMEOUT);
    } catch(Exception e) {
      CONNECTION_POOL_LOGGER.error("Could not configure timeout for DriverManager");
    }
  }


  /**
   * Whether to store timing information for all queries run through the pool
   */
  private static boolean storeTimingInfo;

  /**
   * Whether to store stack trace information for all queries run through the pool
   */
  private static boolean storeStackTraceInfo = false;
  
  /**
   * Whether we are going to use Apache Commons DBCP API
   */
  private boolean apacheDbcpEnabled = false;
  
  /**
   * The datasource that will distribute connections
   */
  private DataSource dbcpDataSource = null;
  
  /**
   * The pool that will contain connections
   */
  private GenericObjectPool objectPool = null;
  
  /**
   * The database name for this pool
   */
  private String dbName;

  /**
   * The database address for this pool
   */
  private String jdbcUrl;

  /**
   * The database user for this pool
   */
  private String jdbcUser;

  /**
   * Whether to do jdbc in depth logging or not
   */
  private boolean jdbcInDepthLogging = false;
  
  /**
   * Whether the pool can be flushable or not
   */
  private boolean jdbcFlushable = false;
  
  /**
   * Flag used to test the flushing - dont need to wait for the max failures
   */
  private boolean jdbcTestFlushing = false;
  
  /**
   * If set to true the pool will monitor itself to make sure it hasn't
   * run out of connections
   */
  private boolean jdbcAutoFlushing = false;
  
  
  /**
   * The maximum number of failed connection requests before they are flushed
   */
  private int jdbcMaxFailuresBeforeFlush = 500;

  /**
   * The encrypted database password for this pool
   */
  private byte[] jdbcEncryptedPassword;

  /**
   * The minimum number of connections in this pool
   */
  private int jdbcMinConnections;

  /**
   * The maximum number of connections in this pool
   */
  private int jdbcMaxConnections;

  /**
   * Maximum age of connections in this pool beyond which we don't reuse them
   */
  private int jdbcMaxSecondsToKeepConnection;
  
  /**
   * When the pool has been exhausted, this is the maximum number of 
   * seconds that the pool will wait for a new connection to be available
   * Only work with DBCP
   */
  private int jdbcMaxBlockPeriod = 0;
  
  /**
   * How often in seconds that idle connections are removed from the pool
   * Only work with DBCP
   */
  private int jdbcEvictionPeriod = -1;

  /**
   * The number of connections in use from this pool. This integer should always
   * be synchronized by including it in a block that is synchronized to freeConnections
   * 
   */
  private int numConnectionsInUse = 0;

  /**
   * The connections in this pool that are not currently being used
   *
   * Note that we can safely make this an ArrayList rather than a Vector because
   * it is only accessed in sychronized methods.
   */
  @SuppressWarnings("unchecked")
private ArrayList freeConnections;

  /**
   * Mapping from each each connection in the pool (both in use and free) to its
   * expiry time. Use a hashtable so it there is no need for synchronization.
   */
  @SuppressWarnings("unchecked")
private Hashtable connectionToExpiryTime;

  private static final Format DATE_FORMAT = FastDateFormat.getInstance("HH:mm:ss:SSS");

  /**
   * The time when the last request for a connection was successfully returned
   */
  private Date lastSuccessfulConnectionRequest = null;

  /**
   * The number of failures that have occured since the last successful
   * request for a connection from this pool.
   */
  private int numberOfFailuresSinceLastSuccess = 0;
  
  /**
   * The dialect class to use for this pool
   */
  private DatabaseDialect databaseDialect;
  
  /**
   * The default database dialect
   */
  private static DatabaseDialect DEFAULT_DATABASE_DIALECT = new MysqlDialect();
  
  /**
   * Value for the maximum number of arguments to be used in an Oracle "in"
   * statement - eg select colname from table were value in (?,?,?, etc);
   */
  public static final int MAX_IN_PARAMETERS  = 1000;

  /**
   * Inner class for holding key information about in-use connections.
   */
  public class InUseConnectionInfo {
    /**
     * The name of the logger of the current user of the connection
     */
    private String user;

    /**
     * The class, method and line where the connection was requested
     */
    private String callingClass;
    
    /**
     * The complete stack trace of the cal to the connection pool
     */
    private String stackTrace;

    /**
     * The hash code of the connection that this is collecting info for
     */
    private int hashCode;

    /**
     * The time at which the connection was given to this user
     */
    private Date whenGiven;

    /**
     * The SQL of the last statement or stored procedure created on this
     * connection (null if the user has not yet created one).
     */
    private String lastQuery;

    /**
     * The time the last statement or stored procedure began running (or null
     * if there is none or we have not yet run it).
     */
    private Date lastQueryStarted;

    /**
     * The time the last statement or stored procedure finished running (or null
     * if there is none or it has not yet finished).
     */
    private Date lastQueryEnded;

    private boolean inUse = false;
    
    /**
     * Constructor
     *
     * @param user the user to whom the current connection is being given
     * @param callingClass the class, method, line number that got this connection
     * @param hashCode the hashcode of the connection
     */
    public InUseConnectionInfo(String user, String callingClass, int hashCode) {
      this.user = user;
      this.hashCode = hashCode;
      this.callingClass = callingClass;
      this.stackTrace = "";
      this.whenGiven = new Date();
      this.lastQuery = null;
      this.lastQueryStarted = null;
      this.lastQueryEnded = null;
      this.inUse = true;
      return;
    }

    /**
     * @return The name of the logger of the current user of the connection to
     *         which this object relates.
     */
    public String getUser() {
      return this.user;
    }

    /**
     * @return the hashCode
     */
    public int getHashCode() {
      return hashCode;
    }

    /**
     * @return the callingClass
     */
    public String getCallingClass() {
      return callingClass;
    }

    /**
     * @return The time at which the connection to which this object relates was
     *         obtained from the pool
     */
    public Date getWhenGiven() {
      return this.whenGiven;
    }

    /**
     * @return The SQL of the last statement or stored procedure created on the
     *         connection to which this object relates (or null if the user has
     *         not yet created one).
     */
    public String getLastQuery() {
      return this.lastQuery;
    }

    /**
     * @return The time the last statement or stored procedure began running (or
     *         null if there is none or we have not yet run it).
     */
    public Date getLastQueryStarted() {
      return this.lastQueryStarted;
    }

    /**
     * @return The time the last statement or stored procedure finished running
     *         (or null if there is none or it has not yet finished).
     */
    public Date getLastQueryEnded() {
      return this.lastQueryEnded;
    }

    /**
     * @return the stackTrace
     */
    public String getStackTrace() {
      return stackTrace;
    }
    

    /**
     * @return whether the connection is in use or not
     */
    public boolean isInUse() {
      return inUse;
    }
  }

  /**
   * Mapping from each connections to information about it held in an
   * {@link #InUseConnectionInfo} object.
   * 
   * This is a Hashtable rather than a HashMap to ensure single-threaded access
   * when there are multiple connections.
   */
  @SuppressWarnings("unchecked")
private Hashtable connectionToInfo;

  /**
   * Mapping from each pooled connection (in-use or free) to the pool containing
   * it.
   *
   * This is a Hashtable rather than a HashMap to ensure single-threaded access
   * when there are multiple pools.
   */
  @SuppressWarnings("unchecked")
private static Hashtable connectionToPool = new Hashtable();

  /**
   * Default maximum number of connections
   */
  private static final int DEFAULT_MAX_CONNECTIONS = 10;

  /**
   * Default minimum number of connections
   */
  private static final int DEFAULT_MIN_CONNECTIONS = 2;

  /**
   * Default maximum lifespan for connections
   */
  private static final int DEFAULT_MAX_SECONDS_TO_KEEP_CONNECTION = 3600;

  /**
   * Number of milliseconds in a second
   */
  private static final int MILLISECONDS_PER_SECOND = 1000;
  
  /**
   * The key used for decrypting encrypted database passwords from configuration
   * files.
   *
   * NB: This is a Blowfish key so its maximum meaningful length is 56 bytes.
   *
   * DO NOT CHANGE THIS WITHOUT MAKING THE EQUIVALENT CHANGE TO
   * uk.co.inbrand.encryption.PasswordEncrypt AND RECREATING ENCRYPTED PASSWORDS IN
   * CONFIGURATION FILES!
   */
  private static final byte[] DATABASE_PASSWORD_ENCRYPTION_KEY = {
    (byte) 0xF7, (byte) 0x7B, (byte) 0xF9, (byte) 0x2F,
    (byte) 0x95, (byte) 0x01, (byte) 0x20, (byte) 0x48,
    (byte) 0x43, (byte) 0x49, (byte) 0xC4, (byte) 0x0A,
    (byte) 0x98, (byte) 0x1A, (byte) 0x26, (byte) 0xA2,
    (byte) 0x27, (byte) 0x57, (byte) 0xF4, (byte) 0x0C,
    (byte) 0x8A, (byte) 0x2F, (byte) 0x06, (byte) 0x41,
    (byte) 0x79, (byte) 0x1B, (byte) 0x7F, (byte) 0x16,
    (byte) 0x04, (byte) 0x1A, (byte) 0x16, (byte) 0x13,
    (byte) 0xAE, (byte) 0x5A, (byte) 0x35, (byte) 0x21,
    (byte) 0x9C, (byte) 0x24, (byte) 0x0A, (byte) 0x61,
    (byte) 0x9E, (byte) 0x0A, (byte) 0x7D, (byte) 0x11,
    (byte) 0xBE, (byte) 0x02, (byte) 0x05, (byte) 0xD0,
    (byte) 0x9B, (byte) 0xD0, (byte) 0x56, (byte) 0x07,
    (byte) 0xBA, (byte) 0x03, (byte) 0x0E, (byte) 0xB1
  };

  /**
   * The singletons -- one per database/pool
   */
  @SuppressWarnings("unchecked")
private static HashMap instances = new HashMap();

  /**
   * The pools that have run out of connections and therefore have been
   * removed from instances
   */
  @SuppressWarnings("unchecked")
private static List failedPools = new ArrayList();


  /**
   * .:TBC:. MY 01/10/2003 Move all this inside InUseConnectionInfo
   *
   * Per-connection look-up to find the mapping from statements run on that
   * connection to the (last) SQL string executed by each of those statements.
   * The complete set of mappings is:
   *
   *  connectionStatements: connection -> mapping from statements to SQL strings
   *
   *  connectionSqlStrings: connection -> mapping from SQL strings to statistics
   *
   * Note that this is a Hashtable rather than a HashMap to ensure that access
   * is synchronised.
   *
   * Note the need for two hash maps (<code>connectionStatements</code> and
   * <code>connectionSqlStrings</code>) arises from the fact that when we
   * execute a statement we cannot get at the SQL used to construct it.  So we
   * must first intercept statement construction to get the SQL of the statement
   * and then intercept statement execution to get the timing information.
   */
  @SuppressWarnings("unchecked")
private static Hashtable connectionStatements = null;

  /**
   * Per-connection look-up to find the mapping from SQL strings executed on
   * that connection to timing information.
   *
   * Note that this is a Hashtable rather than a HashMap to ensure that access
   * is synchronised.
   */
  @SuppressWarnings("unchecked")
private static Hashtable connectionSqlStrings = null;

  /**
   * Inner class for holding timing information about individual SQL statements
   */
  private static class TimingInfo {
    /**
     * The number of times the query was run on this connection
     */
    private int numberOfTimesRun = 0;

    /**
     * The total time spent executing the query.  (Divide by the number of times
     * executed to get the average execution time.)
     */
    private long totalExecutionTime = 0;
  }

  /**
   * This is a singleton so the class has a private constructor.  Use the
   * {@link #getInstance()} static method to obtain a reference to the
   * singleton.
   *
   * @param dbName  name of the pool to construct
   * @param logger  where to log errors
   *
   * @throws ClassNotFoundException  if we cannot find the uk.co.inbrand.properties
   *                                 configuration file in the class path or
   *                                 cannot load the database driver
   *
   * @throws SQLException  if we are unable to create valid connections for the
   *                       database for which this pool is configured
   */
  @SuppressWarnings("unchecked")
private ConnectionPool(String dbName,
                         Logger logger)
                  throws ClassNotFoundException,
                         SQLException {
    //
    // Try to find the database connection parameters from the main uk.co.inbrand
    // properties file.
    //
    CachedTypedProperties inbrandProperties =
                            CachedTypedProperties.getInstance("inbrand.properties");

    //
    // Is this the first time this class was used?
    //
    if (!ConnectionPool.initialised) {
      //
      // By default, don't store timing info
      //
      ConnectionPool.storeTimingInfo = inbrandProperties.getBooleanProperty(
        "connectionPoolsStoreTimingInfo",
        false
      );
      
      ConnectionPool.storeStackTraceInfo = inbrandProperties.getBooleanProperty(
        "connectionPoolsStackTraceInfo",
        false
      );

      if(logger.isInfoEnabled())
        logger.log(
            Level.INFO,
            "First time using ConnectionPool.  Statement timing = " +
            ConnectionPool.storeTimingInfo
        );
      
      if (ConnectionPool.storeTimingInfo) {
        ConnectionPool.connectionStatements = new Hashtable();
        ConnectionPool.connectionSqlStrings = new Hashtable();
      }

      ConnectionPool.initialised = true;
    }

    //
    // Read the properties in which we are interested
    //
    String jdbcDriver = inbrandProperties.getProperty("jdbc" + dbName + "Driver");
    this.jdbcUrl = inbrandProperties.getProperty("jdbc" + dbName + "Url");
    this.jdbcUser = inbrandProperties.getProperty("jdbc" + dbName + "User");
    this.jdbcEncryptedPassword = DataConverter.hexStringToBytes(
      inbrandProperties.getProperty("jdbc" + dbName + "EncryptedPassword")
    );
    this.jdbcMaxConnections =
      inbrandProperties.getIntProperty("jdbc" + dbName + "MaxConnections",
                                   ConnectionPool.DEFAULT_MAX_CONNECTIONS);
    this.jdbcMinConnections =
      inbrandProperties.getIntProperty("jdbc" + dbName + "MinConnections",
                                   ConnectionPool.DEFAULT_MIN_CONNECTIONS);

    this.jdbcMaxSecondsToKeepConnection = inbrandProperties.getIntProperty(
      "jdbc" + dbName + "MaxSecondsToKeepConnection",
      ConnectionPool.DEFAULT_MAX_SECONDS_TO_KEEP_CONNECTION
    );

    this.jdbcInDepthLogging =
      inbrandProperties.getBooleanProperty("jdbc" + dbName + "InDepthLogging", false);

    this.jdbcFlushable =
      inbrandProperties.getBooleanProperty("jdbc" + dbName + "Flushable", false);

    this.jdbcMaxFailuresBeforeFlush =
      inbrandProperties.getIntProperty("jdbc" + dbName + "MaxFailuresBeforeFlush", 500);

    this.jdbcTestFlushing =
      inbrandProperties.getBooleanProperty("jdbc" + dbName + "TestFlushing", false);

    this.jdbcAutoFlushing =
      inbrandProperties.getBooleanProperty("jdbc" + dbName + "AutoFlushing", false);

    this.apacheDbcpEnabled =
      inbrandProperties.getBooleanProperty("jdbc" + dbName + "DBCP", false);
    
    this.jdbcMaxBlockPeriod =
    	inbrandProperties.getIntProperty("jdbc" + dbName + "MaxBlockPeriod", 0);
    
    this.jdbcEvictionPeriod =
    	inbrandProperties.getIntProperty("jdbc" + dbName + "EvictionPeriod", -1);
    
    try {
		this.databaseDialect = (DatabaseDialect)
			Class.forName(
					inbrandProperties.getStringProperty("jdbc" + dbName + "DatabaseDialect", 
					MysqlDialect.class.getName())
			).newInstance();
		
	} catch (InstantiationException e) {
		//
		// Use the ClassNotFoundException to avoid break the existing contract
		//
		throw new ClassNotFoundException(e.getMessage(), e);
	} catch (IllegalAccessException e) {
		//
		// Use the ClassNotFoundException to avoid break the existing contract
		//
		throw new ClassNotFoundException(e.getMessage(), e);
	}
    
    //
    // If auto flushing IS turned on then we have to make sure that test
    // flushing is NOT turned on. If we didn't then the pool would flush itself
    // every time a failure happened and spiral into oblivion!!!
    //
    if(this.jdbcAutoFlushing) {
      if(this.jdbcTestFlushing) {
        this.jdbcTestFlushing = false;
        CONNECTION_POOL_LOGGER.fatal(HOSTNAME + "_" + this.jdbcUser + " " +
                                     "Auto flushing and Test flushing were both set to true in uk.co.inbrand.properties. " +
                                     "Test flushing has been set to false.");
      }
    }

    //
    // need to load the driver class into memory
    //
    Class.forName(jdbcDriver);
    
    //
    // we'll store the in use info regardless of the method of pooling
    //
    this.connectionToInfo = new Hashtable(this.jdbcMaxConnections);
        
    if(!this.apacheDbcpEnabled) {

      //
      // Attempt to fill up the pool to its minimum level
      //
      this.freeConnections = new ArrayList(this.jdbcMaxConnections);
      this.connectionToExpiryTime = new Hashtable(this.jdbcMaxConnections);
      this.numConnectionsInUse = 0;
  
      this.createConnections(this.jdbcMinConnections, logger);
    
    } else {
      
      //
      // Decrypt the key before we hand it to the JDBC driver
      //
      BlowfishEcb bfEcb = new BlowfishEcb(DATABASE_PASSWORD_ENCRYPTION_KEY);

      byte[] passwordBytes = new byte[this.jdbcEncryptedPassword.length];

      bfEcb.decrypt(this.jdbcEncryptedPassword, passwordBytes);
      
      String passwordString = new String(passwordBytes);
      
      passwordString = passwordString.trim();  
      
      //
      // create the connection pool
      //
      objectPool = new GenericObjectPool();
      
      //
      // when the pool has run out of connections we want it to throw an exception
      //
      if (0 == this.jdbcMaxBlockPeriod) {
      	objectPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_FAIL);
      } else {
      	objectPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
      	objectPool.setMaxWait(this.jdbcMaxBlockPeriod * 1000);
      }
      
      //
      // the pool can have all the connections active or all the connections idle at one time
      //
      objectPool.setMaxActive(this.jdbcMaxConnections);
      objectPool.setMaxIdle(this.jdbcMaxConnections);
      
      //
      // Ensure the pool doesn't get too small
      //
      objectPool.setMinIdle(this.jdbcMinConnections);
      
      //
      // always test the connection before we borrow one from the pool
      //
      objectPool.setTestOnBorrow(true);
      objectPool.setTestOnReturn(false);
      objectPool.setTestWhileIdle(false);
      
      //
      // The amount of time an object can remain idle before being evicted
      //
      if (this.jdbcEvictionPeriod > 0) {
      	objectPool.setSoftMinEvictableIdleTimeMillis(this.jdbcMaxSecondsToKeepConnection * 1000);
      	objectPool.setTimeBetweenEvictionRunsMillis(this.jdbcEvictionPeriod * 1000);
      }
      
      //
      // the connection factory is used to actual connect to the database
      //
      ConnectionFactory connectionFactory = 
        new DriverManagerConnectionFactory(this.jdbcUrl, this.jdbcUser, passwordString);
      
      //
      // the poolable connection factory ties the pool and the connection factory together
      //
      @SuppressWarnings("unused")
	PoolableConnectionFactory poolableConnectionFactory = 
        new CPPoolableConnectionFactory(connectionFactory, objectPool, null,
            getDatabaseDialect().getConnectionValidationQuery(), false, true);
      
      //
      // the datasource will issue the connections
      //
      PoolingDataSource poolingDataSource = new PoolingDataSource(objectPool);
      
      //
      // Allow the underlying connections to be accessible for identification purposes
      //
      poolingDataSource.setAccessToUnderlyingConnectionAllowed(true);
      this.dbcpDataSource = poolingDataSource;
      
    }   
    
    //
    // Save off the name of the pool for tracing
    //
    this.dbName = dbName;
    
    return;
  }


  /**
   * Returns a reference to the singleton for the specified database pool, with
   * FINE level logging about pool creation.
   *
   * @param dbName  which pool to return (as defined in the uk.co.inbrand.properties
   *                file).  Empty string means the default pool.
   * @param logger  the logger to which information about pool creation should
   *                be traced
   *
   * @return  the singleton for the specified database pool
   *
   * @throws ClassNotFoundException  if the pool does not exist and could not be
   *                                 created because either the database driver
   *                                 or the uk.co.inbrand.properties configuration file
   *                                 could not be found
   *
   * @throws SQLException  if during pool creation we were unable to create
   *                       valid connections
   */
  @SuppressWarnings("unchecked")
public static ConnectionPool getInstance(String dbName, Logger logger)
                                           throws ClassNotFoundException, SQLException {

    ConnectionPool poolToReturn = null;

    //
    // Better performance when the thread keeps the lock while it checks
    // for the pool and then creates a new one if it doesn't exist.
    //
    synchronized(ConnectionPool.instances) {

      poolToReturn = (ConnectionPool)ConnectionPool.instances.get(dbName);

      if (null == poolToReturn) {
        if(logger.isDebugEnabled())
          logger.log(Level.DEBUG, "Creating database connection pool \"" + dbName + "\"");
        poolToReturn = new ConnectionPool(dbName, logger);
        ConnectionPool.instances.put(dbName, poolToReturn);
        
        registerConnectionPoolWithManagement(poolToReturn, logger);
      }
    }

    return poolToReturn;
  }
  
  private static String createPoolMBeanName(ConnectionPool pool) {
	  String mbeanName = pool.getdbName();
	  //
	  // Handle empty value
	  //
	  if (StringUtils.isNullOrEmpty(mbeanName)) {
		 mbeanName = "Default";
	  } 
	
	  return mbeanName;
  }
  
  /**
   * Registers the pool with JMX
   * @param pool The pool to register
   * @param logger A logger to use
   */
  private static void registerConnectionPoolWithManagement(ConnectionPool pool, Logger logger) {
	  
	try {
      	
		ManagementRegistry.getInstance().registerMBean(createPoolMBeanName(pool), pool);
			
	} catch (MalformedObjectNameException e) {
		logger.error(e);
	} catch (UnsupportedOperationException e) {
		logger.error(e);
	} catch (RuntimeException e) {
		logger.error(e);
	} catch (InstanceAlreadyExistsException e) {
		logger.error(e);
	} catch (MBeanRegistrationException e) {
		logger.error(e);
	} catch (NotCompliantMBeanException e) {
		logger.error(e);
	} catch (Exception e) {
		//
		// Finally catch anything, just to be sure
		//
		logger.error(e);
	}
  }
  
  /**
   * Unregisters the pool with JMX
   * @param pool The pool to register
   * @param logger A logger to use
   */
  private static void unregisterConnectionPoolWithManagement(ConnectionPool pool, Logger logger) {
	
	  try {
		
		  ManagementRegistry.getInstance().unregisterMBean(createPoolMBeanName(pool), pool);
		
	} catch (MalformedObjectNameException e) {
		logger.error(e);
	} catch (InstanceNotFoundException e) {
		logger.error(e);
	} catch (MBeanRegistrationException e) {
		logger.error(e);
	} catch (Exception e) {
		//
		// Finally catch anything, just to be sure
		//
		logger.error(e);
	}
  }


  /**
   * Return a reference to the singleton for the default database, with FINE
   * level logging about pool creation.  Equivalent to
   * <code>getInstance("", logger)</code>
   *
   * @param logger  if not null then the logger to which information about
   *                pool creation should be traced  <i>(Note that the ability to
   *                supply a null logger is provided only for backwards
   *                compatibility with existing code.  New code should always
   *                supply a valid logger.)</i>
   *
   * @return  the singleton for the specified database pool
   *
   * @throws ClassNotFoundException  if the pool does not exist and could not be
   *                                 created because either the database driver
   *                                 or the uk.co.inbrand.properties configuration file
   *                                 could not be found
   *
   * @throws SQLException  if during pool creation we were unable to create
   *                       valid connections
   */
  public static ConnectionPool getInstance(Logger logger)
                                           throws ClassNotFoundException, SQLException {
    return ConnectionPool.getInstance("", logger);
  }

  /**
   * Build a list of comma separated question marks from an array, suitable for
   * inclusion in a {@link PreparedStatement} IN clause (e.g. WHERE id in (?, ?, ?)).
   *
   * The corresponding {@link #setInListParameters()} method is used to set
   * the actual values in the PreparedStatement.
   *
   * @param instanceIds An int array containing at least one value.
   * @param srcPos The index into the instanceIds to start setting parameters
   *            from.
   * @param count The total number of parameters that need to be set.
   * 
   * @return A string consisting of comma separated question marks.
   */
  public static String getInList(int[] instanceIds, int srcPos, int count) {
    Validate.isTrue(!ArrayUtils.isEmpty(instanceIds),
        "instanceIds should be a non-empty array");
    Validate.isTrue((srcPos >= 0), "srcPos should be >= 0");
    Validate.isTrue((count > 0), "count should be > 0");
    Validate.isTrue((srcPos >= 0) && ((srcPos + count) <= instanceIds.length),
        "srcPos + count should be <= instanceIds.length");
    
    return getTupleInList(count, "?");
  }

  /**
   * Build a list of comma seperated question marks from an array, suitable for
   * inclusion in a PreparedStatment IN clause (e.g. WHERE id IN (?, ?, ?)).
   *
   * The corresponding {@link #setInListParameters()} method is used to set
   * the actual values in the PreparedStatement.
   *
   * @param instanceIds  An int array containing at least one value.
   * 
   * @return A string consisting of comma separated question marks, the number
   *         of which equal the length of the array parameter.  If the array
   *         had length equal 1, the string will consist of a single question
   *         mark.
   */
  public static String getInList(int[] instanceIds) {
    Validate.isTrue(!ArrayUtils.isEmpty(instanceIds),
        "instanceIds should be a non-empty array");
    
    return getInList(instanceIds, 0, instanceIds.length);
  }

  /**
   * Build a list of comma seperated question marks from an array, suitable for
   * inclusion in a PreparedStatment IN clause (e.g. WHERE id IN (?, ?, ?)).
   *
   * @param collection  The {@link Collection} to build the in-list for.
   * 
   * @return A string consisting of comma separated question marks, the number
   *         of which equal the size of the collection.  If the collection
   *         had length equal 1, the string will consist of a single question
   *         mark.
   */
  public static String getInList(Collection<?> collection) {
    Validate.isTrue((collection != null), "collection should not be null");
    Validate.isTrue(!collection.isEmpty(), "collection should not be empty");
    
    return getTupleInList(collection.size(), "?");
  }
  
  /**
   * Build a list of comma separated n-tuple markers, suitable for inclusion in
   * a {@link PreparedStatement} IN class. ie
   * 
   * <pre><code>
   *   WHERE id IN (?, ?, ?) ...
   *   WHERE (id, value) IN ((?, ?), (?, ?), (?, ?))
   *   WHERE (id, value) IN ((204, ?), (204, ?), (204, ?))
   * </code></pre>
   * 
   * @param tupleCount The number of tuples, or entries, that will go in the SQL
   *            IN clause.
   * @param tupleEntry The string value to use for the tuple, ie '?', '(?, ?)',
   *            etc.
   * 
   * @return A string consisting of the comma separated tuple list.
   */
  public static String getTupleInList(int tupleCount, String tupleEntry) {
    assert (tupleCount > 0);
    StringBuilder tupleInList = new StringBuilder(tupleEntry);
    for(int i = 1; i < tupleCount; i++) {
      tupleInList.append(", ").append(tupleEntry);
    }
    
    return tupleInList.toString();
  }
  
  /**
   * Set the values of a PreparedStatement using a subset of the the values from
   * an int array.
   * 
   * @param query A {@link PreparedStatement} 
   * @param parameterNumber The prepared statement parameter number from which
   *            the values will be set. E.g. if this value is zero, then the
   *            first parameter will be set for index one.
   * @param instanceIds The array of int's whose values will be used to set the
   *            prepared statement parameters.
   * @param srcPos The index into the instanceIds to start setting parameters
   *            from.
   * @param count The total number of parameters that need to be set.
   * 
   * @return The last parameter number for which a value was set.
   * 
   * @throws SQLException If a problem occurred setting the parameter values.
   */
  public static int setInListParameters(PreparedStatement query,
                                        int parameterNumber,
                                        int[] instanceIds,
                                        int srcPos,
                                        int count)
  throws SQLException {
    assert(instanceIds.length > 0);
    assert(count > 0);
    assert((srcPos >= 0) && ((srcPos + count) <= instanceIds.length));
    for(int ii = srcPos; ii < (srcPos + count); ii++) {
      query.setInt(++parameterNumber, instanceIds[ii]);
    }
    
    return parameterNumber;
  }

  /**
   * Set the values of a PreparedStatement using the values from an int array.
   *
   * @param query A prepared statement.  This should have been constructed
   *        using the corresponding {@link #getInList()} method to ensure that
   *        the appropriate number of parameters exist.
   * @param parameterNumber the PreparedStatement parameter number from which
   *        the values will be set.  E.g. if this value is zero, then the
   *        first parameter will be set for index one.
   * @param instanceIds  An array of int's whose values will be used to set
   *        the PreparedStatement parameters.
   *
   * @return The last parameter number for which a value was set.
   *
   * @throws SQLException on database error.
   */
  public static int setInListParameters(PreparedStatement query,
                                 int parameterNumber,
                                 int[] instanceIds)
                          throws SQLException {
    assert (instanceIds.length > 0);
    for (int ii = 0; ii < instanceIds.length; ++ii) {
      query.setInt(++parameterNumber, instanceIds[ii]);
    }
    return parameterNumber;
  }

  /**
   * Set the bind values of a {@link PreparedStatement} using the values in the
   * supplied collection.
   * 
   * <p><em>Note:</em> The collection type should be an object that is
   * compatible with the {@link PreparedStatement#setObject(int, Object)}
   * method.</p>
   * 
   * @param query A prepared statement.  This should have been constructed
   *        using the corresponding {@link #getInList()} method to ensure that
   *        the appropriate number of parameters exist.
   * @param parameterNumber the PreparedStatement parameter number from which
   *        the values will be set.  E.g. if this value is zero, then the
   *        first parameter will be set for index one.
   * @param collection  The {@link Collection} of objects whose values will be
   *        used to set the {@link PreparedStatement} parameters.
   *
   * @return The last parameter number for which a value was set.
   *
   * @throws SQLException on database error.
   */
  public static int setInListParameters(PreparedStatement query,
                                        int parameterNumber,
                                        Collection<?> collection)
  throws SQLException {
    Validate.notEmpty(collection, "collection should not be empty");
    
    for(Object o: collection) {
      query.setObject(++parameterNumber, o);
    }
    
    return parameterNumber;
  }

  /**
   * Get the Statement --> SQL Strings map for a given connection, which must be
   * in a pool.
   *
   * @param connection  the connection for which to get the map
   * @param logger  where to log errors
   *
   * @return  a map of Statements to query strings
   *
   * @throws SQLException  if the connection is not in this pool
   */
  @SuppressWarnings("unchecked")
private static HashMap getStatementToSqlMap(Connection connection,
                                              Logger logger)
                                       throws SQLException {
    //
    // Assert that this method is only called if we are configured to record
    // statement timing information.
    //
    assert (ConnectionPool.storeTimingInfo);

    //
    // Note that becuase ConnectionPool.connectionStatements is a Hashtable,
    // this method does not have to be synchronized.
    //
    HashMap statementToSqlMap =
                  (HashMap) ConnectionPool.connectionStatements.get(connection);

    return statementToSqlMap;
  }



  /**
   * Get the SQL Strings --> statistics map for a given connection, which must
   * be in a pool.
   *
   * @param connection  the connection for which to get statistics
   * @param logger  where to log errors
   *
   * @return  a map of query strings to TimingInfo objects
   *
   * @throws SQLException  if the connection is not in this pool
   */
  @SuppressWarnings("unchecked")
private static HashMap getSqlToStatisticslMap(Connection connection,
                                                Logger logger)
                                         throws SQLException {
    //
    // Assert that this method is only called if we are configured to record
    // statement timing information.
    //
    assert (ConnectionPool.storeTimingInfo);

    //
    // Note that because ConnectionPool.connectionSqlStrings is a Hashtable,
    // this method does not have to be synchronized.
    //
    HashMap sqlToStatisticslMap =
                  (HashMap) ConnectionPool.connectionSqlStrings.get(connection);

    return sqlToStatisticslMap;
  }
  
  /**
   * Method for obtaining the pool for a given connection
   * @param connection The connection for which the owning pool is required
   * @return The pool from which the supplied connection was obtained
   */
  private static ConnectionPool getPoolForConnection(Connection connection, Logger logger) {
	
	/**
	 * Remove any proxy wrappers
	 */
	connection = stripProxyConnections(connection, logger);
	  
	  
    ConnectionPool pool =
               (ConnectionPool) ConnectionPool.connectionToPool.get(connection);
    
    //
    // if the pool was not found using the connection then try with
    // the apache dbcp conenction string
    //
    
    if(pool == null) {
      pool = (ConnectionPool)ConnectionPool.connectionToPool.get(getApacheDbcpIdentifier(connection));
    }
    
    return pool;
  }


  /**
   * Get the in use information for a connection when we don't know which pool
   * it belongs to.
   *
   * @param connection  the connection for which we want to get the user
   *                    information
   * @return the user information for this connection
   */
  private static InUseConnectionInfo getInfo(Connection connection) {
    
    InUseConnectionInfo info = null;
    
    //
    // This lookup does not need to be synchronized because it is a hastable
    //
    ConnectionPool pool =
               (ConnectionPool) ConnectionPool.connectionToPool.get(connection);
    
    //
    // if the pool was not found using the connection then try with
    // the apache dbcp conenction string
    //
    
    if(pool == null) {
      pool = (ConnectionPool)ConnectionPool.connectionToPool.get(getApacheDbcpIdentifier(connection));
    }
    
    
    //
    // if pool was not returned or the hashmap was not initialised then dont bother
    //
    if(pool != null && pool.connectionToInfo != null) {
      info = pool.getInUseConnectionInfo(connection);
    }
    
    return info;
  }

  private InUseConnectionInfo getInUseConnectionInfo(Connection connection) {
    
    //
    // first, use the connection object to find the info
    //
    InUseConnectionInfo info = (InUseConnectionInfo) this.connectionToInfo.get(connection);
      
    //
    // if the info was not found using the connection then try with
    // the apache dbcp conenction string
    //
    
    if(info == null) {
      info = (InUseConnectionInfo) this.connectionToInfo.get(getApacheDbcpIdentifier(connection));
    }
    
    
    return info;
  }
  

  /**
   * If configured to, keep track of the SQL for which a statement is created
   *
   * @param statement  the statement just created
   * @param sql        the SQL used to create the statement
   * @param connection the connection on which the statement was created
   * @param logger  where to log errors/tracing
   *
   * @throws SQLException  if the connection is not in this pool
   */
  @SuppressWarnings("unchecked")
private static void recordStatementSql(PreparedStatement statement,
                                         String sql,
                                         Connection connection,
                                         Logger logger)
                                  throws SQLException {
    
    InUseConnectionInfo info = ConnectionPool.getInfo(connection);
  
    info.lastQuery = sql;
    info.stackTrace = ConnectionPool.getStackTrace();
    info.lastQueryStarted = null;
    info.lastQueryEnded = null;
  
    //
    // Only record statement timing information if configured to do so.
    //
    if (ConnectionPool.storeTimingInfo) {
      HashMap statementToSqlMap = ConnectionPool.getStatementToSqlMap(connection, logger);
      if(statementToSqlMap != null) {
        statementToSqlMap.put(statement, sql);
      }
    }
    
    return;
  }


  /**
   * Return the timing information for a given statement (creating it and
   * initialising it first if necessary).
   *
   * @param statement  the statement for which to retrieve timing information
   * @param connection  the pooled connection on which the statement was
   *                    executed
   * @param logger  where to log errors/tracing
   * @return  the number of times the statement was run and the total time spent
   *          executing it
   *
   * @throws SQLException  if the connection is not in this pool
   */
  @SuppressWarnings("unchecked")
private static TimingInfo getTimingInfo(PreparedStatement statement,
                                          Connection connection,
                                          Logger     logger)
                                   throws SQLException {
    
    TimingInfo timingInfo = null;
    
    //
    // Assert that this method is only called if we are configured to record
    // statement timing information.
    //
    assert (ConnectionPool.storeTimingInfo);

    //
    // Get the mapping from SQL strings to timing information
    //
    HashMap sqlToStatisticslMap = getSqlToStatisticslMap(connection, logger);

    HashMap statementToSqlMap = ConnectionPool.getStatementToSqlMap(connection, logger);
    
    if(sqlToStatisticslMap != null && statementToSqlMap != null) {
      //
      // Get the SQL string for this statement
      //
      String sqlString = (String)statementToSqlMap.get(statement);
  
      //
      // Find timing information if it exists
      //
      timingInfo = (TimingInfo) sqlToStatisticslMap.get(sqlString);
      if (null == timingInfo) {
        //
        // First time this particular SQL string has been run, so create new
        // timing information.
        //
        timingInfo = new TimingInfo();
        sqlToStatisticslMap.put(sqlString, timingInfo);
      }
    }
    
    return timingInfo;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @see <code>Connection.prepareStatement(String sql)</code>
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn = connection.prepareStatement(sql);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @see <code>Connection.prepareStatement(String sql, int autoGeneratedKeys)
   * </code>
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param autoGeneratedKeys  a flag indicating whether auto-generated keys
   *                           should be returned; one of the following
   *                           Statement constants
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   int autoGeneratedKeys,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn =
                            connection.prepareStatement(sql, autoGeneratedKeys);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param columnIndexes  an array of column indexes indicating the columns
   *                       that should be returned from the inserted row or rows
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   int[] columnIndexes,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn =
      connection.prepareStatement(sql, columnIndexes);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param resultSetType  a result set type; one of
   *                       ResultSet.TYPE_FORWARD_ONLY,
   *                       ResultSet.TYPE_SCROLL_INSENSITIVE, or
   *                       ResultSet.TYPE_SCROLL_SENSITIVE
   * @param resultSetConcurrency  a concurrency type; one of
   *                              ResultSet.CONCUR_READ_ONLY or
   *                              ResultSet.CONCUR_UPDATABLE
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   int resultSetType,
                                                   int resultSetConcurrency,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn =
      connection.prepareStatement(sql, resultSetType, resultSetConcurrency);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param resultSetType  a result set type; one of
   *                       ResultSet.TYPE_FORWARD_ONLY,
   *                       ResultSet.TYPE_SCROLL_INSENSITIVE, or
   *                       ResultSet.TYPE_SCROLL_SENSITIVE
   * @param resultSetConcurrency  a concurrency type; one of
   *                              ResultSet.CONCUR_READ_ONLY or
   *                              ResultSet.CONCUR_UPDATABLE
   * @param resultSetHoldability  one of the following ResultSet constants:
   *                              ResultSet.HOLD_CURSORS_OVER_COMMIT or
   *                              ResultSet.CLOSE_CURSORS_AT_COMMIT
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   int resultSetType,
                                                   int resultSetConcurrency,
                                                   int resultSetHoldability,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn =
      connection.prepareStatement(sql,
                                  resultSetType,
                                  resultSetConcurrency,
                                  resultSetHoldability);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a PreparedStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  an SQL statement that may contain one or more '?' IN parameter
   *             placeholders
   * @param columnNames  an array of column names indicating the columns that
   *                     should be returned from the inserted row or rows
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareStatement()</code>
   *
   * @return a PreparedStatement
   */
  public static PreparedStatement prepareStatement(String sql,
                                                   String[] columnNames,
                                                   Connection connection,
                                                   Logger logger)
                                            throws SQLException {
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    PreparedStatement statementToReturn =
      connection.prepareStatement(sql, columnNames);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a CallableStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  a String object that is the SQL statement to be sent to the
   *             database; may contain on or more ? parameters
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareCall()</code>
   *
   * @return a CallableStatement
   */
  public static CallableStatement prepareCall(String sql,
                                              Connection connection,
                                              Logger logger)
                                       throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the callable statement to return
    //
    CallableStatement statementToReturn = connection.prepareCall(sql);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a CallableStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  a String object that is the SQL statement to be sent to the
   *             database; may contain on or more ? parameters
   * @param resultSetType  a result set type; one of
   *                       ResultSet.TYPE_FORWARD_ONLY,
   *                       ResultSet.TYPE_SCROLL_INSENSITIVE, or
   *                       ResultSet.TYPE_SCROLL_SENSITIVE
   * @param resultSetConcurrency  a concurrency type; one of
   *                              ResultSet.CONCUR_READ_ONLY or
   *                              ResultSet.CONCUR_UPDATABLE
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareCall()</code>
   *
   * @return a CallableStatement
   */
  public static CallableStatement prepareCall(String sql,
                                              int resultSetType,
                                              int resultSetConcurrency,
                                              Connection connection,
                                              Logger logger)
                                       throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    CallableStatement statementToReturn =
      connection.prepareCall(sql, resultSetType, resultSetConcurrency);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Return a CallableStatement after storing the sql String that was used to
   * create it.
   *
   * @param sql  a String object that is the SQL statement to be sent to the
   *             database; may contain on or more ? parameters
   * @param resultSetType  a result set type; one of
   *                       ResultSet.TYPE_FORWARD_ONLY,
   *                       ResultSet.TYPE_SCROLL_INSENSITIVE, or
   *                       ResultSet.TYPE_SCROLL_SENSITIVE
   * @param resultSetConcurrency  a concurrency type; one of
   *                              ResultSet.CONCUR_READ_ONLY or
   *                              ResultSet.CONCUR_UPDATABLE
   * @param resultSetHoldability  one of the following ResultSet constants:
   *                              ResultSet.HOLD_CURSORS_OVER_COMMIT or
   *                              ResultSet.CLOSE_CURSORS_AT_COMMIT
   * @param connection  the pooled database connection to use
   * @param logger      where any errors are to be logged
   *
   * @throws SQLException  in the same circumstances as
   *                       <code>Connection.prepareCall()</code>
   *
   * @return a CallableStatement
   */
  public static CallableStatement prepareCall(String sql,
                                              int resultSetType,
                                              int resultSetConcurrency,
                                              int resultSetHoldability,
                                              Connection connection,
                                              Logger logger)
                                       throws SQLException {
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);
  	
    //
    // Make the prepared statement to return
    //
    CallableStatement statementToReturn =
      connection.prepareCall(sql,
                             resultSetType,
                             resultSetConcurrency,
                             resultSetHoldability);

    recordStatementSql(statementToReturn, sql, connection, logger);

    return statementToReturn;
  }


  /**
   * Execute a PreparedStatement and store the time it took in the statements
   * HashMap.
   *
   * @see <code>PreparedStatement.executeQuery()</code>
   *
   * @param statement   the statement to execute
   * @param connection  the connection on which to execute it
   * @param logger      where any errors are to be logged
   *
   * @return ResultSet as for <code>PreparedStatement.executeQuery()</code>
   *
   * @throws SQLException in the same circumstances as
   * <code>PreparedStatement.executeQuery()</code> or if trying to use a
   * <code>PreparedStatement</code> not created by <code>ConnectionPool</code>
   * for this <code>Connection</code>.
   */
  public static ResultSet executeQuery(PreparedStatement statement,
                                       Connection        connection,
                                       Logger            logger)
                                throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);

    //
    // Execute and, if configured to, time the statement
    //
    long startTime = System.currentTimeMillis();
    InUseConnectionInfo info = ConnectionPool.getInfo(connection);
    info.lastQueryStarted = new Date();
    info.lastQueryEnded = null;
    ResultSet result = statement.executeQuery();
    info.lastQueryEnded = new Date();  

    if(ConnectionPool.storeTimingInfo) {
      TimingInfo timingInfo = getTimingInfo(statement, connection, logger);
      if(timingInfo != null) {
        timingInfo.totalExecutionTime += System.currentTimeMillis() - startTime;
        ++timingInfo.numberOfTimesRun;
      }
    } 
    
    return result;
  }


  /**
   * Execute a PreparedStatement and store the time it took in the statements
   * HashMap.
   *
   * @param statement   the statement to execute
   * @param connection  the connection on which to execute it
   * @param logger      where any errors are to be logged
   * @return same as <code>PreparedStatement.executeUpdate()</code>
   *
   * @throws SQLException in the same circumstances as
   * <code>PreparedStatement.executeUpdate()</code> or if trying to use a
   * <code>PreparedStatement</code> not created by <code>ConnectionPool</code>
   * for this <code>Connection</code>.
   */
  public static int executeUpdate(PreparedStatement statement,
                                  Connection        connection,
                                  Logger            logger)
                           throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);


    //
    // Execute and, if configured to, time the statement
    //
    long startTime = System.currentTimeMillis();
    InUseConnectionInfo info = ConnectionPool.getInfo(connection);
    info.lastQueryStarted = new Date();
    info.lastQueryEnded = null;
    int result = statement.executeUpdate();
    info.lastQueryEnded = new Date();
    
    if(ConnectionPool.storeTimingInfo) {
      TimingInfo timingInfo = getTimingInfo(statement, connection, logger);
      if(timingInfo != null) {
        timingInfo.totalExecutionTime += System.currentTimeMillis() - startTime;
        ++timingInfo.numberOfTimesRun;
      }
    }

    return result;
  }


  /**
   * Execute a PreparedStatement and store the time it took in the statements
   * HashMap.
   *
   * @param statement   the statement to execute
   * @param connection  the connection on which to execute it
   * @param logger      where any errors are to be logged
   * @return same as <code>PreparedStatement.execute()</code>
   *
   * @throws SQLException in the same circumstances as
   * <code>PreparedStatement.execute()</code> or if trying to use a
   * <code>PreparedStatement</code> not created by <code>ConnectionPool</code>
   * for this <code>Connection</code>.
   */
  public static boolean execute(PreparedStatement statement,
                                Connection        connection,
                                Logger            logger)
                         throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);


    //
    // Execute and, if configured to, time the statement
    //
    long startTime = System.currentTimeMillis();
    InUseConnectionInfo info = ConnectionPool.getInfo(connection);
    info.lastQueryStarted = new Date();
    info.lastQueryEnded = null;
    boolean result = statement.execute();
    info.lastQueryEnded = new Date();

    if(ConnectionPool.storeTimingInfo) {
      TimingInfo timingInfo = getTimingInfo(statement, connection, logger);
      if(timingInfo != null) {
        timingInfo.totalExecutionTime += System.currentTimeMillis() - startTime;
        ++timingInfo.numberOfTimesRun;
      }
    }

    return result;
  }


  /**
   * If we are configured to record statistics, return a <code>HashMap</code>
   * containing average execution times in milliseconds for all the statements
   * executed on this connection.  The SQL strings for the queries are the
   * HashMap keys and the average execution times in ms are the HashMap values.
   *
   * It is intended that we only call this just before returning the connection
   * to the pool, so we make it an instance method rather than a static one.
   *
   * @param connection  the connection for which to get statistics
   * @param logger  where to log errors etc
   *
   * @return  the <code>HashMap</code> containing average execution times if we
   *          are configured to record statistics, or null otherwise
   *
   * @throws SQLException if the connection is not in this pool
   */
  @SuppressWarnings("unchecked")
public HashMap getStatementTimings(Connection connection,
                                     Logger     logger)
                              throws SQLException {
  	
  	//
  	// Ensure the supplied connection is the underlying one
  	//
  	connection = stripProxyConnections(connection, logger);

    HashMap statisticsToReturn = null;

    //
    // Check whether we are recording statistics before trying to return them
    //
    if (ConnectionPool.storeTimingInfo) {
      //
      // Find the set of SQL strings executed on this connection
      //
      HashMap sqlToStatisticslMap = getSqlToStatisticslMap(connection, logger);

      if(sqlToStatisticslMap != null) {
        statisticsToReturn = new HashMap();
        Iterator statementIterator = sqlToStatisticslMap.keySet().iterator();
        while (statementIterator.hasNext()) {
          String sqlString = (String) statementIterator.next();
          TimingInfo timing = (TimingInfo) sqlToStatisticslMap.get(sqlString);
  
          //
          // Log at INFO level because we're only logging if storeTimingInfo is
          // true.
          //
          logger.log(
            Level.INFO,
            "Spent " + timing.totalExecutionTime +
            "ms in total executing query \"" + sqlString + "\" " +
            timing.numberOfTimesRun + " time(s)."
          );
          if (timing.numberOfTimesRun > 0) {
            statisticsToReturn.put(
              sqlString,
              new Long(timing.totalExecutionTime / timing.numberOfTimesRun)
            );
          }
        }
  
        logger.log(Level.DEBUG,
                   "Number of timings = " + statisticsToReturn.size());
      }
    }

    return statisticsToReturn;

  }


  /**
   * Get a list of all the users of this pool's connections and how many
   * connections they each have.
   *
   * @return  a map of user (logger) names to number of connections held
   */
  @SuppressWarnings("unchecked")
public HashMap getUsersToNumConnections() {

    HashMap usersToNumConnections = new HashMap();
    Integer numConnections = null;
    String user = null;

    //
    // create a temp array list so that we dont need to synchronize the connectionToInfo map
    //
    Collection temp = new ArrayList();
    
    temp.addAll(this.connectionToInfo.values());

    for (Iterator ii = temp.iterator();ii.hasNext();) {
      user = ((InUseConnectionInfo) ii.next()).user;
      numConnections = (Integer) usersToNumConnections.get(user);
      if (null == numConnections) {
        numConnections = new Integer(1);
      } else {
        numConnections = new Integer(numConnections.intValue() + 1);
      }

      usersToNumConnections.put(user, numConnections);
    }

    return usersToNumConnections;

  }

  /**
   * Get a list of all the users of this pool's connections and the date
   * when the last query ended.
   *
   * @return  a map of user (logger) names to the date when the last query ended.
   */
  @SuppressWarnings("unchecked")
public HashMap getUsersToLastQueryEndedDate() {

    HashMap usersToNumConnections = new HashMap();
    InUseConnectionInfo info = null;
    String user = null;
    String endDate = null;

    //
    // create a temp array list so that we dont need to synchronize the connectionToInfo map
    //
    Collection temp = new ArrayList();
    
    temp.addAll(this.connectionToInfo.values());
    
    for (Iterator ii = temp.iterator();ii.hasNext();) {
      info = ((InUseConnectionInfo) ii.next());
      user = info.user;
      //
      // The ended date may not be setas the object is added to the collection with it set to null
      //
      endDate = null != info.lastQueryEnded ? DATE_FORMAT.format(info.lastQueryEnded) : "";
      usersToNumConnections.put(user, endDate);
    }

    return usersToNumConnections;
  }

  /**
   * Get a list of all the users of this pool's connections and the date
   * when the last query started.
   *
   * @return  a map of user (logger) names to the date when the last query started.
   */
  @SuppressWarnings("unchecked")
public HashMap getUsersToLastQueryStartDate() {

    HashMap usersToNumConnections = new HashMap();
    InUseConnectionInfo info = null;
    String user = null;
    String startDate = null;

    //
    // create a temp array list so that we dont need to synchronize the connectionToInfo map
    //
    Collection temp = new ArrayList();
    
    temp.addAll(this.connectionToInfo.values());
    
    for (Iterator ii = temp.iterator();ii.hasNext();) {
      info = ((InUseConnectionInfo) ii.next());
      user = info.user;
      //
      // The start date may not be set as the object is added to the collection with it set to null
      //
      startDate = null != info.lastQueryStarted ? DATE_FORMAT.format(info.lastQueryStarted) : "";

      usersToNumConnections.put(user, startDate);
    }

    return usersToNumConnections;
  }


  /**
   * Get a list of information about the connections in use from this pool
   *
   * @return a set of {@link #InUseConnectionInfo} objects containing
   *         information about in-use connections
   */
  @SuppressWarnings("unchecked")
public HashSet getInUseInfo() {
    HashSet connectionsInfo = new HashSet();
    
    //
    // create a temp array list so that we dont need to synchronize the connectionToInfo map
    //
    Collection temp = new ArrayList();
    
    temp.addAll(this.connectionToInfo.values());
    
    for (Iterator ii = temp.iterator(); ii.hasNext();) {
      connectionsInfo.add(ii.next());
    }

    return connectionsInfo;
  }

  /**
   * he database name for this pool
   *
   * @return The dbName of this pool.
   *
   */
   public String getdbName() {
    return this.dbName;
   }

   /**
  * Get the jdbcUser of this pool.
  *
  * @return The jdbcUser of this pool.
  *
  */
  public String getJdbcUser() {
    return this.jdbcUser;
  }

  /**
   * Get the the number of free connections of this pool.
   *
   * @return The number of the free connections of this pool.
   *
   */
  public int getFreeConnectionsInt() {
    int size = 0;
    if(!this.apacheDbcpEnabled) {
      synchronized(this.freeConnections) {
        size = this.freeConnections.size();
      }
    } else {
      size =  objectPool.getNumIdle();
    }
    return size;
  }

  /**
   * Get the the number of connections in use.
   *
   * @return The number of connections in use.
   *
   */
  public int getConnectionsInUse() {
  	if (!apacheDbcpEnabled) {
  		return this.numConnectionsInUse;
  	} else {
  		return objectPool.getNumActive();
  	}
  }

  /**
   * Returns a count of all the connection pools that have been created
   * @return a count of all the connection pools that have been created
   */
  public int getConnectionPoolCreated() {
    int size = 0;
    synchronized(ConnectionPool.instances) {
      ConnectionPool.instances.size();
    }
    return size;
  }

  @SuppressWarnings("unchecked")
public ArrayList getConnectionPoolToDBNames() {

    ArrayList list = new ArrayList();
    synchronized(ConnectionPool.instances) {
      list.addAll(ConnectionPool.instances.keySet());
    }
    return list;
  }

  @SuppressWarnings("unchecked")
public ArrayList getConnectionPoolInstances() {

    ArrayList list = new ArrayList();
    synchronized(ConnectionPool.instances) {
      list.addAll(ConnectionPool.instances.values());
    }
    return list;
  }

  @SuppressWarnings("unchecked")
public List getFailedConnectionPoolInstances() {
    ArrayList list = new ArrayList();
    synchronized(ConnectionPool.failedPools) {
      list.addAll(ConnectionPool.failedPools);
    }
    return list;
  }

  /**
   * Get the the max number of connections of this pool.
   *
   * @return The max number of connections of this pool.
   *
   */
  public int getMaxConnections() {
    return jdbcMaxConnections;
  }


  /**
   * Get the the min number of connections of this pool.
   *
   * @return The min number of connections of this pool.
   *
   */
  public int getMinConnections() {
    return jdbcMinConnections;
  }

  /**
   * Get the number of seconds to keep a connection open
   * @return
   */
  public int getConnectionExpiryInSeconds() {
    return this.jdbcMaxSecondsToKeepConnection;
  }

  /**
   * @return the lastSuccessfulConnectionRequest
   */
  public Date getLastSuccessfulConnectionRequest() {
    return lastSuccessfulConnectionRequest;
  }



  /**
   * @return the numberOfFailuresSinceLastSuccess
   */
  public int getNumberOfFailuresSinceLastSuccess() {
    return numberOfFailuresSinceLastSuccess;
  }


  /**
   * @return the MaxFailuresBeforeFlush
   */
  public int getMaxFailuresBeforeFlush() {
    return jdbcMaxFailuresBeforeFlush;
  }

  /**
   * 
   * @return whether the pool is using apache dbcp
   */
  public boolean isApacheDbcpEnabled() {
    return apacheDbcpEnabled;
  }
  


  /**
   * @return the jdbcAutoFlushing
   */
  public boolean isJdbcAutoFlushing() {
    return jdbcAutoFlushing;
  }


  /**
   * @return the jdbcFlushable
   */
  public boolean isJdbcFlushable() {
    return jdbcFlushable;
  }


  /**
   * @return the jdbcTestFlushing
   */
  public boolean isJdbcTestFlushing() {
    return jdbcTestFlushing;
  }


  /**
   * @param jdbcAutoFlushing the jdbcAutoFlushing to set
   */
  public void setJdbcAutoFlushing(boolean jdbcAutoFlushing) {
    this.jdbcAutoFlushing = jdbcAutoFlushing;
  }



  /**
   * @param jdbcFlushable the jdbcFlushable to set
   */
  public void setJdbcFlushable(boolean jdbcFlushable) {
    this.jdbcFlushable = jdbcFlushable;
  }



  /**
   * @param jdbcTestFlushing the jdbcTestFlushing to set
   */
  public void setJdbcTestFlushing(boolean jdbcTestFlushing) {
    this.jdbcTestFlushing = jdbcTestFlushing;
  }

  /**
   * @return The database dialect used for this connection pool
   */
  public DatabaseDialect getDatabaseDialect() {
	return databaseDialect;
  }
  
  /**
   * Returns the default database dialect
   */
  protected static DatabaseDialect getDefaultDatabaseDialect() {
	  return DEFAULT_DATABASE_DIALECT;
  }

  /**
   * @param databaseDialect The database dialect used for this connection pool
   */
  protected void setDatabaseDialect(DatabaseDialect databaseDialect) {
	this.databaseDialect = databaseDialect;
  }


 /**
   * Get a connection from the pool, optionally with logging
   *
   * @param logger  the logger to which errors and tracing will be logged
   * @return a pooled connection
   *
   * @throws java.sql.SQLException if the maximum number of connections for this
   * pool is already in use
   */
  public Connection getConnection(Logger logger) throws SQLException {
    return this.getConnection(false, logger);
  }


  /**
   * Get a connection from the pool, optionally with logging
   *
   * @param returnNullIfPoolEmpty  if true and the pool is empty then return
   *                               null instead of throwing a SQLException
   * @param logger  the logger to which errors and tracing will be logged
   * @return a pooled connection
   *
   * @throws java.sql.SQLException if the maximum number of connections for this
   * pool is already in use
   */
  @SuppressWarnings("unchecked")
public Connection getConnection(boolean returnNullIfPoolEmpty, Logger logger)
                                  throws SQLException {
    
    //
    // Find out which application is getting the connection for logging /
    // debugging.
    //
    String connectionUser = logger.getName();

    //
    // get the class, method and line number that called this method
    //
    String callingClass = getCallingClass();

    Connection connectionToReturn = null;
    
    if(!this.apacheDbcpEnabled) {
  
      //
      // This will only loop around if it has got an expired connection or if
      // a refill was required. If none exist in the pool and the pool is maxed
      // out then an exception will be thrown that exits this loop.
      //
      while (null == connectionToReturn) {
  
        synchronized(this.freeConnections) {
  
            if (this.freeConnections.size() <= 0) {
            //
            // No free connections.  See whether we can create another one
            //
            if (this.numConnectionsInUse < this.jdbcMaxConnections) {
              //
              // If we've fallen below the minimum number of connections, bring us
              // back up to that level.
              //
              int numConnectionsToCreate;
              if (this.numConnectionsInUse < this.jdbcMinConnections) {
                numConnectionsToCreate =
                  this.jdbcMinConnections - this.numConnectionsInUse;
              } else {
                numConnectionsToCreate = 1;
              }
  
              this.createConnections(numConnectionsToCreate, logger);
  
              if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
                CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + " CREATED an additional " + numConnectionsToCreate + " connections for the pool. " +
                                             "inUse = " + this.numConnectionsInUse +
                                             ", free = " + this.freeConnections.size());
              }
  
            } else {
  
              //
              // record that a failure has taken place
              //
              this.numberOfFailuresSinceLastSuccess++;
  
              //
              // Count how many connections each user has...
              //
              HashMap usersToNumConnections = this.getUsersToNumConnections();
  
              //
              // ...and build a string showing it.
              //
              StringBuilder connectionsUsedBy = new StringBuilder();
              for (Iterator ii = usersToNumConnections.keySet().iterator(); ii.hasNext();) {
  
                String user = (String) ii.next();
                connectionsUsedBy.append(user);
                connectionsUsedBy.append(" (using ");
                connectionsUsedBy.append(
                  ((Integer) usersToNumConnections.get(user)).toString()
                );
                if (ii.hasNext()) {
                  connectionsUsedBy.append("), ");
                } else {
                  connectionsUsedBy.append(").");
                }
              }
  
              if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
                CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser +
                                             " No more free connections: " + this.numConnectionsInUse +
                                             " already in use by " + connectionsUsedBy);
              }
  
              logger.log(
                Level.ERROR,
                "No more free connections: " + this.numConnectionsInUse+
                " already in use by " + connectionsUsedBy +
                "  (Getting connection for " + connectionUser + ".)"
              );
  
              if(this.jdbcFlushable && this.jdbcAutoFlushing) {
                //
                // if no connections are available then check to see that the pool
                // has not run out irrepairably and therefore needs refreshing
                //
                this.checkForTrouble();
              }
  
              //
              // break out of this loop
              //
              if (returnNullIfPoolEmpty) {
                break;
              } else {
                throw new SQLException(
                  "No more free connections in pool \"" + this.dbName + "\": " +
                  this.numConnectionsInUse + " already in use by " +
                  connectionsUsedBy
                );
              }
            }
          }
  
          connectionToReturn =
                 (Connection)this.freeConnections.remove(this.freeConnections.size() - 1);
  
          this.numConnectionsInUse++;
        }
  
        //
        // Don't return a connection that is past its expiry date
        //
        if (this.isConnectionExpired(connectionToReturn)) {
          if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + connectionToReturn.hashCode() +
                                         " Closing connection in pool as older than " +
                                         this.jdbcMaxSecondsToKeepConnection + " seconds.");
          }
  
          try {
			connectionToReturn.close();
		  } catch (SQLException e) {
		   logger.error("Error closing the connection", e);
		  }
        } else {
          //
          // If this is not a newly created connection, perform a rudimentary
          // check that it is still open.
          //
          // Note the following from java.sql.Connection interface documentation:
          //
          //   "A connection is closed if the method close has been called on it
          //   or if certain fatal errors have occurred. This method is guaranteed
          //   to return true only when it is called after the method
          //   Connection.close has been called.
          //
          //   "The isClosed() method generally cannot be called to determine
          //   whether a connection to a database is valid or invalid.  A typical
          //   client can determine that a connection is invalid by catching any
          //   exceptions that might be thrown when an operation is attempted."
          //
          // We therefore execute a minimal SQL statement on the connection to
          // check that it is valid before returning it to the caller.
          //
          // .:TBC:. MY 28/07/2003 We could consider only doing this check every
          // N minutes to reduce the overhead of using the pool...
          //
          try {
            PreparedStatement ps =
              connectionToReturn.prepareStatement(getDatabaseDialect().getConnectionValidationQuery());
            ResultSet rs = ps.executeQuery();
            logger.log(Level.DEBUG, "Checked DB connection OK");
            rs.close();
            ps.close();
            rs = null;
            ps = null;
          } catch (SQLException sqle) {
            if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
              CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + connectionToReturn.hashCode() +
                                           " Found invalid connection in pool : " + sqle.getMessage() +
                                           " Closing connection.");
            }
            try {
            	connectionToReturn.close();
		    } catch (SQLException e) {
		     logger.error("Error closing the connection", e);
		    }
          }
        }
  
        if (connectionToReturn.isClosed()) {
          if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + connectionToReturn.hashCode() +
                                         " Found closed connection in pool.  Trying to get another one.");
          }
          this.destroyConnection(connectionToReturn, logger);
          connectionToReturn = null;
  
          //
          // drop down the number of connections in use
          //
          synchronized(this.freeConnections) {
            this.numConnectionsInUse--;
          }
        }
      }
  
      if (null != connectionToReturn) {
  
        this.lastSuccessfulConnectionRequest = new Date();
        this.numberOfFailuresSinceLastSuccess = 0;
  
        //
        // Add the HashMaps in which to store statistics for this connection's
        // statements
        //
        if (ConnectionPool.storeTimingInfo) {
          ConnectionPool.connectionStatements.put(connectionToReturn,
                                                  new HashMap());
          ConnectionPool.connectionSqlStrings.put(connectionToReturn,
                                                  new HashMap());
        }       
        
        //
        // Initialise current user information about the connection we are giving out.
        // This will overwrite the value that was previously held for this user.
        //
        this.connectionToInfo.put(connectionToReturn,
                                  new InUseConnectionInfo(connectionUser,
                                                          callingClass,
                                                          connectionToReturn.hashCode()));
  
        if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
          CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + connectionToReturn.hashCode() + " GOT " + callingClass);
        }
      }
    } else {
      
      try {
        
        connectionToReturn = this.dbcpDataSource.getConnection();
        
        //
        // It looks like something strange happens with the equals method
        // in the connection. It can never find it, so I am going to use the
        // hashcode string instead.
        //
        Object connectionId = getApacheDbcpIdentifier(connectionToReturn);
        
        //
        // if the connection is not yet in the pool map then add it
        //
        if(!ConnectionPool.connectionToPool.containsKey(connectionId)) {
          ConnectionPool.connectionToPool.put(connectionId, this);
        }

        //
        // Initialise current user information about the connection we are giving out.
        // This will overwrite the value that was previously held for this user.
        //
        this.connectionToInfo.put(connectionId,
                                  new InUseConnectionInfo(connectionUser,
                                                          callingClass,
                                                          connectionToReturn.hashCode()));
        
        //this.numConnectionsInUse++;
      	this.lastSuccessfulConnectionRequest = new Date();
      	this.numberOfFailuresSinceLastSuccess = 0;
        
      } catch(Exception e) {
        this.numberOfFailuresSinceLastSuccess++;
        logger.error("No more free connections : " + this.dbName + " / " + this.jdbcUser + 
                     ", " + this.getConnectionsInUse() + " connections in use, " + numberOfFailuresSinceLastSuccess + 
                     " failures since last success : " + e.getMessage(), e);
        throw new SQLException("No more free connections : " + this.dbName + " / " + this.jdbcUrl + " : " + e.getMessage());
      }
    }
    
    return connectionToReturn;
  }


  /**
   * Create new connections and add them to the pool
   *
   * @param numConnections  number of connections to create
   * @param logger  where to log errors etc
   *
   * @throws SQLException  if there was a problem creating the connection(s)
   */
  @SuppressWarnings("unchecked")
private void createConnections(int numConnections,
                                 Logger logger) throws SQLException {

    //
    // Decrypt the key before we hand it to the JDBC driver
    //
    BlowfishEcb bfEcb = new BlowfishEcb(DATABASE_PASSWORD_ENCRYPTION_KEY);

    byte[] passwordBytes = new byte[this.jdbcEncryptedPassword.length];

    bfEcb.decrypt(this.jdbcEncryptedPassword, passwordBytes);
    String passwordString = new String(passwordBytes);

    bfEcb.cleanUp();

    for (int connectionNumber = 0; connectionNumber < numConnections; connectionNumber++) {
      Connection newConnection = DriverManager.getConnection(
        this.jdbcUrl,
        this.jdbcUser,
        passwordString.trim()
      );
      if (newConnection.isClosed()) {
        throw new SQLException("New connection was closed when created!");
      }

      synchronized(this.freeConnections) {
        this.freeConnections.add(newConnection);
      }

      //
      // Add the lifetime for connections in this pool to the current time and
      // store it in a hash map as the expiry time for this connection.
      //
      this.connectionToExpiryTime.put(newConnection,
                                      new java.util.Date(
                                        new java.util.Date().getTime() +
                                        (MILLISECONDS_PER_SECOND * this.jdbcMaxSecondsToKeepConnection)
                                      )
      );
      
      //
      // Add the look-up entry to allow us to get back to the pool given only
      // the connection. This is a hashtable so doesn't need to be synchronized
      //
      ConnectionPool.connectionToPool.put(newConnection, this);
    }

    return;
  }


  /**
   * Check whether a connection is past its expiry time
   *
   * @param connection  the connection in question
   * @return true  if the connection is past its expiry time
   */
  private boolean isConnectionExpired(Connection connection) {
    boolean ret = false;
    ret = ((java.util.Date)this.connectionToExpiryTime.get(connection)).before(new java.util.Date());
    return ret;
  }


  /**
   * Delete all information we hold about a connection
   *
   * @param connection  the connection in question
   * @param logger  where to log a warning if we have no record of the
   *                connection's expiry time
   * @throws SQLException if there is an error when we try to close the
   *                      connection
   */
  private void destroyConnection(Connection connection,
                                 Logger     logger) throws SQLException {
    //
    // We should have a record of the expiry time of each connection we create.
    // Check this is so for this connection and remove it.
    //
    if (null == this.connectionToExpiryTime.remove(connection)) {
      logger.log(Level.WARN, "Could not find expiry time for connection " + connection);
    }

    //
    // We should have a record of the owning pool for each connection we create.
    // Check this is so for this connection and remove it.
    //
    if (null == ConnectionPool.connectionToPool.remove(connection)) {
      logger.log(Level.WARN,
                 "Could not find pool for connection " + connection);
    }

    //
    // If the connection was free, remove it from the list of free ones
    //
    synchronized(this.freeConnections) {
      this.freeConnections.remove(connection);
    }

    //
    // If the connection was in use, remove it from the mapping of connections
    // to connection user information.
    //
    this.connectionToInfo.remove(connection);

    //
    // Close the connection if this wasn't done already.  Do this last as,
    // unlike any of the code above it might throw a SQLException
    //
    if (!connection.isClosed()) {
        try {
        	connection.close();
	    } catch (SQLException e) {
	     logger.error("Error closing the connection", e);
	    }
    }

    return;
  }


  /**
   * Return a conection to the pool, optionally with logging
   *
   * @param returnedConnection  the connection to return
   * @param logger  the logger to which errors and tracing will be logged.
   *
   * @throws java.sql.SQLException
   *         if too many connections are returned to a pool (ie more than were
   *         taken out) or if a connection is closed when it is returned.
   */
  @SuppressWarnings("unchecked")
public void returnConnection(Connection returnedConnection,
                               Logger logger) throws SQLException {

    if(returnedConnection != null) {
    	
    	//
    	// Ensure the connection sin't wrapped in a delegate
    	//
    	returnedConnection = stripProxyConnections(returnedConnection, logger);
      
      String returnersName = logger.getName();
      
      //
      // Remove and check the information about this connection
      //
      InUseConnectionInfo connectionInfo = this.getInUseConnectionInfo(returnedConnection);
      
      //
      // set the in use flag to false
      //
      connectionInfo.inUse = false;
      
      if(!apacheDbcpEnabled) {
  
        if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
          CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                       " RETURN REQUEST " + getCallingClass());
        }
  
        //
        // Delete all timing related stuff we have for this connection
        //
        if (ConnectionPool.storeTimingInfo &&
            ((null ==
              ConnectionPool.connectionStatements.remove(returnedConnection)) ||
             (null ==
              ConnectionPool.connectionSqlStrings.remove(returnedConnection)))) {
  
          if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                         " ERROR Could not find connection statistics maps when returning " +
                                         "connection to for " + returnersName);
          }
  
          //
          // this connection is lost - may as well close it
          //
          try {
			returnedConnection.close();
		  } catch (SQLException e) {
			logger.error("Error closing connection", e);
		  }
          
  
          throw new SQLException(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                 " Could not find connection statistics maps when returning " +
                                 "connection to for " + returnersName);
        }
  
        if (null == connectionInfo) {
          if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                         " LOST Could not find the information about connection " +
                                         "being returned to pool by " + returnersName);
          }
  
          //
          // this connection is lost - may as well close it
          //
          try {
  			returnedConnection.close();
  		  } catch (SQLException e) {
  			logger.error("Error closing connection", e);
  		  }
  
          throw new SQLException(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                 " Could not find the information about connection " +
                                 "being returned to pool  by " + returnersName);
        }
  
  
        //
        // Check the returner and the person who took the connection are the same
        //
        if (!returnersName.equals(connectionInfo.user)) {
          if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                         " LOST Returner's name (" + returnersName + ") does not " +
                                         "match taker's name (" + connectionInfo.user +") ");
          }
  
          //
          // this connection is lost - may as well close it
          //
          try {
  			returnedConnection.close();
  		  } catch (SQLException e) {
  			logger.error("Error closing connection", e);
  		  }
  
          throw new SQLException(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                 " Returner's name (" + returnersName + ") does not " +
                                 "match taker's name (" + connectionInfo.user +") ");
        }
  
        //
        // The caller should not have closed the connection -- because we want to
        // be able to reuse it.
        //
        if (returnedConnection.isClosed()) {
  
          if (this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                         " CLOSED because connection was closed when returned to pool. " + getCallingClass());
          }
  
          this.destroyConnection(returnedConnection, logger);
          returnedConnection = null;
          synchronized(this.freeConnections) {
            this.numConnectionsInUse--;
          }
  
        } else if (this.isConnectionExpired(returnedConnection)) {
          //
          // Don't bother putting the connection back in the pool if it is past its
          // expiry date
          //
          if (this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
            CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                         " EXPIRED because older than " + this.jdbcMaxSecondsToKeepConnection +
                                         " seconds. " + getCallingClass());
          }
  
          this.destroyConnection(returnedConnection, logger);
          returnedConnection = null;
          synchronized(this.freeConnections) {
            this.numConnectionsInUse--;
          }
  
        } else {
          //
          // Clean up the connection before we return it to the pool
          //
          if (!returnedConnection.getAutoCommit()) {
            returnedConnection.rollback();
            returnedConnection.setAutoCommit(true);
          }
          returnedConnection.clearWarnings();
  
          //
          // Put the connection back in the pool
          //
          synchronized(this.freeConnections) {
            this.freeConnections.add(returnedConnection);
            this.numConnectionsInUse--;
  
            if(this.jdbcInDepthLogging && CONNECTION_POOL_LOGGER.isDebugEnabled()) {
              CONNECTION_POOL_LOGGER.debug(HOSTNAME + "_" + this.jdbcUser + "_" + returnedConnection.hashCode() +
                                           " RETURN SUCCESS " + getCallingClass() +
                                           " : inUse = " + this.numConnectionsInUse +
                                           ", free = " + this.freeConnections.size());
            }
          }
        }
      } else {
        
        //
        // doesn't really close the connection, just returns it to the pool
        //
    	  try {
  			returnedConnection.close();
  		  } catch (SQLException e) {
  			logger.error("Error closing connection", e);
  		  }
      	//synchronized(this.freeConnections) {
        //  this.numConnectionsInUse--;
      	//}
      }
      
    } else {
      logger.error("Tried to return a null connection.");
    }

    return;
  }


  /**
   * Pass in a string parameter to a callable statement (ie stored procedure),
   * converting Java empty strings to database nulls.
   *
   * @param statement <code>PreparedStatement</code> that contains the
   * <code>String</code> parameter to set.
   * @param parameterNumber integer position of the paramater within the
   * <code>PreparedStatement</code>
   * @param value Value that you wish to set against the parameter
   * @throws SQLException for when a database error occurs.
   */
  public static void setPreparedStatementString(PreparedStatement statement,
                                                int parameterNumber,
                                                String value)
                                         throws SQLException {
    if (null == value) {
      statement.setNull(parameterNumber, Types.VARCHAR);
    } else {
      statement.setString(parameterNumber, value);
    }
    return;
  }


  /**
   * Get a date and time as <code>java.sql.Date</code>) from a result set.
   * Since oracle only operates to second precision - we lose any
   * sub second (nano) component of the time.
   *
   * @param results  the result set from which to get the date and time
   * @param columnName  the name of the column containing the date and time
   *
   * @return  the value of the specified column for the current row of the
   *          supplied result set
   *
   * @throws SQLException if a database error occurs
   */
  public static java.sql.Date getSqlDateAndTime(ResultSet results,
                                    String    columnName)
                             throws SQLException {
    //
    // See Timestamp javadoc for why we can't just downcast to a Date
    //
    java.sql.Date dateAndTime;
    Timestamp timestamp = results.getTimestamp(columnName);
    if (results.wasNull()) {
      dateAndTime = null;
    } else {
      timestamp.setNanos(0);
      dateAndTime = new java.sql.Date(timestamp.getTime());
    }
    return dateAndTime;
  }

  /**
   * Get a date and time (<code>java.util.Date</code>) rather than just a date
   * (<code>java.sql.Date</code>) from a result set. Since oracle only
   * operates to second precision - we lose any sub second (nano) component
   * of the time.
   *
   * @param results  the result set from which to get the date and time
   * @param columnName  the name of the column containing the date and time
   *
   * @return  the value of the specified column for the current row of the
   *          supplied result set
   *
   * @throws SQLException if a database error occurs
   */
  public static Date getDateAndTime(ResultSet results,
                                    String    columnName)
                             throws SQLException {
    //
    // See Timestamp javadoc for why we can't just downcast to a Date
    //
    Date dateAndTime;
    Timestamp timestamp = results.getTimestamp(columnName);
    if (results.wasNull()) {
      dateAndTime = null;
    } else {
      timestamp.setNanos(0);
      dateAndTime = new Date(timestamp.getTime());
    }
    return dateAndTime;
  }

  /**
   * This method can be used to avoid receiving a null from an empty column
   * value. If the column value is null then you will return an empty string.
   * If the column value is not null then for good measure this method will trim
   * the returned string as well so as to get rid of any extra blank spaces.
   *
   * @param results the result set
   * @param columnName the name of the column who's value you want
   * @return either an empty string or a trimmed version of the column value
   * @throws SQLException
   */
  public static String getString(ResultSet results,
                                 String columnName)
                                 throws SQLException {

    // get the column value - if the column value was null then ret == null
    String ret = results.getString(columnName);

    // never return a null - return an empty string instead and also trim it
    ret = (null == ret ? "" : ret.trim());

    return ret;
  }

  /**
   * Set a date and time (<code>java.util.Date</code>) rather than just a date
   * (<code>java.sql.Date</code>) as a SQL query parameter. Set the nanossecond
   * part of the query to 0 as oracle only holds data to second precision
   *
   * @param query  the query for which to set the date and time
   * @param parameterIndex  the number of the parameter to set (first is 1,
   *                        second is 2, etc)
   * @param date  the date and time to set
   *
   * @throws SQLException if a database error occurs
   */
  public static void setDateAndTime(PreparedStatement query,
                                    int               parameterIndex,
                                    Date              date)
                             throws SQLException {
    if (null == date) {
      query.setNull(parameterIndex, Types.TIMESTAMP);
    } else {
      Timestamp dateAsTStamp = new Timestamp(date.getTime());
      dateAsTStamp.setNanos(0);
      query.setTimestamp(parameterIndex, dateAsTStamp);
    }

    return;
  }


  /**
   * Returns from the database the next sequence number, given a sequence name.
   *
   * @param sequenceName  The name of the sequence to get the next sequence
   *                      number from.
   * @param con  Database Connection
   * @param logger  Logger
   *
   * @return  The next sequence number for the sequence provided.
   *
   * @throws SQLException if a database access error occurs.
   */
  public static int getNextSequenceNumber(String sequenceName,
                                          Connection con,
                                          Logger logger)
                                   throws SQLException {

	ConnectionPool pool = getPoolForConnection(con, logger);
	
	if (null != pool) { 
		return pool.getDatabaseDialect().getNextSequenceNumber(sequenceName, con, logger);
	} else {
		logger.error("Unable to determine pool for connection " + con +", using default database dialect " + getDefaultDatabaseDialect());
		return getDefaultDatabaseDialect().getNextSequenceNumber(sequenceName, con, logger);
	}
	  
  }


  /**
   * Get a <code>java.lang.String</code> value from a CLOB column in
   * a result set.
   *
   * @param results  the result set from which to get the string
   * @param columnName  the name of the column containing the string
   *
   * @return  the value of the specified column for the current row of the
   *          supplied result set
   *
   * @throws SQLException if a database error occurs
   */
  public static String getClobValue(ResultSet results,
                                    String    columnName)
                             throws SQLException {
    String value;
    Clob rawValue = results.getClob(columnName);
    if (results.wasNull()) {
      value = null;
    } else {
      value = rawValue.getSubString(1, (int) rawValue.length());
    }
    return value;
  }

  /**
   * This is a horrible, horrible method to try and find out the calling
   * class, method and line of the getConnection and returnConnection method.
   * This will be replaced by Thread.currentThread().getStackTrace() once we
   * are fully swapped over to Java 1.5
   *
   * @return the class, method and line number that called the Connection Pool
   */
  private static String getCallingClass() {
    String ret = null;
    StackTraceElement[] temp = new Exception().getStackTrace();
    if(temp != null) {
    	
    	int i = 0;
    	
      for (; i < temp.length; i++) {
        if(ret == null &&
           temp[i] != null &&
           //
           // Skip past all db based classes
           //
           !temp[i].getClassName().startsWith("uk.co.inbrand.db.") &&
           !"uk.co.inbrand.common.uk.co.inbrandHelper".equals(temp[i].getClassName())) {
          ret = temp[i].getClassName() + "." + temp[i].getMethodName() + "():" + temp[i].getLineNumber();
          break;
        }
      }
      
      //
      // If this connection is being used in a persistence framework, such as Hibernate
      // then we will want to know where we are calling the framework, not the inner workings of the framework
      //
      i++;
      
      if (null != ret && !ret.startsWith("uk.co.inbrand.")) {
	      for (; i < temp.length; i++) {
	      	if (temp[i] != null && temp[i].getClassName().startsWith("uk.co.inbrand.")) {
	      		ret = temp[i].getClassName() + "." + temp[i].getMethodName() + "():" + temp[i].getLineNumber();
	      		break;
	      	}
	      }
      }
    }
    return ret;
  }

  /**
   * @return the stacktrace that called the Connection Pool
   */
  private static String getStackTrace() {
    
    if(!storeStackTraceInfo)
      return "DISABLED";
    
    StackTraceElement[] elements = new Exception().getStackTrace();
    if(elements != null) {
      StringBuilder sb = new StringBuilder();
      for(StackTraceElement element : elements) {
        if(element != null) {
          sb.append(element.getClassName());
          sb.append(".");
          sb.append(element.getMethodName());
          sb.append("():");
          sb.append(element.getLineNumber());
          sb.append("<br>");
        }
      }
      return sb.toString();
    }
    return "";
  }
  
  /**
   * The flush data method will try to recover any connection pools that have
   * run out of connections.
   */
  public static void flushData() {
    ConnectionPool.checkForTroubledPools();
  }
  
  /**
   * Iterates through all of the connection pools to see if any of them
   * have run out of connections.
   */
  @SuppressWarnings("unchecked")
private static void checkForTroubledPools() {


    List tempPools = new ArrayList();
    ConnectionPool pool = null;

    //
    // cycle through each of the pools and flush any that have
    // run out of connections
    //
    synchronized(ConnectionPool.instances) {
      Iterator it = ConnectionPool.instances.values().iterator();
      //
      // need to get all the pools out of the array first because this will
      // cause a concurrent modification exception if we try to take one of
      // them out of the instances map during this process
      //
      while(it.hasNext()) {
        tempPools.add(it.next());
      }
    }

    //
    // we can now iterate around our temp pool safely
    //
    Iterator it = tempPools.iterator();

    while(it.hasNext()) {

      pool = (ConnectionPool)it.next();

      if(pool.jdbcFlushable) {
        CONNECTION_POOL_LOGGER.warn(HOSTNAME + " REFRESH CHECK Pool : " + pool.dbName + "( " + pool.jdbcUser + " )" +
                                    ", inUse : " + pool.getConnectionsInUse() +
                                    ", lastSuccess : " + pool.lastSuccessfulConnectionRequest +
                                    ", failuresSinceLastSuccess : " + pool.numberOfFailuresSinceLastSuccess +
                                    ", maxFailures : " + pool.jdbcMaxFailuresBeforeFlush);
        pool.checkForTrouble();
      }
    }
  }

  /**
   * Checks the pool to see if it has run out of connections and refreshes
   * it if it has.
   */
  public void checkForTrouble() {
    //
    // if the pool is set up for test flushing then we dont need to
    // wait for the max failures to happen before the flushing occurs
    //
    if(this.jdbcTestFlushing ||
        this.numberOfFailuresSinceLastSuccess > this.jdbcMaxFailuresBeforeFlush) {
      this.refreshPool();
    }
  }

  /**
   * Removes the pool and puts it into the failed pools list so that a new
   * one can start up
   */
  @SuppressWarnings("unchecked")
public void refreshPool() {

    ConnectionPool removedPool = null;

    synchronized(ConnectionPool.instances) {
      removedPool = (ConnectionPool)ConnectionPool.instances.remove(this.dbName);
    }

    synchronized(ConnectionPool.failedPools) {
      ConnectionPool.failedPools.add(removedPool);
    }
    
    unregisterConnectionPoolWithManagement(removedPool, Logger.getLogger(ConnectionPool.class));

    CONNECTION_POOL_LOGGER.fatal(HOSTNAME + "_" + removedPool.jdbcUser + " REFRESH Had to remove pool because connections were not being returned.");

    try {

      StringBuilder logString = new StringBuilder();
      logString.append(HOSTNAME);
      logString.append("_");
      logString.append(removedPool.jdbcUser);
      logString.append(" No connections available : Pool Info -> dbName: [");
      logString.append(removedPool.dbName);
      logString.append("], jdbcUrl: [");
      logString.append(removedPool.jdbcUrl);
      logString.append("], jdbcUser: [");
      logString.append(removedPool.jdbcUser);
      logString.append("], maxConnections: [");
      logString.append(removedPool.jdbcMaxConnections);
      logString.append("], connections in use: [");     
      logString.append(removedPool.getConnectionsInUse());
      logString.append("], lastSuccess : [");
      logString.append(removedPool.lastSuccessfulConnectionRequest);
      logString.append("], failuresSinceLastSuccess : [");
      logString.append(removedPool.numberOfFailuresSinceLastSuccess);
      logString.append("], maxFailures : [");
      logString.append(removedPool.jdbcMaxFailuresBeforeFlush);
      logString.append("]\n<br>");
      
      int count = 1;
      InUseConnectionInfo connectionInfo = null;
      Iterator connectionInfoIterator = removedPool.getInUseInfo().iterator();

      while (connectionInfoIterator!= null && connectionInfoIterator.hasNext()) {

        connectionInfo = (InUseConnectionInfo)connectionInfoIterator.next();

        if (connectionInfo != null) {        
          logString.append("Connection number ");
          logString.append( IBHelper.padNumberUnderTen(count));
          logString.append(" info -> ");
          logString.append(HOSTNAME);
          logString.append("_");
          logString.append(removedPool.jdbcUser);
          logString.append("_");
          logString.append(connectionInfo.getHashCode());
          logString.append(" User: [");
          logString.append(connectionInfo.getUser());
          logString.append("], Class: [");
          logString.append(connectionInfo.getCallingClass());
          logString.append("], Time obtained: [");
          logString.append(connectionInfo.getWhenGiven());
          logString.append("], Last query: [");
          logString.append(connectionInfo.getLastQuery());
          logString.append("], Last query start time: [");
          logString.append(connectionInfo.getLastQueryStarted());
          logString.append("], Last query end time: [");
          logString.append(connectionInfo.getLastQueryEnded());
          logString.append("].\n<br>");
        }
        count++;
      }

      CONNECTION_POOL_LOGGER.warn(logString);

    } catch (Exception e) {
      CONNECTION_POOL_LOGGER.error(HOSTNAME + "_" + removedPool.jdbcUser + " EXCEPTION occured whilst trying to produce string for pool with no connections.", e);
    }
  }

  /**
   * Returns The underlying connection, stripping off the Apache wrappers
   * 
   * This is necessary as the wrapper returned by the data source nulls the underlying connections
   * when it is closed so an equality check cannot be performed agaisnt it
   * 
   * @param connection
   * @return The underlying connection
   */
  private static Object getApacheDbcpIdentifier(Connection connection) {
    
  	Object identifier = connection;
  	
    if(connection instanceof DelegatingConnection ) {
    	identifier = ((DelegatingConnection) connection).getInnermostDelegate();
    }
    return identifier;
  }
  
  /**
   * This method is used to fetch if the specified column name is present in the 
   * resultset. 
   * @param resultSet - The resultset which needs to be checked for the specified
   *                    column name
   * @param columnName - The Column name which needs to be checked ,
   *                      if it exists in the result set .
   * @return - boolean true if the column exits in result set else false. 
   */
  public static boolean isColumnInResultSet(ResultSet resultSet , 
                                            String columnName,
                                            Logger logger)
                                            throws SQLException {
    
    boolean columnPresent = false;
    try{
      ResultSetMetaData metaData =  resultSet.getMetaData();
      if(null != metaData){
        int columnCount = metaData.getColumnCount();
        
        for (int i = 0; i < columnCount; i++) {
          if(columnName.equalsIgnoreCase(metaData.getColumnName(i+1))){
            columnPresent =  true;
            break ;
          }
        }
      } 
      return columnPresent ;
    }catch (SQLException e){
      logger.error(e);
      throw e ;
    }
   
  }
  
  /**
   * Attempst to strip all proxy connections until a connections of the 
   * registered class is obtained 
   * 
   * Because of static methods receiving Connections it is not possible to reliably
   * determine of if we have stripped fdown to the right level, so only known connection proxies
   * know to be appropriate
   * <ul>
   * 	<li>{@link ConnectionProxy}</li>
   * </ul>
   * 
   * The <code>org.apache.dbcp<code> is used as an underlying database source so cannot be stripped. 
   * 
   * @return The wrapped connection
   */
  private static Connection stripProxyConnections(Connection connection, Logger logger) {
  	
  	Connection returnConnection = connection;
  	//
  	// To avoid infinite loops (if a proxy delages to itself, for instance), limit the recursion count
  	// this is very unlikely to happen but should be accounted for just in case.
  	//
  	int recursionCount = 0;
  	
	
		if (returnConnection instanceof ConnectionProxy) {
			//
			// The recursionCount limit of 20 is completely arbitrary. 
			//
			while (returnConnection instanceof ConnectionProxy && recursionCount++ < 20) {
				//
  			// Handle Spring proxies
  			//
				returnConnection = ((ConnectionProxy) returnConnection).getTargetConnection();
			}
			
			if (returnConnection instanceof ConnectionProxy) {
				logger.error("The ultimate delegated connection was a ConnectionProxy; unable to strip to the real one.");
			}
		
			if (null == returnConnection) {
				logger.error("The delegate connection was null, returning original.");
				returnConnection = connection;
			}
  	}
	
  	
  	return returnConnection;
  	
  }
  
}