JDBCConnectionException: Unable to acquire JDBC Connection

Question

I have seen lot of posts about such issues but I did not get proper solutions yet.

I have a Spring boot application which is being called from other source on regular intervals daily. Recently we are seeing JDBCConnectionException

Below is the error stackTrace

WARN o.h.e.jdbc.spi.SqlExceptionHelper@logExceptions:137 - SQL Error: 0, SQLState: null
ERROR o.h.e.jdbc.spi.SqlExceptionHelper@logExceptions:142 - HikariPool-1 - Connection is not available, request timed out after 928162ms.
ERROR c.w.locationmaster.service.DBService@getAllCustomers:110 - 
Exception in getAllCustomers(): Exception org.springframework.dao.DataAccessResourceFailureException: Unable to acquire JDBC Connection; 
nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection, stackTrace 
[org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:275), 
org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253), 
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527), 
org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61), 
org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242), 
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke

I use

Springboot 2.1.6
gradle-5.4.1

Gradle:

plugins {
    id 'org.springframework.boot' version '2.1.6.RELEASE'
    id 'java'
}

apply plugin: 'io.spring.dependency-management'
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'jacoco'
apply plugin: 'checkstyle'
apply plugin: 'jdepend'

sourceCompatibility = 1.11

bootJar {
    baseName = 'customers-service'
}

checkstyle {
    ignoreFailures = true
    toolVersion = '8.2'
    configDir = file("$rootProject.projectDir/etc/checkstyle")
    System.setProperty('checkstyle.cache.file', String.format('%s/%s', buildDir, 'checkstyle.cachefile'))
}

repositories {
    mavenCentral()
}

dependencies {
    implementation('org.springframework.boot:spring-boot-starter')
    compile 'org.springframework.boot:spring-boot-starter-actuator'
    compile('org.springframework.boot:spring-boot-starter-security')
    compile('org.springframework.boot:spring-boot-starter-cache')
    compile 'org.springframework.boot:spring-boot-starter-data-jpa'
    compile 'org.springframework.boot:spring-boot-starter-mail'
    compile 'org.springframework.boot:spring-boot-starter-web'

    compile('org.springframework.boot:spring-boot-starter-jdbc') {
        exclude group: 'org.apache.tomcat', module: 'tomcat-jdbc'
    }
    compile('com.fasterxml.jackson.core:jackson-annotations:2.9.4')
    compile('com.fasterxml.jackson.core:jackson-databind:2.9.4')

    compile('org.apache.httpcomponents:httpclient:4.3.4')

    compile('org.apache.commons:commons-lang3:3.5')
    compile('org.apache.commons:commons-collections4:4.0')

    testCompile('org.springframework.boot:spring-boot-starter-test')
    runtime('com.microsoft:sqljdbc4:4.0') {
        exclude group: 'org.apache.tomcat', module: 'tomcat-jdbc'
    }
}

application.properties

server.servlet.context-path=/customers
server.port=XXXX

spring.security.user.name=XXXX
spring.security.user.password=XXXX

management.endpoints.web.exposure.include=*
management.endpoint.shutdown.enabled=true
management.health.ldap.enabled=false
management.endpoints.web.cors.allow-credentials=true

spring.datasource.url=jdbc:sqlserver://XXXX;databaseName=XXX
spring.datasource.username=XXXX
spring.datasource.password=XXXX
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.platform=sqlserver

spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2014Dialect
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy

spring.datasource.validation-query=select 1
spring.datasource.testOnBorrow=true

I have @Repository class and a method with

@Transactional(readOnly = true) 

to retrieve records from the database, is @Transactional causing the issue?

Please advice me. Thanks in advance

Answer 1

It´s not @Transactional, it does not even reach this point.

Your application can not establish the DB connection:

HikariPool-1 - Connection is not available, request timed out

It must be a problem in the DB pool configuration (wrong DB url?).