jdbcTemplate database connection is closed in Spring Boot micro service

Question

I have a micro service that after several hours of working time connot connect to data base with a following error

Error retrieving database metadata; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

If I redeploy it, it works, that after some hours or some days error gets back.

Here is my db connection configuration class (I use 2 different data sources)

@Getter
@Configuration
public class ApiConfiguration {

    @Autowired
    private Environment env;

    @Value("${spring.datasource.url}")
    private String legacyURL;

    @Value("${spring.datasource.username}")
    private String legacyUsername;

    @Value("${spring.datasource.password}")
    private String legacyPassword;

    @Value("${spring.datasource.driverClassName}")
    private String legacyDriverClassName;

    @Bean(name = "legacyDataSource")
    @Primary
    public JdbcTemplate getLegacyJdbcTemplate() {
        DataSource ds = DataSourceBuilder.create()
                .url(legacyURL)
                .username(legacyUsername)
                .password(legacyPassword)
                .driverClassName(legacyDriverClassName)
                .build();
        return new JdbcTemplate(ds);
    }
}

application.properties

# Legacy spring.datasource.url=jdbc:sqlserver://${DB_SERVER};PortNumber=${DB_PORT};failoverPartner=${DB_FAILOVER_SERVER};databaseName=${DB_NAME};applicationName=questions-and-answers-api;
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

Answer 1

I think maybe because of network issues, your db connection is getting stale. Try adding the following entries to application.properties:

spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 3600000
spring.datasource.validationQuery = SELECT 1

This will run a background thread in the given interval and check the db connections with the validation query. If the query does not run successfully, it will evict that connection from the pool.