JDBCConnectionException "Unable to acquire JDBC Connection" with spring boot

Question

My Spring Boot works fine when I generate report using Japser report.

The problem I face is that the application throws a hibernate exception :

Unable to acquire JDBC Connection

I get this error after I generate report many times .

1 running delayed actions on {type: MASTER, group: null, band: 0}
2018-09-20 14:27:55.536 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.fill.JRBaseFiller           : Fill 1: ended
2018-09-20 14:27:55.536 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.fill.JRFillDataset          : Fill 1: closing query executer
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block DEVANAGARI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block BENGALI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TELUGU
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TAMIL
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block GUJARATI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block KANNADA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block MALAYALAM
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block ORIYA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block GURMUKHI
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block SINHALA
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block TIBETAN
2018-09-20 14:27:55.539 DEBUG 46148 --- [ XNIO-2 task-27] 
n.s.j.engine.export.JRPdfExporter        : glyph renderer block KHMER
2018-09-20 14:28:25.549  WARN 46148 --- [ XNIO-2 task-27] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2018-09-20 14:28:25.550 ERROR 46148 --- [ XNIO-2 task-27] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not 
available, request timed out after 30000ms.
2018-09-20 14:28:25.556 ERROR 46148 --- [ XNIO-2 task-27] 
c.n.xx.aop.logging.LoggingAspect    : Exception in 
com.xx.xx.web.rest.GrueResource.generateRapportGrue() with cause = 
'org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection' and exception = 'Could not open JPA EntityManager for 
transaction; nested exception is 
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection'

org.springframework.transaction.CannotCreateTransactionException: Could not 
open JPA EntityManager for transaction; nested exception is 
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC 
Connection

Collective

Answer 1

You run out of connections.

Try to set the Hikari Connection Pool to a bigger number:

spring.datasource.hikari.maximum-pool-size=10

Thermosphere

It depends how long your query runs to get the data for the reports. So you should optimize that. At the end you can have as many connections to the database as your database allows. – Thermosphere

About database connexion, for any report generation, a new instance of DataSource is generated or it is the same one ? – Collective

Hikari is a connection pool. So it tries to reuse the connections. – Thermosphere

So, your solution says to accepts 10 different connexions at the same time, or it means to accept 10 instances of the same DataSource object ? – Collective

You are mixing things. A Datasource reflects just the configuration of the database connection. And the connection is the physical connection to the database. So the pool would hold a max of 10 connections. – Thermosphere

Thanks for your explanation. It helped me. But the default value of maximum-pool-size is 10. I just need to put a bigger number – Collective

How much did you increase it? did you follow any metric to increase pool size? – Bartels

According to Hikari official article the pool size formula is connections = ((core_count * 2) + effective_spindle_count) – Breastfeed

Answer 2

I did face the same issues every 2nd day when I was working with jasper reports and finally fixed it by proper understanding because when we work with query based reports we are responsible to close the connection of data source our own so that it return to the pool and available for next use. You have to take care of multiple things 1- get connection from dataSource

DataSourceUtils.getConnection(ds);

2-You must have to close data source connection in any case :better to close it in finally block so that in case of exception connection do not remain open .

finally{closeConnection(con,dataSource);}
public void closeConnection(Connection con,DataSource ds) {

 if (con != null) {
DataSourceUtils.releaseConnection(con, ds);
 }
}

3-Made changes in application.properties file

spring.datasource.hikari.connectionTimeout=30000   
spring.datasource.hikari.idleTimeout=600000          
spring.datasource.hikari.maxLifetime=1800000      
spring.datasource.hikari.maximumPoolSize=30

Lindbergh