mysql jdbc 關閉連接配接池,JDBC MySql連接配接池的做法可避免耗盡連接配接池

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: [email protected]

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: