Getting java.sql.SQLException: HikariDataSource HikariDataSource (hikariConnectionPool) has been closed exceptions.

[Shalaka1197]

I am getting this exception at the time of refreshing the data source. I am creating new datasource after every 1 minute.

java.sql.SQLException: HikariDataSource HikariDataSource (hikariConnectionPool) has been closed. at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:96) at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38) at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:113) at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:143) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:152) at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:420)

Below are the configurations passed at the time of connection. keepaliveTime...................0 leakDetectionThreshold..........60000 maxLifetime.....................60000 maximumPoolSize.................200 connectionTimeout...............360000

[Shalaka1197]

I am refreshing the Hikari datasource after every 1 minute of interval. I am using the spring data JPA for performing the save operations. But JPA is pointing to older bean of datasource and I am getting above exceptions.

[lfbayer]

Why would you create a new datasource once per minute? This doesn't sound like a normal use-case.

[Shalaka1197]

My database credential is getting refreshed after one minute @lfbayer .

[lfbayer]

You should not create a new datasource just for that. I would assume any existing connections are fine even if the credentials change. By recreating the datasource you are likely creating all sorts of problems for yourself. And if it is true that existing connections are not fine, then to be honest a connection pool isn't going to have any benefit for you.

You should only have to worry about new connections. In that case I highly recommend you subclass your specific datasource implementation and override the getConnection method so that you can use new credentials for each new connection.

[lfbayer]

Alternatively, you could use the mbean to update the current HikariCP pool's username and password settings on your one minute schedule. Then any new connections after that will use the new credentials. You still don't need to create a new datasource every minute for this.

[Shalaka1197]

@lfbayer How to close older connection in this scenario's. I am getting below exception for this.

java.sql.SQLTransientConnectionException: hikariConnectionPool - Connection is not available, request timed out after 60000ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)

Older connections still in connection pool.

[lfbayer]

Any existing connection should still be valid. I don't think the error you get there is related to the username or password changing. Connection is not available doesn't mean that an old connection is in the pool, it means that your other connections have never been returned to the pool and that you have hit max connections so no new connections will be created (or the pool was unable to create a new connection in the background within the given timeout).

[Shalaka1197]

@lfbayer Now I am creating the new connection after every one hour and closing older ones. But in case of any wrong password getConnection() method is not throwing any exception. Please can you reply on this issue.

HikariDataSource hds; hds.setUsername("userName"); hds.setPassword("password"); try{ Connection conn =hds.getConnection(); } catch(Exception e) {}