我使用JDBC连接池吗？ [英] Am I Using JDBC Connection Pooling?

问题描述

我正在尝试确定我是否实际使用JDBC连接池。在做了一些研究之后，实施起来似乎太容易了。事实上比常规连接更容易，所以我想验证。

I am trying to determine if I am actually using JDBC connection pooling. After doing some research, the implementation almost seems too easy. Easier than a regular connection in fact so i'd like to verify.

这是我的连接类：

public class DatabaseConnection {

Connection conn = null;

public Connection getConnection() {

    BasicDataSource bds = new BasicDataSource();
    bds.setDriverClassName("com.mysql.jdbc.Driver");
    bds.setUrl("jdbc:mysql://localhost:3306/data");
    bds.setUsername("USERNAME");
    bds.setPassword("PASSWORD");

    try{
        System.out.println("Attempting Database Connection");
        conn = bds.getConnection();
        System.out.println("Connected Successfully");
    }catch(SQLException e){
        System.out.println("Caught SQL Exception: " + e);
    }
    return conn;
}

public void closeConnection() throws SQLException {
    conn.close();
}

}

这是真正的连接汇集吗？我在另一个类中使用连接：

Is this true connection pooling? I am using the connection in another class as so:

        //Check data against database.
    DatabaseConnection dbConn = new DatabaseConnection();
    Connection conn;
    ResultSet rs;
    PreparedStatement prepStmt;

    //Query database and check username/pass against table.
    try{
        conn = dbConn.getConnection();
        String sql = "SELECT * FROM users WHERE username=? AND password=?";
        prepStmt = conn.prepareStatement(sql);
        prepStmt.setString(1, user.getUsername());
        prepStmt.setString(2, user.getPassword());
        rs = prepStmt.executeQuery();

        if(rs.next()){ //Found Match.
            do{
                out.println("UserName = " + rs.getObject("username") + " Password = " + rs.getObject("password"));
                out.println("<br>");
            } while(rs.next());
        } else {
            out.println("Sorry, you are not in my database."); //No Match.
        }

        dbConn.closeConnection(); //Close db connection.

    }catch(SQLException e){
        System.out.println("Caught SQL Exception: " + e);
    }

推荐答案

假设它是 BasicDataSource 来自 DBCP ，然后是，您正在使用连接池。但是，您将在每个连接获取上重新创建另一个连接池。您实际上并不是从同一个池中汇集连接。您只需在应用程序启动时创建一次连接池，并从中获取每个连接。您也不应将连接保持为实例变量。您还应关闭连接，语句和结果集，以确保在异常情况下正确关闭资源。 Java 7的 try-with-resources 声明对此有帮助，它将在尝试块完成后自动关闭资源。

Assuming that it's the BasicDataSource is from DBCP, then yes, you are using a connection pool. However, you're recreating another connection pool on every connection acquirement. You are not really pooling connections from the same pool. You need to create the connection pool only once on application's startup and get every connection from it. You should also not hold the connection as an instance variable. You should also close the connection, statement and resultset to ensure that the resources are properly closed, also in case of exceptions. Java 7's try-with-resources statement is helpful in this, it will auto-close the resources when the try block is finished.

这是一个小改写：

public final class Database {

    private static final BasicDataSource dataSource = new BasicDataSource();

    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/data");
        dataSource.setUsername("USERNAME");
        dataSource.setPassword("PASSWORD");
    }

    private Database() {
        //
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

}

（如果必要时作为抽象工厂重构，以提高可插性）

和

private static final String SQL_EXIST = "SELECT * FROM users WHERE username=? AND password=?";

public boolean exist(User user) throws SQLException {
    boolean exist = false;

    try (
        Connection connection = Database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_EXIST);
    ) {
        statement.setString(1, user.getUsername());
        statement.setString(2, user.getPassword());

        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            exist = resultSet.next();
        }
    }       

    return exist;
}

将按以下方式使用：

try {
    if (!userDAO.exist(username, password)) {
        request.setAttribute("message", "Unknown login. Try again.");
        request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response);
    } else {
        request.getSession().setAttribute("user", username);
        response.sendRedirect("userhome");
    }
} catch (SQLException e) {
    throw new ServletException("DB error", e);
}

在实际的Java EE环境中，您应该委派创建 DataSource 到容器/应用程序服务器并从JNDI获取它。对于Tomcat，请参阅此文档： http：//tomcat.apache。 org / tomcat-6.0-doc / jndi-resources-howto.html

In a real Java EE environement you should however delegate the creation of the DataSource to the container / application server and obtain it from JNDI. In case of Tomcat, see also for example this document: http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html

这篇关于我使用JDBC连接池吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！