2
\$\begingroup\$

I've created a database connection pool used on the serverside (of a client-server socket app) and was wondering what improvements can be made to help the design and/or efficiency of the solution. The pool is used from multiple threads so synchronisation is important.

The application has been running for a while now with seemingly few problems so the code does appear to work as intended.

Theory:

My basic theory is to have x amount of connections open at any one point in time taking from the top and putting back on the bottom. Connections will only stay alive for so long before they are freed and a new one is created. If I ever have too many because of high server demand a cleanup thread in the ConnectionPool class is responsibling for removing the extra connections as required.

Classes

  • ConnectionPool: Class used to contain the connections and manage their usage
  • CachedConnection: Just a wrapper for Connection and used so that I only keep connections around for a limited time before they are closed and new one takes their place
  • DatabaseConnection: abstract class that holds the connection pool. Children of this class will maintain connection strings etc

Some classes not added but used in the code

  • EventLog: Just a logging class, I've actually posted already here
  • WebServer: A static class to contain some configuration options
  • Stopwatch: Small class that acts as a stopwatch effectively

Code:

public class CachedConnection {

    private Connection _connection = null;
    private Stopwatch _stopWatch;
    private static int KEEP_ALIVE = 1000 * 60 * 20; // 20 minutes max connection life
    private static long _connectionCounter = 0; 
    private final long _id;

    public CachedConnection(Connection connection) {
        _connection = connection;
        _stopWatch = new Stopwatch(KEEP_ALIVE).start();

        _id = newId();
    }

    public Connection getConnection() {
        return _connection;
    }

    public long getId() {
        return _id;
    }

    public boolean keepAlive() {
        return !_stopWatch.isFinished();
    }

    public void close() throws SQLException {
        _connection.close();
    }

    private synchronized long newId() {
        return _connectionCounter++;
    }   
}

Actual Connection Pool class, the crux of the matter

public class ConnectionPool implements Runnable {

    // Number of initial connections to make.
    private final int _maxConnections;    
    // A list of available connections for use.
    private final Queue<CachedConnection> _availableConnections;    
    // A list of connections being used currently.
    private final Queue<CachedConnection> _usedConnections;
    // The URL string used to connect to the database
    private final String m_URLString;
    // The username used to connect to the database
    private final String m_UserName;    
    // The password used to connect to the database
    private final String m_Password;    
    // The cleanup thread
    private Thread m_CleanupThread = null;

    //Constructor
    protected ConnectionPool(String urlString, String user, String passwd, int connections) throws SQLException {

        // Initialize the required parameters
        m_URLString = urlString;
        m_UserName = user;
        m_Password = passwd;
        _maxConnections = connections;

        _usedConnections = new LinkedBlockingQueue<CachedConnection>();
        _availableConnections = new LinkedBlockingQueue<CachedConnection>();

        setupAvailableConnections(_maxConnections);
        startCleanUpThread();
    }    

    public synchronized void checkin(CachedConnection cached) {    
        if(cached != null) {
            // Remove from used list.            
            freeConnectionInUse(cached);

            try {
                Connection c = cached.getConnection();

                if(!c.isClosed()) {

                    // only add it back to our connection pool if we haven't exceeded
                    // the max allowed connections
                    if(availableConnectionCount() < _maxConnections) {
                        addAvailableConnection(cached);
                    }
                    else {
                        closeConnection(cached);
                    }                       
                }

            } catch(SQLException ex) {
                EventLog.write(ex, "ConnectionPool", "checkin");
            }
        }
    }            

    public void run() {
        try {
            while(true) {
                int cleanedConnections = 0;

                while(availableConnectionCount() > _maxConnections) {               
                    closeConnection(getNextAvailableConnection());
                    cleanedConnections++;
                }

                if(cleanedConnections > 0) {
                    EventLog.write("Cleaned " + cleanedConnections + " connections", "ConnectionPool", "run");  
                }

                // Now sleep for 1 minute
                Thread.sleep(60000 * 1);
            }    
        }
        catch(Exception e) {
            EventLog.write(e, "ConnectionPool", "run");            
        }
    }

    public CachedConnection checkout() throws SQLException {
        CachedConnection newConnxn = getNextAvailableConnection();

        if(newConnxn != null && newConnxn.keepAlive()) {            
            // Add it to the in use list
            connectionInUse(newConnxn); 
        } else {
            // otherwise it's dead so archive it and try and get another instance
            closeConnection(newConnxn);

            // add a new available connection
            newAvailableConnection();

            // try to get the first next available connection 
            newConnxn = checkout();
        }        

        // Either way, we should have a connection object now.
        return newConnxn;
    }

    private void startCleanUpThread() {
        // Create the cleanup thread
        m_CleanupThread = new Thread(this);
        m_CleanupThread.start();
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(m_URLString, m_UserName, m_Password);
    }

    private CachedConnection newCachedConnection() throws SQLException {
        return new CachedConnection(getConnection());
    }

    private void setupAvailableConnections(int connections) throws SQLException {
        int counter = 0;
        while(counter++ < connections) {
            // Add a new connection to the available list.
            newAvailableConnection();
        }
    }

    private synchronized CachedConnection getNextAvailableConnection() {
        return  _availableConnections.poll();
    }

    private synchronized void addAvailableConnection(CachedConnection connection) {
        _availableConnections.add(connection);
    }

    private synchronized void newAvailableConnection() throws SQLException {
        _availableConnections.add(newCachedConnection());
    }

    private synchronized void connectionInUse(CachedConnection connection) {
         _usedConnections.add(connection);
    }

    private synchronized void freeConnectionInUse(CachedConnection connection) {
        _usedConnections.remove(connection);
    }

    private void closeConnection(CachedConnection c)
    {
        if(c != null) {
            // don't re-add it and the connection is being freed for garbage collection
            try {
                c.close();
            } catch(SQLException ex) {
                EventLog.write(ex, "ConnectionPool", "archive");
            }
            finally {

                int av = availableConnectionCount();                
                EventLog.write("Closed old connection Id=" + c.getId() + ".  Available connections=" + av, "ConnectionPool", "archive");                
            }
        }
    }

    private synchronized int availableConnectionCount() {
        return _availableConnections.size();
    }
}

And it's used by children of DatabaseConnection

public abstract class DatabaseConnection {

    private CachedConnection _connection = null;
    abstract protected ConnectionPool getInstance() throws SQLException;

    public Connection getConnection() {
        return _connection.getConnection();
    }

    public boolean isOpen() { return getConnection() != null; }

    public synchronized void open() throws SQLException {       
        _connection = getInstance().checkout(); 
    }

    public void close() {
        if(_connection != null ) {
            try {                       
                getInstance().checkin(_connection);
            } catch(Exception ex)  {
                EventLog.write(ex.toString(), "DatabaseConnection", "Close" );              
            } finally {
                _connection = null;
            }
        } else
            EventLog.write("ERROR:  Attempting to close a connection but connection is null", "DatabaseConnection", "close" );

    }
}

And example of a DatabaseConnection child:

public class ClientDatabase extends DatabaseConnection {

    private final String _dbURL;
    private final String _username = "saywhat";
    private final String _password = "noway!";
    private final int _connections;

    private static ConnectionPool _pool = null;

    public ClientDatabase() {
        _dbURL = "what you talking about willis";
        _connections = WebServer.CONNECTIONS;
    }

    @Override
    protected ConnectionPool getInstance() throws SQLException { 

        if (_pool == null) {
            synchronized(ClientDatabase.class) {  
                if (_pool == null)          
                    _pool = new ConnectionPool(_dbURL, _username, _password, _connections);
            }
        }

        return _pool;
    }   
}
\$\endgroup\$
1
\$\begingroup\$

I would advice to look at DBCP library http://commons.apache.org/dbcp/.

If you are deploying your application in servlet container or application server then it has it's own implementation of the database connection pool. So you only have to configure database connection pool in xml or property file.

\$\endgroup\$
  • \$\begingroup\$ Cheers. My applet is not being served in a servlet container. I actually don't even know what that is :) It's jut being run on the server as a standard java application. \$\endgroup\$ – dreza Jul 20 '12 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.