Force Hikari/Hibernate to close stale (leaked?) connections

Question

I'm working with a FileMaker 16 datasource through the official JDBC driver in Spring Boot 2 with Hibernate 5.3 and Hikari 2.7.

The FileMaker server performance is poor, a SQL query execution time can reach a minute for big tables. Sometimes it results in connection leaking, when the connection pool is full of active connections which are never released.

The question is how to force active connections in the pool which have been hanging there say for two minutes to close, moving them to idle and making available for using again.

As an example, I'm accessing the FileMaker datasource through a RestController using the findAll method in org.springframework.data.repository.PagingAndSortingRepository:

@RestController
public class PatientController {

    @Autowired
    private PatientRepository repository;

    @GetMapping("/patients")
    public Page<Patient> find(Pageable pageable) {
        return repository.findAll(pageable);
    }
}

Calling /patients a few times in a raw causes connection leaking, here's what Hikari reports:

2018-09-20 13:49:00.939 DEBUG 1 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=2)

It also throws exceptions like this:

java.lang.Exception: Apparent connection leak detected
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.9.jar!/:na]

What I need is if repository.findAll takes more than N seconds, the connection must be killed and the controller method must throw and exception. How to achieve it?

Here's my Hikari config:

 allowPoolSuspension.............false
 autoCommit......................true
 catalog.........................none
 connectionInitSql...............none
 connectionTestQuery............."SELECT COUNT(*) FROM Clinics"
 connectionTimeout...............30000
 dataSource......................none
 dataSourceClassName.............none
 dataSourceJNDI..................none
 dataSourceProperties............{password=<masked>}
 driverClassName................."com.filemaker.jdbc.Driver"
 healthCheckProperties...........{}
 healthCheckRegistry.............none
 idleTimeout.....................600000
 initializationFailFast..........true
 initializationFailTimeout.......1
 isolateInternalQueries..........false
 jdbc4ConnectionTest.............false
 jdbcUrl.........................jdbc:filemaker://***:2399/ec_data
 leakDetectionThreshold..........90000
 maxLifetime.....................1800000
 maximumPoolSize.................10
 metricRegistry..................none
 metricsTrackerFactory...........none
 minimumIdle.....................10
 password........................<masked>
 poolName........................"HikariPool-1"
 readOnly........................false
 registerMbeans..................false
 scheduledExecutor...............none
 scheduledExecutorService........internal
 schema..........................none
 threadFactory...................internal
 transactionIsolation............default
 username........................"CHC"
 validationTimeout...............5000

Answer 1

The filemaker JDBC driver ignores the javax.persistence.query.timeout parameter, even though the timeout value is set in the driver's implementation of the java.sql.setQueryTimeout setter. So I resolved the problem by extending the class com.filemaker.jdbc.Driver and overriding the connect method, so that it adds the sockettimeout parameter to the connection properties. Having this param in place, the FM JDBC driver interrupts the connection if no data have been coming from the socket for the timeout period.

I've also filed an issue with filemaker: https://community.filemaker.com/message/798471