Overriding timeout for database connection in properties file

Question

I was wondering if there is a specific way to override database connection timeout in the properties file in my Java web project? I am using Hibernate, Spring, and MySQL DB. I have tried several different property fields and reduced the timeout time to 1 millsecond, yet the connection is still completed with transactions still being processed properly.

These are the property fields I have used to no avail...

• spring.jpa.properties.javax.persistence.query.timeout=1
• spring.jdbc.template.query-timeout=1
• hibernate.c3p0.timeout=1

Is hibernate overriding this timeout value or am I just setting it improperly? Thanks in advance!

Priggish

Are you using @Transactional methods and want configure timeout for all of these methods? – Manikin

I would ideally like to do for both unless it is impossible to do for methods without @Transactional, for instance, set a database timeout for a method that retrieves a record from the database and another method one that deletes it. – Priggish

Answer 1

Assuming that you're using Spring Boot you can try:

spring.transaction.defaultTimeout=1

This property sets defaultTimeout for transactions to 1 second.

(Looking at the source code of TransactionDefinition it seems that it is not possible to use anything more precise than seconds.)

See also: TransactionProperties

---

javax.persistence.query.timeout

This is a hint for Query. It is supposed to work if you use it like this:

entityManager.createQuery("select e from SampleEntity e")
    .setHint(QueryHints.SPEC_HINT_TIMEOUT, 1)
    .getResultList();

See also QueryHints

---

spring.jdbc.template.query-timeout

Remember that according to the JdbcTemplate#setQueryTimeout javadoc:

Any timeout specified here will be overridden by the remaining transaction timeout when executing within a transaction that has a timeout specified at the transaction level.

---

hibernate.c3p0.timeout

I suspect that this property specifies timeout for getting from the connection pool, not for a query execution

Manikin

I tried your first suggestion to no avail. I wonder if not having it in the project accessing the database project is the reason it's not overriding. I have one project with all the web features which access the DAL (Data Access Layer) Project which actually houses the DAOs(Data Access Objects)/Transactional methods. There is a database properties file and application properties file in the web project. Could the fact that there is no properties file in the DAL project be affecting it? – Priggish

Most probably yes... But I would suggest you to post MCVE if it's posiible. It is the best way to help us help you – Manikin

Not sure exactly what code to provide... using javax.persistence.EntityManager methods to persist to the database in a DAO class. Those methods are getting called from an "in-between" service layer class, another service layer is calling the methods in the "in-between" service layer class and the service method is being invoked in the controller with a POST method. The first two classes are in the DAL project, the second two classes are in the WEB project. Does that help? – Priggish

I am afraid that it won't help. I would like you to post pom/build.gradle for both projects, application.properties, your services, controller and dao. And the file structure. Also did you try spring.transaction.defaultTimeout for both of projects? Are the methods in your services @Transactional? Try to debug getTransaction and commit methods of PlatformTransactionManager. Put a breakpoint in the interface. Are they triggered? If yes then try to execute getDefaultTimeout during debugging. What value is returned? – Manikin

spring.transaction.default-timeout worked on just the transactional methods. – Priggish