DBCP – validationQuery for different Databases

I use DBCP pool and I want use testOnBorrow and testOnReturn to test if connection is still valid.
Unfortunately I have to set property validationQuery to make it work.

Question: What value should be in validationQuery?

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

Problem is that we use various databases (DB2, Oracle, hsqldb).

Answer

There is not only one validationQuery for all databases. On each database you have to use different validationQuery.

After few hours of googling and testing I have collected this table:

Database validationQuery notes

• hsqldb – select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle – select 1 from dual
• DB2 – select 1 from sysibm.sysdummy1
• mysql – /* ping */ 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
• MariaDb – select 1
• Informix – select 1 from systables
• Hive – select 1
• Impala – select 1

I wrote about it on my blog – validation query for various databases.

In advance there is an example of class, which return validationQuery according to JDBC driver.

Or does anybody have better solution?