DBCP - validationQuery for different Databases
apache-commons-dbcp, connection-pooling, java
Solution
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?
Problem
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).