JDBC MySql 连接池避免耗尽连接池的做法 [英] JDBC MySql connection pooling practices to avoid exhausted connection pool

查看:66
本文介绍了JDBC MySql 连接池避免耗尽连接池的做法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 GlassFish 上有一个 Java-JSF Web 应用程序,我想在其中使用连接池.因此,我创建了一个 application 作用域 bean,它与其他 bean 的 Connection 实例一起使用:

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:无法从连接池 [mysql_testPool] 获取/创建连接.原因:使用中的连接等于 max-pool-size 和过期的 max-wait-time.无法分配更多连接.RAR5114:分配连接时出错:[分配连接时出错.原因:使用中的连接等于 max-pool-size 和过期的 max-wait-time.无法分配更多连接.] java.sql.SQLException:分配连接时出错.原因:使用中的连接等于 max-pool-size 和过期的 max-wait-time.无法分配更多连接.

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.

推荐答案

该异常表示应用程序代码泄漏数据库连接的典型案例.您需要确保获得关闭所有这些(ConnectionStatement ResultSet) 在 try-with-resources 块在完全相同的方法块中,根据正常的 JDBC 习惯用法.

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();
    }
}

或者当您使用的不是 Java 7 时,在 try-finally 块中.在 finally 中关闭它们将保证它们在出现异常时也被关闭.

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) {}
    }
}

是的,您仍然需要自己关闭连接,即使使用连接池也是如此.初学者中的一个常见错误是他们认为它会自动处理关闭.这不正确.连接池即返回一个包装的连接,它在 close() 中执行以下操作:

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.

这篇关于JDBC MySql 连接池避免耗尽连接池的做法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆