DBCP - validationQuery for different databases

Question

I am using the DBCP pool and I want to use testOnBorrow and testOnReturn to verify that the connection is working.
Unfortunately, I have to set the validationQuery property for it to work.

Question: What value should be in validationQuery?

I know that: validationQuery must be an SQL SELECT statement that returns at least one row.

The problem is that we use different databases (DB2, Oracle, hsqldb).

Answer 1

There is more than one validationQuery for all databases. In each database, you must use different validationQuery.

After hours of searching and testing, I put together this table:

Validate Database Validation

• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle - select 1 from dual
• DB2 - select 1 from sysibm.sysdummy1
• mysql - select 1
• Microsoft SQL Server - select 1 (tested on SQL Server 9.0, 10.5 [2008])
• postgresql - select 1
• ingres - select 1
• derby - values 1
• H2 - select 1
• Firebird - select 1 from rdb$database

I wrote a validation request for various databases on this blog.

There is an example class in advance that returns validationQuery according to the JDBC driver.

Or does anyone have a better solution?

Answer 2

For MySQL with the Connector / J driver, there is an easy validation request that simply sends ping to the server and returns a dummy result set. A validation request can be (or must begin with) exactly the following line:

 /* ping */ 

For more information, see “ Confirming Connections” in the MySQL manual.

Answer 3

For Informix, validation request, select 1 from systables