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.
Best Answer
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
andResultSet
) in atry-with-resources
block in the very same method block according the normal JDBC idiom.Or when you're not on Java 7, in a
try-finally
block. Closing them infinally
will guarantee that they are also closed in case of exceptions.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():
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: