4
\$\begingroup\$

I was curious about trying to write my own simple Database Connection Pool, where all the responsibility for freeing resources belongs to objects which use those connections.

There are:
3 classes - ConnectionPool, RecoverableConnection and ConnectionFactory
1 interface - IrresponsibleConnectionManager

Is my code (my thoughts) acceptable ? Or is it a complete junk ? It took me an hour and half to write and I know there are several better algorithms to implement. But this could server well for my pourpose. I also found this solution faster to some libraries I had downloaded, even faster than DataSource.

I'm glad to hear any comments about my code !

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author XY
 */
public class ConnectionFactory {

    private static ConnectionPool connectionPool;

    public static void initializeConnectionPool(String url, Properties connectionProperties, int connectionPoolSize) throws SQLException {

        if (connectionPool == null) {
            connectionPool = new ConnectionPool(url, connectionProperties, connectionPoolSize);
        }
    }

    /**
     * Returns a connection from connection pool.
     *
     * @param waitTime Time to wait for a connection.
     * @return Connection to the database.
     * @throws java.sql.SQLException When connection is not available within given wait time.
     */
    public static Connection getConnection(ConnectionPool.WaitTime waitTime) throws SQLException {
        return connectionPool.getConnection(waitTime);
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;

/**
 * Offers unified way to handle database connections. Provides connection pooling functionality.
 *
 * @author XY
 *         Date: 13.5.13
 */
public class ConnectionPool implements IrresponsibleConnectionPoolManager {


    private int pool_size = 0;
    private Queue<Connection> availableConnections;


    /**
     * @param url                  Database URL
     * @param connectionProperties Properties of the connection to establish.
     * @param poolSize             Number of connections to establish.
     * @throws SQLException When creating of the connection pool fails.
     */
    public ConnectionPool(String url, Properties connectionProperties, int poolSize) throws SQLException {
        initializeConnections(url, connectionProperties);
        pool_size = poolSize;

    }

    /**
     * Creates the pool of available connections according to currently set number of pool size.
     * Note that connection pool must be initialized to it's full capacity, or it is emptied and objects
     * are free to be garbage collected.
     *
     * @param url        Database connection URL
     * @param properties Properties required for connection.
     * @throws SQLException If case of not finishing creating connection pool, this exception is thrown.
     */
    private void initializeConnections(String url, Properties properties) throws SQLException {

        availableConnections = new ArrayDeque<>();

        try {
            //Fills list of available connections
            for (int i = 0; i < pool_size; i++) {
                Connection createdConnection = new RecoverableConnection(DriverManager.getConnection(url, properties), this);
                availableConnections.add(createdConnection);
            }

        } catch (SQLException e) {
            //Free involved objects for garbage collection
            availableConnections.removeAll(availableConnections);
            availableConnections = null;
            throw new SQLException("Could not establish connection.");

        }

    }

    /**
     * Returns a connection from connection pool.
     *
     * @param waitTime Time to wait for a connection.
     * @return Connection to the database.
     * @throws SQLException When connection is not available within given wait time.
     */
    public Connection getConnection(WaitTime waitTime) throws SQLException {
        Connection returnedConnection;
        //Operations with connection pool should be synchronized
        synchronized (ConnectionPool.class) {
            returnedConnection = availableConnections.poll();
        }


        if (returnedConnection == null && waitTime == WaitTime.IMMEDIATELY) {
            //If request method doesn't want to wait, throw an exception.
            throw new SQLException("No connection available at the moment.");
        } else if (returnedConnection == null) {
            //If connection is not required immediately, wait for specified time and recursively
            //call this method again
            try {
                Thread.sleep(waitTime.getWaitTime());
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            //Try to get connection object one more time
            returnedConnection = getConnection(WaitTime.IMMEDIATELY);
            return returnedConnection;
        } else {
            //Connection object is available
            return returnedConnection;
        }

    }

    public void recycleConnection(Connection c) {
        availableConnections.offer(c);
    }

    /**
     * Represents time to wait for a connection. Ensures unified waiting intervals.
     */
    public enum WaitTime {

        IMMEDIATELY(0),
        HALF_A_SECOND(500),
        ONE_SECOND(1000),
        THREE_SECONDS(3000),
        FIVE_SECONDS(5000);
        private int waitTime;

        private WaitTime(int i) {
            this.waitTime = i;
        }

        public int getWaitTime() {
            return waitTime;
        }
    }

}


/**
 * Connection pool manager recycling allocated connections when
 * @author XY
 */
interface IrresponsibleConnectionPoolManager {
    /**
     * Returns connection back to connection pool.
     *
     * @param recycledConnection Connection to return back to connection pool.
     */
    void recycleConnection(Connection recycledConnection);
}

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

/**
 * Wraps any object implementing Connection interface and makes it manageable by any implementation of ConnectionPoolmanager
 * @author XY
 * @see IrresponsibleConnectionPoolManager
 * @see Connection
 */
public class RecoverableConnection implements Connection {

    private Connection connection;
    private IrresponsibleConnectionPoolManager manager;


    public RecoverableConnection(Connection connection, IrresponsibleConnectionPoolManager manager) {
        this.connection = connection;
        this.manager = manager;

    }

    @Override
    public Statement createStatement() throws SQLException {
        return connection.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return connection.prepareCall(sql);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        return connection.nativeSQL(sql);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return connection.getAutoCommit();
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        connection.setAutoCommit(autoCommit);
    }

    @Override
    public void commit() throws SQLException {
        connection.commit();
    }

    @Override
    public void rollback() throws SQLException {
        connection.rollback();
    }

    @Override
    /**
     * Return connection to the connection pool
     */
    public void close() throws SQLException {
        connection = null;
        manager.recycleConnection(this);

    }

    @Override
    public boolean isClosed() throws SQLException {
        return connection == null;
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return connection.getMetaData();
    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return connection.isReadOnly();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        connection.setReadOnly(readOnly);
    }

    @Override
    public String getCatalog() throws SQLException {
        return connection.getCatalog();
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        connection.setCatalog(catalog);
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return connection.getTransactionIsolation();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        connection.setTransactionIsolation(level);
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return connection.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException {
        connection.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.createStatement(resultSetType, resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.prepareStatement(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return connection.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return connection.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        connection.setTypeMap(map);
    }

    @Override
    public int getHoldability() throws SQLException {
        return connection.getHoldability();
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        connection.setHoldability(holdability);
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return connection.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return connection.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        connection.rollback();
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        connection.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return connection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return connection.prepareStatement(sql, autoGeneratedKeys);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return connection.prepareStatement(sql, columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return connection.prepareStatement(sql, columnNames);
    }

    @Override
    public Clob createClob() throws SQLException {
        return connection.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException {
        return connection.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return connection.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return connection.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return connection.isValid(timeout);
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        connection.setClientInfo(name, value);
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return connection.getClientInfo(name);
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return connection.getClientInfo();
    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        connection.setClientInfo(properties);
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        return connection.createArrayOf(typeName, elements);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        return connection.createStruct(typeName, attributes);
    }

    @Override
    public String getSchema() throws SQLException {
        return connection.getSchema();
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        connection.setSchema(schema);
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        connection.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        connection.setNetworkTimeout(executor, milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return connection.getNetworkTimeout();
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return connection.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return connection.isWrapperFor(iface);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        RecoverableConnection that = (RecoverableConnection) o;

        if (connection != null ? !connection.equals(that.connection) : that.connection != null) return false;
        if (manager != null ? !manager.equals(that.manager) : that.manager != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = connection != null ? connection.hashCode() : 0;
        result = 31 * result + (manager != null ? manager.hashCode() : 0);
        return result;
    }
}
\$\endgroup\$
2
\$\begingroup\$

1) ConnectionFactory.initializeConnectionPool() is not thread-safe since two threads could see the connectionPool as null and set it at the same time. The first one will be "lost" and garbage collected, so it is not catastrophic, but it should probably be thread-safe. I did not check other classes for threadsafety, but they might also have issues.

2) ConnectionFactory.getConnection() should probably return some useful error message if initializeConnectionPool() had not been called before. It would actually make more sense to replace initializeConnectionPool() by the constructor. In such a case you would not use the singleton pattern, since you might have many instances of ConnectionFactory (hopefully to different databases).

3) The constructor ConnectionPool() calls initializeConnections() before setting pool_size, but it should be the other way around

4) Instead of using a Queue<Connection> in ConnectionPool, you should use a BlockingQueue. They have poll and offer methods that take a time out. You somewhat recreated that behavior with thread.sleep. However thread.sleep can sometimes wake up before its intended time and it is in general preferable to use existing classes that provide some functionality since you know they are bulletproof.

5) It does not smell quite right that RecoverableConnection contains a connection manager as a member. That class is very long but just seems to redefine all Connection methods. It is probably better to get rid of RecoverableConnection and just use normal Connections.

I have not read all your classes, but I hope those comments will be useful.

\$\endgroup\$
1
\$\begingroup\$

ConnectionFactory looks like a singleton and I think could probably be rewritten and would be safer as a singleton. Unsynchronized concurrent calls to initializeConnectionPool() would have unintended side-effects. Take a look at http://en.wikipedia.org/wiki/Singleton_pattern to see which Java singleton pattern works best for you.

\$\endgroup\$

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.