Java JDBC MySql 连接池避免耗尽连接池的做法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2313197/
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
JDBC MySql connection pooling practices to avoid exhausted connection pool
提问by Daniel Szalay
I have a Java-JSF Web Application on GlassFish, in which I want to use connection pooling. Therefore I created an applicationscoped bean that serves with Connectioninstances for other beans:
我在 GlassFish 上有一个 Java-JSF Web 应用程序，我想在其中使用连接池。因此，我创建了一个application作用域 bean，它与Connection其他 bean 的实例一起使用：
public class DatabaseBean {
private DataSource myDataSource;
public DatabaseBean() {
try {
Context ctx = new InitialContext();
ecwinsDataSource = (DataSource) ctx.lookup("jdbc/myDataSource");
} catch (NamingException ex) {
ex.printStackTrace();
}
}
public Connection getConnection() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
Connection connection = myDataSource.getConnection();
System.out.println("Succesfully connected: " + connection);
//Sample: Succesfully connected: com.sun.gjc.spi.jdbc40.ConnectionHolder40@7fb213a5
return connection;
}
}
This way the connection pool gets filled very fast; after a few navigation through 'db-related' views, the application stops with the following:
这样连接池会很快被填满；在通过“db-related”视图进行几次导航后，应用程序停止并显示以下内容：
RAR5117 : Failed to obtain/create connection from connection pool [ mysql_testPool ]. Reason : In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections. RAR5114 : Error allocating connection : [Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.] java.sql.SQLException: Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.
RAR5117：无法从连接池 [mysql_testPool] 获取/创建连接。原因：使用中的连接等于 max-pool-size 和过期的 max-wait-time。无法分配更多连接。RAR5114：分配连接时出错：[分配连接时出错。原因：使用中的连接等于 max-pool-size 和过期的 max-wait-time。无法分配更多连接。] java.sql.SQLException：分配连接时出错。原因：使用中的连接等于 max-pool-size 和过期的 max-wait-time。无法分配更多连接。
I'm closing connections and other resources in every method. The application runs all OK with standalone connections.
我正在关闭每种方法中的连接和其他资源。该应用程序通过独立连接运行一切正常。
What am I doing wrong? Any tips or advice would be appreciated.
我究竟做错了什么？任何提示或建议将不胜感激。
采纳答案by BalusC
The exception indicates a typical case of application code which leaks database connections. You need to ensure that you acquire andclose all of them (Connection, StatementandResultSet) in a try-with-resourcesblock in the very same method block according the normal JDBC idiom.
该异常表示应用程序代码泄漏数据库连接的典型情况。您需要确保根据正常的 JDBC 习惯用法在同一方法块中的块中获取并关闭所有这些（Connection,Statement和ResultSet）try-with-resources。
public void create(Entity entity) throws SQLException {
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_CREATE);
) {
statement.setSomeObject(1, entity.getSomeProperty());
// ...
statement.executeUpdate();
}
}
Or when you're not on Java 7, in a try-finallyblock. Closing them in finallywill guarantee that they are also closed in case of exceptions.
或者当你不在 Java 7 上时，在一个try-finally块中。关闭它们finally将保证它们在异常情况下也被关闭。
public void create(Entity entity) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(SQL_CREATE);
statement.setSomeObject(1, entity.getSomeProperty());
// ...
statement.executeUpdate();
} finally {
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
}
Yes, you still need to close connections yourself, even when using connection pooling. It's a common mistake among starters that they think that it will then automatically handle the close. This is not true. The connection pool namely returns a wrapped connection which does something like the following in the close():
是的，您仍然需要自己关闭连接，即使使用连接池也是如此。初学者的一个常见错误是他们认为它会自动处理关闭。这不是真的。连接池即返回一个包装的连接，它在 close() 中执行以下操作：
public void close() throws SQLException {
if (this.connection is still eligible for reuse) {
do not close this.connection, but just return it to pool for reuse;
} else {
actually invoke this.connection.close();
}
}
Not closing them would cause the connection not being released back to the pool for reuse and thus it will acquire a new one again and again until the DB runs out of connections which will cause your application to crash.
不关闭它们会导致连接不会被释放回池以供重用，因此它将一次又一次地获取一个新的连接，直到数据库用完连接，这将导致您的应用程序崩溃。
See also:
也可以看看：
回答by Roland Bouman
If you need JDBC connection pooling, why don't you rely on what's available already? AFAIK, JDBC connection pooling is considered more or less a standard feature in these java application servers, and IMO, you should not want to build this yourself if you're just interested in creating an application.
如果您需要 JDBC 连接池，为什么不依赖已有的东西呢？AFAIK，JDBC 连接池或多或少被认为是这些 Java 应用程序服务器中的标准功能，而 IMO，如果您只是对创建应用程序感兴趣，则不应该自己构建它。
Here's a link that should get you started: http://weblogs.java.net/blog/2007/09/12/totd-9-using-jdbc-connection-pooljndi-name-glassfish-rails-application
这是一个可以帮助您入门的链接：http: //weblogs.java.net/blog/2007/09/12/totd-9-using-jdbc-connection-pooljndi-name-glassfish-rails-application
What you probably should be doing is find out how to let your application grab a connection from the pool using jndi.
您可能应该做的是找出如何让您的应用程序使用 jndi 从池中获取连接。
