Spring boot Oracle JPA set QueryTimeout

Question

I'm using Spring Boot with Ojdbc8 18.3.0.0.0 With Hikari Datasource and JPA, all query work fine. But now I need to set Query timeout for all database query I was try many way:

javax.persistence.query.timeout=1000
spring.transaction.default-timeout=1
spring.jdbc.template.query-timeout=1
spring.jpa.properties.hibernate.c3p0.timeout=1
spring.jpa.properties.javax.persistence.query.timeout=1

Config Class:

@Configuration
public class JPAQueryTimeout {

    @Value("${spring.jpa.properties.javax.persistence.query.timeout}")
    private int queryTimeout;   

    @Bean
    public PlatformTransactionManager transactionManager() throws Exception {
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setDefaultTimeout(queryTimeout); //Put 1 seconds timeout
        return txManager;
    }
}

Query:

List<Integer> llll = manager.createNativeQuery("select test_sleep(5) from dual")
            .setHint("javax.persistence.query.timeout", 1).getResultList();

The database task take 5 second before return value, but in all of case, no error occor.

Could anyone tell me how to set query timeout?

Answer 1

You can try using the simplest solution, that is using the timeout value within @Transactional;

@Transactional(timeout = 1) // in seconds (so that is 1 second timeout)
public Foo runQuery(String id) {
    String result = repo.findById(id);
    // other logic
}

Be aware that the method annotated with @Transactional must be public for it to work properly