我是否在使用 JDBC 连接池?

2021-11-20 00:00:00 connection-pooling mysql jdbc jakarta-ee

我正在尝试确定我是否真的在使用 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 声明 有助于这样，它会在 try 块完成时自动关闭资源.

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

相关文章