Spring Boot auto reconnect to PostgreSQL using JdbcTemplate and multiple datasources

Question

I have a Spring Boot v 1.5.1.RELEASE app which uses PostgreSQL 9.6 as a datasource. My app remains connected to Postgres even when idle, but if the connection is lost then the app does not reconnect and instead throws:

org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.postgresql.util.PSQLException: This connection has been closed.
    at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:366) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:212) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134) [spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:97) [spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99) [spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) [spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]

Although I am not using JPA and I believe I am using the Tomcat pool that comes with spring-boot-starter, I have read and tried the suggestions discussed here and here with no luck. In my properties file, I have tried:

#spring.datasource.tomcat.test-on-borrow=true
#spring.datasource.tomcat.validation-query=SELECT 1

#spring.datasource.tomcat.test-while-idle=true
#spring.datasource.tomcat.time-between-eviction-runs-millis=3600000
#spring.datasource.tomcat.validation-query=SELECT 1

#spring.datasource.dbcp2.test-on-borrow=true
#spring.datasource.dbcp2.validation-query=SELECT 1

spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1

However, I am using two data sources, configured like this:

@Configuration
public class DatabaseConfiguration 
{
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.ds_pgsql_rtmain")
    public DataSource rtmainDataSource() 
    {
        DataSource dataSource = DataSourceBuilder.create().build();
        return dataSource;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.ds_pgsql_pdns")
    public DataSource pdnsDataSource() 
    {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public JdbcTemplate rtmainJdbcTemplate(DataSource rtmainDataSource) 
    {
        return new JdbcTemplate(rtmainDataSource);
    }

    @Bean
    public JdbcTemplate pdnsJdbcTemplate(@Qualifier("pdnsDataSource") DataSource pdnsDataSource) {
        return new JdbcTemplate(pdnsDataSource);
    }   

}

I'm not sure if the problem is that I did not configure the data pool correctly or because the pool does not work when I manually configure the data sources. Or something else. Assistance would be greatly appreciated, thank you.

score 0 · Accepted Answer · answered Apr 21 '17 at 00:10

Yes since you're not using an auto configured data source it is not working.

Since you're applying properties from your own prefix you just have to put test-on-borrow and validation-query on your own prefix. Try this:

spring.ds_pgsql_rtmain.test-on-borrow=true
spring.ds_pgsql_rtmain.validation-query=SELECT 1

spring.ds_pgsql_pdns.test-on-borrow=true
spring.ds_pgsql_pdns.validation-query=SELECT 1

Spring Boot auto reconnect to PostgreSQL using JdbcTemplate and multiple datasources

1 Answers1