In any database connection pool, there is a risk of stale connections due to network outages or database server restarts. How do we refresh the connection pool without restarting the client application?
The standard technique used is to plug-in a validation query; that gets fired every time a connection is requested from the pool. This validation query is typically a default test query that does not result in any IO / disk access. Examples of validation queries for different databases are given below:
Next we added the following properties in our application.properties file. We will pick up these properties to create our HikariCP connection. pool.
Finally we wrote a @Configuration bean to wire up the HirakiCP datasource as below:
The standard technique used is to plug-in a validation query; that gets fired every time a connection is requested from the pool. This validation query is typically a default test query that does not result in any IO / disk access. Examples of validation queries for different databases are given below:
- Oracle - select 1 from dual
- SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
- Postgresql - select 1
- hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
- derby - values 1
- H2 - select 1
- DB2 - select 1 from sysibm.sysdummy1
- Mysql - select 1
We were using Spring Boot that uses the Tomcat JDBC Connection Pool by default. We tried setting all the parameters required for the validation check as given here, but in vain.
Finally we decided to go with HikariCP connection pool as suggested here.
First, we added the dependency in our maven pom as shown below.
Next we added the following properties in our application.properties file. We will pick up these properties to create our HikariCP connection. pool.
Finally we wrote a @Configuration bean to wire up the HirakiCP datasource as below:
No comments:
Post a Comment