Describe a scenario where you would need to customize HikariCP's connection test query and explain why a default test query might not suffice.
Java interview question for Advanced practice.
Answer
A custom HikariCP connection test query might be needed in a scenario involving a database proxy or load balancer that has its own session timeout. The default test query, often a simple SELECT 1, only checks if the database server is reachable and can execute a command. It does not validate the state of the session on the proxy. For example, if a firewall or a database proxy between the application and the database silently drops idle connections after a certain period, the connection pool might hold connections that are no longer valid from the proxy's perspective. When the application tries to use one of these connections, the operation will fail. A simple SELECT 1 sent directly to the database might succeed, but any query sent through the now-terminated proxy session would fail. In this case, a more sophisticated custom test query might be required to ensure the entire path from the application to the database is valid. An alternative is to rely on TCP keep-alive settings and ensure the maxLifetime and idleTimeout in HikariCP are shorter than any network or proxy timeouts.
Explanation
HikariCP offers the ability to use a custom ConnectionValidator interface if you need even more control over how connections are validated.