How to automatically reconnect a DataSource connection in Spring?

Question

Problem: when my spring application is running, and meanwhile the database server is stopped/restarted, then then db connection is lost and never restored.

I tested as follows:

• execute query: OK
• service mysql stop

• execute query: exception:

  Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException:

  com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

• service mysql start

• execute query: still exception!

Question: how can I tell spring DataSource to automatically reconnect after connection has been lost?

This is my configuration:

spring.datasource.url=jdbc:mysql://localhost/tablename?useSSL=false
spring.datasource.username=root
spring.datasource.password=rootpw
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.tomcat.validation-query=SELECT 1
spring.datasource.tomcat.validation-query-timeout=5000
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-on-connect=true

Solution 1

My bad, I had a misconfiguration. The config above is all that's needed for auto reconnect.

Solution 2

I'm using a connection pool like HikariCP to do a reconnect automatically.

see the example at Stackoverflow: How do I configure HikariCP in my Spring Boot app in my application.properties files?