JDBC MySql连接池的做法可避免耗尽连接池 [英] JDBC MySql connection pooling practices to avoid exhausted connection pool
问题描述
我在GlassFish上有一个Java-JSF Web应用程序，我想在其中使用连接池。因此，我创建了一个应用程序 scoped bean，它为其他bean提供 Connection 实例
public class DatabaseBean {
private DataSource myDataSource;
public DatabaseBean（）{
try {
Context ctx = new InitialContext（）;
ecwinsDataSource =（DataSource）ctx.lookup（jdbc / myDataSource）;
} catch（NamingException ex）{
ex.printStackTrace（）;
$ b $ public Connection getConnection（）throws ClassNotFoundException，SQLException，InstantiationException，IllegalAccessException {
Connection connection = myDataSource.getConnection（）;
System.out.println（成功连接：+连接）;
//示例：成功连接：com.sun.gjc.spi.jdbc40.ConnectionHolder40@7fb213a5
返回连接;
$ b $ p
$ b 这样连接池就可以非常快速地被填充;在通过'db-related'视图进行了几次导航之后，应用程序停止并显示以下内容：
RAR5117：无法获取/创建连接连接池[mysql_testPool]。原因：正在使用的连接等于最大池大小和最大等待时间。无法分配更多连接。 RAR5114：分配连接时出错：[分配连接时出错。原因：正在使用的连接等于最大池大小和最大等待时间。无法分配更多连接。] java.sql.SQLException：分配连接时出错。原因：正在使用的连接等于最大池大小和最大等待时间。无法分配更多连接。
我正在关闭每种方法中的连接和其他资源。该应用程序运行所有单独连接正常。
我做错了什么？任何提示或建议，将不胜感激。
解决方案异常表示泄漏数据库连接的典型应用程序代码。您需要确保您获得和全部关闭（ Connection ，语句 <在 和 ResultSet rel =nofollow noreferrer> try-with-resources 根据正常的JDBC习惯用法在相同的方法块中拦截。
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（）;
或者当你不在Java 7中时，一个 try-finally 块。在最后中关闭它们将保证它们在发生异常情况时也会关闭。
public void create（Entity entity）throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
尝试{
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）{}
}
}
是的，你仍然需要自己关闭连接，即使在使用连接池时也是如此。初创公司认为它会自动处理收盘价是一个普遍的错误。这是不正确。连接池即返回一个包装连接，它在close（）中做如下的事情：
public void close（）throws SQLException {
if（this.connection仍然有资格重用）{
不会关闭this.connection，只是将它返回给池以供重用;
} else {
实际调用this.connection.close（）;
$ b 不关闭它们会导致连接不被释放然后它会一次又一次地获取一个新的数据库，直到数据库用完连接，这会导致您的应用程序崩溃。
另请参阅： / h3>
-
- 使用静态java.sql安全吗？多线程系统中的连接实例？
- 关闭池中的JDBC连接
I have a Java-JSF Web Application on GlassFish, in which I want to use connection pooling. Therefore I created an application scoped bean that serves with Connection instances for other beans:
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:
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.
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.
解决方案 The exception indicates a typical case of application code which leaks database connections. You need to ensure that you acquire and close all of them (Connection, Statement and ResultSet) in a try-with-resources block in the very same method block according the normal JDBC idiom.
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-finally block. Closing them in finally will guarantee that they are also closed in case of exceptions.
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():
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:
- How often should Connection, Statement and ResultSet be closed in JDBC?
- Is it safe to use a static java.sql.Connection instance in a multithreaded system?
- Closing JDBC Connections in Pool
这篇关于JDBC MySql连接池的做法可避免耗尽连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
