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?

Can you try adding @Transactional(timeout = 1) on the method that is querying the db? Be aware that the method must be public. — bvrakvs
– bvrakvs, Commented Sep 20, 2019 at 2:00
Thank you for your anwser. I was try and it's work. The exption occur: org.hibernate.TransactionException: Unable to commit against JDBC Connection ... ORA-12592: TNS:bad packet .... — user3611168
– user3611168, Commented Sep 20, 2019 at 2:07
But why the exeption is: "ORA-12592: TNS:bad packet" instead of "ORA-01013: user requested cancel of current operation" ? — user3611168
– user3611168, Commented Sep 20, 2019 at 2:09
Hmm, that is weird, can you try to drop the usage of PlatformTransactionManager ? & use maybe proper repositories or queries with spring data jpa? & drop any other extra config you added to enforce the timeout, just use @Transactional with timeout on vanilla spring data jpa — bvrakvs
– bvrakvs, Commented Sep 20, 2019 at 2:14

bvrakvs · Accepted Answer · 2019-09-20 02:10:38Z

2

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

answered Sep 20, 2019 at 2:10

bvrakvs

14.7k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3611168 Over a year ago

Thanks for your anwser. After using that Anonation, the query has been timeout, the exeption occur is "ORA-12592: TNS:bad packet" And then I put System.setProperty("oracle.net.disableOob", "true"); . Now it work fine with exeption throw: "ORA-01013: user requested cancel of current operation"

bvrakvs Over a year ago

@user3611168 I see, I saw that solution as well, but consider dropping the weird structures like PlatformTransactionManager and using proper spring data jpa features, then you might not need to set that property

user3611168 Over a year ago

I was remove all of other config when using your suggestion,Thank you. My problem has been resolved.

MozenRath Over a year ago

how can we do it when no transaction is required but only timeout?

Collectives™ on Stack Overflow

Spring boot Oracle JPA set QueryTimeout

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related