Java - How handle JDBC connections inside your DAO

Question

I have a DAO and I implement a connection pool. I instantiate the connection pool using a singleton to have only one instance. I call on the getConnection function to get my connection inside of my DAO. I do this for each method within the DAO? Is this a good way handling connections with DAO? Is there a more efficient way to setup my pool and have it work with my DAO? Specifically with JDBC.

public class SQLUserDAO implements GenericDAO<User, String, Boolean>
{

    @Override
    public void update(User user, Boolean active) throws NotFoundException
        {
            // Create the ConnectionPool:
            JDBCConnectionPool pool = JDBCConnectionPool.getPoolInstance();




            // Get a connection:
            Connection con = pool.checkOut();

            // Return the connection:
            pool.checkIn(con);
        }

    @Override
    public void delete(User user, Boolean active) throws NotFoundException
        {
            // Create the ConnectionPool:
            JDBCConnectionPool pool = JDBCConnectionPool.getPoolInstance();

            // Get a connection:
            Connection con = pool.checkOut();

            // Return the connection:
            pool.checkIn(con);

        }

    @Override
    public User findByKey(String key, Boolean active) throws NotFoundException
        {
            // Create the ConnectionPool:
            JDBCConnectionPool pool = JDBCConnectionPool.getPoolInstance();

            // Get a connection:
            Connection con = pool.checkOut();

            // Return the connection:
            pool.checkIn(con);
            return null;
        }

    @Override
    public User findByValue(User object, Boolean active)
            throws NotFoundException
        {
            // Create the ConnectionPool:
            JDBCConnectionPool pool = JDBCConnectionPool.getPoolInstance();

            // Get a connection:
            Connection con = pool.checkOut();

            // Return the connection:
            pool.checkIn(con);
            return null;
        }

    @Override
    public void insert(User user, Boolean active) throws NotFoundException
        {
            // Create the ConnectionPool:
            JDBCConnectionPool pool = JDBCConnectionPool.getPoolInstance();

            // Get a connection:
            Connection conn = pool.checkOut();

            PreparedStatement preparedStatement;

            try
            {
                preparedStatement = conn
                        .prepareStatement("INSERT INTO users(user_id, user_name, user_password) VALUES (?,?,?)");

                preparedStatement.setString(1, user.getUserId().toString());
                preparedStatement.setString(2, user.getUserName());
                preparedStatement.setString(3, user.getClearTextPassword());

                // execute insert SQL stetement
                preparedStatement.executeUpdate();
            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            // Return the connection:
            pool.checkIn(conn);
        }
}

ObjectPool

public abstract class ObjectPool<T>
{
    private long expirationTime;
    private Hashtable<T, Long> locked, unlocked;

    public ObjectPool()
    {
        expirationTime = 30000; // 30 seconds
        locked = new Hashtable<T, Long>();
        unlocked = new Hashtable<T, Long>();
    }

    protected abstract T create();

    public abstract boolean validate(T o);

    public abstract void expire(T o);

    public synchronized T checkOut()
        {
            long now = System.currentTimeMillis();
            T t;
            if (unlocked.size() > 0)
            {
                Enumeration<T> e = unlocked.keys();
                while (e.hasMoreElements())
                {
                    t = e.nextElement();
                    if ((now - unlocked.get(t)) > expirationTime)
                    {
                        // object has expired
                        unlocked.remove(t);
                        expire(t);
                        t = null;
                    }
                    else
                    {
                        if (validate(t))
                        {
                            unlocked.remove(t);
                            locked.put(t, now);
                            return (t);
                        }
                        else
                        {
                            // object failed validation
                            unlocked.remove(t);
                            expire(t);
                            t = null;
                        }
                    }
                }
            }
            // no objects available, create a new one
            t = create();
            locked.put(t, now);
            return (t);
        }

    public synchronized void checkIn(T t)
        {
            locked.remove(t);
            unlocked.put(t, System.currentTimeMillis());
        }
}

Connection Pool

public class JDBCConnectionPool extends ObjectPool<Connection>
{
private String dsn, usr, pwd;

private JDBCConnectionPool()
{
    super();

    try
    {
        Properties p = new Properties();
        FileInputStream input = new FileInputStream(
                "src/dbproperties");
        p.load(input);
        String driver = p.getProperty("driver");
        this.dsn = p.getProperty("url");
        this.usr = p.getProperty("user");
        this.pwd = p.getProperty("password");

        DriverManager
                .registerDriver((oracle.jdbc.driver.OracleDriver) Class
                        .forName(driver).newInstance());

        input.close();

    }
    catch (FileNotFoundException e)
    {
        e.printStackTrace();
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }
    catch (InstantiationException e)
    {

        e.printStackTrace();
    }
    catch (IllegalAccessException e)
    {

        e.printStackTrace();
    }
    catch (SQLException e)
    {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    catch (ClassNotFoundException e)
    {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

private static class LazySingleton
{
    private static final JDBCConnectionPool SINGLETONINSTANCE = new JDBCConnectionPool();
}

public static JDBCConnectionPool getPoolInstance()
    {
        return LazySingleton.SINGLETONINSTANCE;
    }

@Override
protected Connection create()
    {
        try
        {
            return (DriverManager.getConnection(dsn, usr, pwd));
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return (null);
        }
    }

@Override
public void expire(Connection o)
    {
        try
        {
            ((Connection) o).close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

@Override
public boolean validate(Connection o)
    {
        try
        {
            return (!((Connection) o).isClosed());
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return (false);
        }
    }

}

Answer 1

1. Implementing your own connection pooling smells like Not invented here anti-pattern; there are lots of robust libs, e.g. DBCP. Any profound reasons for reinventing this?
2. Why is poolin your code not an instance field of the DAO class, but rather a local var in every method?
3. A DAO as you coded it, doesn't have any state either; make it a singleton, too (besides the connection pool). You may pass the connection pool to the constructor of your DAOs.
4. Continuning on the last point: Why not use the established DataSource type as an argument to the DAO constructor? A DataSource may easily hide any pooling logic.
5. Even if you do not want a full-fledged solution like an IoC framework to instantiate and wire up your classes, you may look at Spring's JdbcTemplate. It helps you avoid resource leaks and is very well designed.