DBCP - validationQuery for different Databases

85
votes

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

4
javaconnection-poolingapache-commons-dbcp
Does using validationQuery not degrade the performance? This query runs for every connection the application gets right? – yaswanth

4 Answers

215
votes

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

15
votes

For MySQL with the Connector/J driver, there's a lightweight validation query that just sends a ping to the server and returns a dummy result set. The validation query can be (or should start with) exactly the following string:

/* ping */

For more infor refer to the Validating Connections in the MySQL driver manual

4
votes

For Informix, The validation query is, select 1 from systables

1
votes

For MairaDB validation query is "select 1" .