Prevent ORMLite connection leaks

In ORMLite, the lazy loading of mapped collections is implemented in a non-standard way. Instead of loading the content once during the first access, it loads it every time the collection is accessed. Also, while the collection is accessed, the connection to the database remains open. To avoid connection leaks, you must explicitly close it.

If the collection is not fully read, or the exception is thrown while iterating, the connection remains open even after the Bot Task finishes until the BEP worker is killed (will only be closed if the garbage collector reaps some time later).

warning

Draining the Data Store connection pool causes an outage of all Business Processes not using Data Stores in this instance. Increasing the connection pool in the configuration does not solve the problem.

Possible exception in logs

If you are not aware of the best practice described in the article, you will get the following exception in a Bot Task that uses Data Stores. Not exactly this Bot Task might cause the issue. Another Bot Task can block the number of connections, while others cannot access Data Stores and fail.

Caused by: java.sql.SQLTransientConnectionException: DataStore connection pool - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676) ~[HikariCP-3.2.0.jar!/:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190) ~[HikariCP-3.2.0.jar!/:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155) ~[HikariCP-3.2.0.jar!/:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-3.2.0.jar!/:na]
    at com.workfusion.odf2.core.orm.wrapper.DataSourceProxy.getConnection(DataSourceProxy.java:27) ~[na:na]
    at com.j256.ormlite.jdbc.DataSourceConnectionSource.initialize(DataSourceConnectionSource.java:105) ~[na:na]
    at com.j256.ormlite.jdbc.DataSourceConnectionSource.<init>(DataSourceConnectionSource.java:71) ~[na:na]
    at com.workfusion.odf2.core.OdfCoreModule.connectionSource(OdfCoreModule.java:77) ~[na:na]
    ... 44 common frames omitted

To avoid connection leaks, see the cause of such an exception in the following code:

for (Account account : accountDao) {
    if (account.getName().equals("Robert Oppenheimer")) {
        
        return account;  // Explosion. Loop not iterated 100% and ORMLite does not close connection. As result one connection leaked until BEP worker will be killed.
        
    }
    
    if (account.getName().equals("Werner Heisenberg")) {
        
        break;  // Explosion. Loop not iterated 100% and ORMLite does not close connection. As result one connection leaked until BEP worker will be killed.
        
    }
    
    if (account.getName().equals("Igor Kurchatov")) {
        
        throw new IllegalStateException(MessageConstants.ERROR);  // Explosion. Loop not iterated 100% and ORMLite does not close connection. As result one connection leaked until BEP worker will be killed.
        
    }
}

Use DAO

To avoid any issues with connection leaks or performance, use DAO instead of mapping:

@SneakyThrows
public List<ChildEntity> getAllByParedId(String parentId) {
    return query(queryBuilder()
            .where().eq(PARENT_UUID, parentId)
            .prepare());
}

Also, upgrade to the latest version of ORMLite (v5.7+) and orm-datastore-schema (v1.5+).

Iterate over collection

Iterating over the collection is not advised. Though, there are several ways to access the data.

info

Mind that using DAO is a recommended approach.

For-each

Best practice (the field type is ForeignCollection<ChildEntity>):

try (CloseableIterator<ChildEntity> iterator = parentEntity.getChildEntities().closeableIterator()) {
    while (iterator.hasNext()) {
        ChildEntity child = iterator.next();
        // Do something.
    }
}

Bad practice (a connection leak occurs if the cycle is interrupted):

for (ChildEntity child: parentEntity.getChildEntities()) {
    if (account.getName().equals("Robert Oppenheimer")) {
        
        return account;  // explosion - loop doesn't iterated 100% and ORMLite does't closes connection
        
    }
}

Streams

Avoid using Stream API together with the DAO collections out of ORMLite. It is recommended to use Iterators and For loops following the rules that do not interrupt all cycles. Also, use CloseableIterator.

Starting with ORMLite v5.7, you can use Stream API in the following way:

Best practice:

try (final Stream<ChildEntity> stream = parentEntity.getChildEntities().stream()) {
    stream.forEach(/* do something*/);
}

Bad practice (that can lead to a connection leak):

parentEntity.getChildEntities().stream().getFirst();

Example

See an example of a code change used to avoid conection leaks.

This causes leaks:

Map<String, String> body = EmailUtil.prepareEmailBodyMapForSuccessEmail(
    email.getAttachments().stream().filter(attach -> attach.getFileName().contains(".pdf")).findFirst()
        .map(Attachment::getFileName).orElse(""), email.getReceivedAt());

After quick refactoring according to the recommendations, the code works without exceptions:

String attFileName = "";
Iterator<Attachment> itr = email.getAttachments().iterator();
while (itr.hasNext()) {
    Attachment att = itr.next();
    if (att.getFileName().contains(".pdf")) {
        attFileName = att.getFileName();
    }
}
Map<String, String> body = EmailUtil.prepareEmailBodyMapForSuccessEmail(attFileName, email.getReceivedAt());