Java - 如何处理 DAO 内的 JDBC 连接

Question

我有一个 DAO，并且实现了一个连接池。我使用单例实例化连接池，使其只有一个实例。我调用 getConnection 函数来获取 DAO 内的连接。我对 DAO 中的每个方法都执行此操作吗？这是处理与 DAO 的连接的好方法吗？有没有更有效的方法来设置我的池并使其与我的 DAO 一起使用？特别是使用 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);
        }
}

对象池

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());
        }
}

连接池

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. 实现您自己的连接池听起来像 Not invented here反模式；有很多强大的库，例如DBCP 。重新发明这个有什么深刻的原因吗？
2. 为什么代码中的 pool 不是 DAO 类的实例字段，而是每个方法中的本地变量？
3. 您编写的 DAO 也没有任何状态；也使其成为单例(除了连接池之外)。您可以将连接池传递给 DAO 的构造函数。
4. 继续最后一点:为什么不使用既定的 DataSource类型作为 DAO 构造函数的参数？数据源可以轻松隐藏任何池逻辑。
5. 即使您不想要像 IoC 框架这样成熟的解决方案来实例化和连接您的类，您也可以查看 Spring 的 JdbcTemplate 。它可以帮助您避免资源泄漏，而且设计得非常好。