JDBC MySql connection pooling practices to avoid exhausted connection pool

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: <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="31525e5c1f42445f1f565b521f4241581f5b55535205011f725e5f5f545245585e5f795e5d5554430501710657530300025004">[email protected]</a>
        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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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:

Method 2

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.

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

What you probably should be doing is find out how to let your application grab a connection from the pool using jndi.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x