MySQL validatequery,DBCP - 针对不同数据库的validationQuery

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).

解决方案

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 - 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

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

Or does anybody have better solution?