The best way to tackle connection leaks is to do it during testing.
You can use an automated utility so that each test verifies if there is a connection leak.
@BeforeClass
public static void initConnectionLeakUtility() {
if ( enableConnectionLeakDetection ) {
connectionLeakUtil = new ConnectionLeakUtil();
}
}
@AfterClass
public static void assertNoLeaks() {
if ( enableConnectionLeakDetection ) {
connectionLeakUtil.assertNoLeaks();
}
}
The ConnectionLeakUtil looks like this:
public class ConnectionLeakUtil {
private JdbcProperties jdbcProperties = JdbcProperties.INSTANCE;
private List idleConnectionCounters =
Arrays.asList(
H2IdleConnectionCounter.INSTANCE,
OracleIdleConnectionCounter.INSTANCE,
PostgreSQLIdleConnectionCounter.INSTANCE,
MySQLIdleConnectionCounter.INSTANCE
);
private IdleConnectionCounter connectionCounter;
private int connectionLeakCount;
public ConnectionLeakUtil() {
for ( IdleConnectionCounter connectionCounter :
idleConnectionCounters ) {
if ( connectionCounter.appliesTo(
Dialect.getDialect().getClass() ) ) {
this.connectionCounter = connectionCounter;
break;
}
}
if ( connectionCounter != null ) {
connectionLeakCount = countConnectionLeaks();
}
}
public void assertNoLeaks() {
if ( connectionCounter != null ) {
int currentConnectionLeakCount = countConnectionLeaks();
int diff = currentConnectionLeakCount - connectionLeakCount;
if ( diff > 0 ) {
throw new ConnectionLeakException(
String.format(
"%d connection(s) have been leaked! Previous leak count: %d, Current leak count: %d",
diff,
connectionLeakCount,
currentConnectionLeakCount
)
);
}
}
}
private int countConnectionLeaks() {
try ( Connection connection = newConnection() ) {
return connectionCounter.count( connection );
}
catch ( SQLException e ) {
throw new IllegalStateException( e );
}
}
private Connection newConnection() {
try {
return DriverManager.getConnection(
jdbcProperties.getUrl(),
jdbcProperties.getUser(),
jdbcProperties.getPassword()
);
}
catch ( SQLException e ) {
throw new IllegalStateException( e );
}
}
}
The IdleConnectionCounter implementations can be found in this [blog post][1], and the MySQL version like this:
public class MySQLIdleConnectionCounter implements IdleConnectionCounter {
public static final IdleConnectionCounter INSTANCE =
new MySQLIdleConnectionCounter();
@Override
public boolean appliesTo(Class<? extends Dialect> dialect) {
return MySQL5Dialect.class.isAssignableFrom( dialect );
}
@Override
public int count(Connection connection) {
try ( Statement statement = connection.createStatement() ) {
try ( ResultSet resultSet = statement.executeQuery(
"SHOW PROCESSLIST" ) ) {
int count = 0;
while ( resultSet.next() ) {
String state = resultSet.getString( "command" );
if ( "sleep".equalsIgnoreCase( state ) ) {
count++;
}
}
return count;
}
}
catch ( SQLException e ) {
throw new IllegalStateException( e );
}
}
}
Now, when you run your tests, you'll get a failure when a connection is being leaked:
:hibernate-core:test
org.hibernate.jpa.test.EntityManagerFactoryClosedTest > classMethod FAILED
org.hibernate.testing.jdbc.leak.ConnectionLeakException
