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][1]
- **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;
}
}
[1]: http://codereview.stackexchange.com/questions/12336/robust-logging-solution-to-file-on-disk-from-multiple-threads-on-serverside-code