Connection pool runs out of connections

[Tristan Van Poucke]

Hello,

I started on writing my own connection pool, and I something is wrong.
If i test my site with testing software it runs ok. But if I combine that with me clicking on random links in the website, I start loosing connections
And eventually I run out of connection wich leads to null pointer exceptions.

this is my code:

    static synchronized public MySQLConnectionPool getInstance() {
              if (mInstance == null) {
                     // Must be the first call to the object
                     mInstance =  new MySQLConnectionPool();
            }
            return mInstance;
       }

 private MySQLConnectionPool() {
              Connection tempConnection;
              String filePrefix = "/" + MySQLConnectionPool.class.getName().replace('.', '/');
              InputStream propertyFile = getClass().getResourceAsStream(filePrefix + ".properties");
              Properties poolProperties = new Properties();
              try {
                     poolProperties.load(propertyFile);
              }
              catch (Exception propertyException) {
                     System.err.println("Error reading property file. " + filePrefix +
                                  ".properties is in the same directory with the ConnectionPool class.");
                     return;
              }
              // First load the database driver by reading the driver from the properties file.
              try {
                     String dbDriverName = poolProperties.getProperty("dbDriver");
                     mDBDriver = (Driver)Class.forName(dbDriverName).newInstance();
                     DriverManager.registerDriver(mDBDriver);
                     // Now grab the remaining properties and create the pool of connections.
                     mDBURL = poolProperties.getProperty("dbUrl");
                     mDBUserName = poolProperties.getProperty("dbUserName");
                     mDBPassword = poolProperties.getProperty("dbPassword");
                     mNumberOfConnections = Integer.parseInt(poolProperties.getProperty("numberOfConnections"));
                     mConnections = new Vector(mNumberOfConnections);
                     for ( int i = 0; i < mNumberOfConnections; i++ ) {
                           tempConnection = (Connection) DriverManager.getConnection(mDBURL, mDBUserName, mDBPassword);
                           mConnections.addElement(tempConnection);
                     }
              }
              catch (Exception setupException) {
                     System.err.println("Unable to setup JDBC connection pool. " + setupException);
              }
       }
public Connection checkOutAndNewConnection() throws SQLException
{
    Connection tempConnection = (Connection) DriverManager.getConnection(mDBURL, mDBUserName, mDBPassword);
     mConnections.addElement(tempConnection);
     return checkOut();
}
 public Connection checkOut() throws SQLException {
              System.out.println("**********************  NUMBER OF CONNECTIONS LEFT:" + mConnections.size());
                Connection dbConnection = null;
              if ( mConnections.size() > 0 ) {
                     dbConnection = (Connection)mConnections.firstElement();
                     mConnections.removeElementAt(0);
                     try {
                           if (dbConnection.isClosed()) {
                                  dbConnection = checkOutAndNewConnection();
                           }
                           else {
                                  dbConnection.getMetaData().getTableTypes();
                           }
                     }
                     catch (SQLException sqlE) {
                           dbConnection = checkOut();
                     }
              }
              return dbConnection;
       }

  public void checkIn(Connection pConnection) {
              if ( pConnection != null ) {
                     try {
                           if ( !pConnection.isClosed() ) {
                                  mConnections.addElement(pConnection);
                           }
                           else
                           {
                               System.out.println("TIS EEEEN CLOSED8!!!!!!!!!!!!!!!");
                           }
                     }
                     catch (SQLException sqlE) {
                           ; // Do nothing. Bad connection checked in. If counting checkouts reduce
                           // the count here.
                     }
              }
       }


  public synchronized void shutdown() {
              Connection tempConnection = null;
      ListIterator ConnectionIterator = mConnections.listIterator();

      try {
         while ( ConnectionIterator.hasNext() ) {
            tempConnection = (Connection)ConnectionIterator.next();
            tempConnection.close();
                     }
                     ConnectionIterator = null;
                     mConnections.removeAllElements();
                     DriverManager.deregisterDriver(mDBDriver);
              }
              catch (SQLException sqlE) {
                     System.err.println("Unable to de-register the JDBC driver.");
              }
              mInstance = null;
       }
}

Am I doing something wrong ?

[Bauke Scholtz]

I have not looked at your code closely, but do you close the connections after use in your JDBC code / DAO class? Do you close connections after a period of use? Do you actually lease connections from pool? Do you reap dead connections?

After all, maybe this is just for learning purposes, but in real I would forget about it at all and go for a long-existing and thoroughly tested open source connection pooling framework which is undoubtely more robust and reliable than you ever would code. For example Apache Commons DBCP, C3P0 and Proxool. Also keep in mind that most application servers ships with built in connection pooling facilities, like Apache Tomcat. I would prefer that above a homegrown pool.

If it is actually for learning purposes, here´s an article which I found very useful when I wanted to learn something about connection pooling some ages ago: http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html Note: once again, don´t use it for production. It´s just a bare example, there is much more to be done in the given code example.

[Tristan Van Poucke]

Okay, thank you very much.

Now I started with apache dbcp, but I don't understand completely.

I have a singleton class wich gets properties from a property file and uses them to open connections and store them in the connectionpool.

Like this (snipped code)

   
private GenericObjectPool connectionPool = new GenericObjectPool(null);
private PoolingDataSource dataSource = null;

 private MySQLConnectionPool() {
   ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(mDBURL, mDBUserName, mDBPassword); 
                     PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true); 
                     dataSource = new PoolingDataSource(connectionPool); 
}
 

Then I have this method "checkout" wich should give a connection to the user, this is being called whenever a user loads a page.

   
public Connection checkOut()  {
            return (Connection) dataSource.getConnection();
       }
 

But with my previous way of connecting, I closed the connection everytime the requested page was done loading.
I used a method "checkIn()", I don't understand how i could return the method in the connection pool...
Any suggestions?

[Bauke Scholtz]

You shouldn't give a connection to an user.

You should write JDBC code the usual way. Acquire and close the connection (and statement and resultset) in the shortest possible scope. Thus already inside the same method block as you invoke queries on the database. The connection pooling implementation will then behind the scenes worry about actually closing the connection or releasing it to the pool. You shouldn't worry about it in your code. Just do connection.close() and the connection pooling implementation will take the work over.

[Tristan Van Poucke]

Okay, I have done this.
I opened the connection and closed it once per page because when working without a connection pool this method seemed faster.
But now with the connectionpool I open it just before the query and close it afterward, because the connection does not really get opened and closed, this works
at about the same speed.

Only problem now is that I get a "connection is closed" SQLException on every method I try to close the connection

[Tristan Van Poucke]

I found my mistake.

Apparently in some persistence method's I called other persistence methods.
That tried to get a connection from the connectionpool, and then close it.
The first persistence method will try to close that connection again.

Thanks for the help