MySQL 我是否在使用 JDBC 连接池？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7592056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Am I Using JDBC Connection Pooling?
提问by ryandlf
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.
我试图确定我是否真的在使用 JDBC 连接池。在做了一些研究之后，实现似乎太容易了。实际上比常规连接更容易，所以我想验证一下。
Here is my connection class:
这是我的连接类：
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);
}
回答by BalusC
Assuming that it's the BasicDataSourceis 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-resourcesstatementis helpful in this, it will auto-close the resources when the tryblock is finished.
假设它BasicDataSource来自DBCP，那么是的，您正在使用连接池。但是，您在每次连接获取时都会重新创建另一个连接池。您并没有真正汇集来自同一个池的连接。您只需要在应用程序启动时创建一次连接池并从中获取每个连接。您也不应该将连接作为实例变量保存。您还应该关闭连接、语句和结果集以确保资源正确关闭，在异常情况下也是如此。Java 7 的try-with-resources语句对此很有帮助，它会在try块完成时自动关闭资源。
Here's a minor rewrite:
这是一个小的重写：
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();
}
}
(this can if necessary be refactored as an abstract factory to improve pluggability)
（如有必要，可以将其重构为抽象工厂以提高可插拔性）
and
和
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;
}
which is to be used as follows:
其用途如下：
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);
}
In a real Java EE environement you should however delegate the creation of the DataSourceto 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
然而，在真实的 Java EE 环境中，您应该将 的创建委托DataSource给容器/应用程序服务器并从 JNDI 获取它。在 Tomcat 的情况下，另请参见例如此文档：http: //tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html
回答by andy
Doesn't seem like it's pooled. You should store the DataSource in DatabaseConnection instead of creating a new one with each getConnection() call. getConnection() should return datasource.getConnection().
好像没有合集。您应该将 DataSource 存储在 DatabaseConnection 中，而不是在每次 getConnection() 调用时创建一个新的。getConnection() 应该返回 datasource.getConnection()。
回答by phyerbarte
Looks like a DBCP usage. If so, then yes. It's already pooled. And here is the default pool property value of the DBCP.
看起来像 DBCP 用法。如果是这样，那么是的。它已经汇集了。这是 DBCP 的默认池属性值。
/**
* The default cap on the number of "sleeping" instances in the pool.
* @see #getMaxIdle
* @see #setMaxIdle
*/
public static final int DEFAULT_MAX_IDLE = 8;
/**
* The default minimum number of "sleeping" instances in the pool
* before before the evictor thread (if active) spawns new objects.
* @see #getMinIdle
* @see #setMinIdle
*/
public static final int DEFAULT_MIN_IDLE = 0;
/**
* The default cap on the total number of active instances from the pool.
* @see #getMaxActive
*/
public static final int DEFAULT_MAX_ACTIVE = 8;
回答by Todd W. Powers
As a follow up to BalusC's solution, below is an implementation that I can be used within an application that requires more than one connection, or in a common library that would not know the connection properties in advance...
作为 BalusC 解决方案的后续，下面是一种实现，我可以在需要多个连接的应用程序中或在事先不知道连接属性的公共库中使用它...
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.ConcurrentHashMap;
public final class Database {
private static final ConcurrentHashMap<String, BasicDataSource> dataSources = new ConcurrentHashMap();
private Database() {
//
}
public static Connection getConnection(String connectionString, String username, String password) throws SQLException {
BasicDataSource dataSource;
if (dataSources.containsKey(connectionString)) {
dataSource = dataSources.get(connectionString);
} else {
dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl(connectionString);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSources.put(connectionString, dataSource);
}
return dataSource.getConnection();
}
}
