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
select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
select 1 from dual
select 1 from sysibm.sysdummy1
select 1
select 1
(tested on SQL-Server 9.0, 10.5 [2008])select 1
select 1
values 1
select 1
select 1 from rdb$database
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?