How to configure custom database connection timeout in Spring Boot application?

Question

In my Spring boot(2.0.7 RELEASE) application I am not able to manually set/override the timeout for the database connections in the application.properites file. I am using JPA, Hibernate, Tomcat connection pool and Postgres.

I've researched thoroughly and found very similar questions :

The reason I ask new question is because neither of the questions above have an accepted answer nor a confirmed working solution. I tried including each proposed solution in my application.properties file with no success.

Also, as mentioned in question 2: if I add parameter 'timeout = someSeconds' in the @Transactional annotation, the connection timeouts as expected but if I try extracting it in the application.properties it fails and timeouts for the default time. The problem here is that I want all connections to timeout in the given time not only the transactions.

Things I've tried in the application.properties (The desired timeout is 4 seconds):

spring.jpa.properties.javax.persistence.query.timeout=4000
spring.jdbc.template.query-timeout=4
spring.transaction.defaultTimeout=4
spring.datasource.tomcat.validation-query-timeout=4

Materials I've read:

Am I missing some property? Does anyone know why the timeout can't be overridden via the application.properties file?

Thanks in advance.

As per Spring 2.0.7 properties(docs.spring.io/spring-boot/docs/2.0.7.RELEASE/reference/…) the property key is spring.transaction.default-timeout and not spring.transaction.defaultTimeout.Try it and let us know. — Jaspreet Jolly
– Jaspreet Jolly, Commented Apr 4, 2019 at 10:04
Thanks for the advice. Unfortunately, the outcome is the same - the connection does not time out in the specified time. Moreover, I am trying to find a solution that comprises all connections and not only the transactions. — yordanoff
– yordanoff, Commented Apr 4, 2019 at 10:25
Did you find a solution to this? Facing the same issue here... — Steven
– Steven, Commented Aug 22, 2019 at 11:38

Arya · Accepted Answer · 2021-04-27 06:13:31Z

There are at least 3 time-outs to configure:

Transaction timeouts, which you already did. I declared mine in the transactionManager bean:

txManager.setDefaultTimeout(myDefaultValue);

Query timeouts(which obviously does not need @transactional), which you already did and also explained here
Network timeouts(Read this excellent article).

For my case, i am using Oracle, and my bean configuration is as follows:

    @Bean
    public HikariDataSource dataSource() {
        
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName(springDatasourceDriverClassName);
        ds.setJdbcUrl(springDatasourceUrl);
        ds.setUsername(springDatasourceUsername);
        ds.setPassword(springDatasourcePassword);
        ds.setDataSourceProperties(oracleProperties());
        
        return ds;
    }
    
    Properties oracleProperties() {
        Properties properties = new Properties();
        
        properties.put("oracle.net.CONNECT_TIMEOUT", 10000);
        properties.put("oracle.net.READ_TIMEOUT", 10000);
        properties.put("oracle.jdbc.ReadTimeout", 10000);

        return properties;
    }

And if you do not want to configure a bean for the DataSource(which is what most people will do), you can configure the network timeout properties in application.properties:

spring.datasource.hikari.data-source-properties.oracle.net.CONNECT_TIMEOUT=10000
spring.datasource.hikari.data-source-properties.oracle.net.READ_TIMEOUT=10000
spring.datasource.hikari.data-source-properties.oracle.jdbc.ReadTimeout=10000

Nicolas Ordonez Chala · Accepted Answer · 2020-03-31 18:57:02Z

6

Depending on your datasource, but you can try this:

spring.datasource.hikari.max-lifetime=1000
spring.datasource.hikari.connection-timeout=1000
spring.datasource.hikari.validation-timeout=1000
spring.datasource.hikari.maximum-pool-size=10

answered Mar 31, 2020 at 18:57

Nicolas Ordonez Chala

861 silver badge2 bronze badges

Comments

BeCase · Accepted Answer · 2024-12-19 10:02:00Z

In Kotlin you can do that:

@Configuration
class DatabaseConfiguration {
    @Value("\${application.datasource.query-timeout:60000}")
    private var queryTimeout: Int = 60000
    @Bean
    @ConfigurationProperties(prefix = "application.datasource")
    fun dataSource(): DataSource {
        return DataSourceBuilder
            .create()
            .type(HikariDataSource::class.java)
            .build()
    }
    @Bean
    fun entityManagerFactory(dataSource: DataSource, builder: EntityManagerFactoryBuilder): LocalContainerEntityManagerFactoryBean {
        return builder
            .dataSource(dataSource)
            .properties(mapOf("javax.persistence.query.timeout" to queryTimeout))
            .build()
    }
    @Bean
    fun transactionManager(entityManagerFactory: LocalContainerEntityManagerFactoryBean): PlatformTransactionManager {
        return JpaTransactionManager(entityManagerFactory.getObject())
    }
}

Collectives™ on Stack Overflow

How to configure custom database connection timeout in Spring Boot application?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related