java – JDBC MySql连接池实践,以避免用尽连接池

发布时间：2020-12-14 05:57:06 所属栏目：Java 来源：网络整理

导读：我在GlassFish上有一个 Java-JSF Web应用程序,其中我想使用连接池.因此,我创建了一个与其他bean的Connection实例一起使用的应用程序作用域bean： public class DatabaseBean { private DataSource myDataSource; public DatabaseBean() { try { Context ctx

我在GlassFish上有一个 Java-JSF Web应用程序,其中我想使用连接池.因此,我创建了一个与其他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;
    }
}

这样连接池就很快就被填满了;在通过“db相关”视图进行几次导航之后,应用程序将停止以下操作：

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.

我在每个方法中关闭连接和其他资源.应用程序使用独立连接运行所有OK.

我究竟做错了什么？任何提示或建议将不胜感激.

解决方法

该异常表示泄漏数据库连接的应用程序代码的典型情况.您需要确保在正常的JDBC成语中以相同的方法块获取并关闭 try-with-resources块中的所有(Connection,Statement和ResultSet).

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;

    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()中执行以下操作：

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

不关闭它们将导致连接未被释放回池重新使用,因此它将一次又一次地获取一个新的连接,直到数据库用完,从而导致应用程序崩溃.

也可以看看：

> 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!