DBCP - validationQuery for different Databases

Question

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

Does using validationQuery not degrade the performance? This query runs for every connection the application gets right? — yaswanth
– yaswanth, Commented May 4, 2017 at 6:40
@yaswanth It's usually only run once at startup I would imagine, unless you have some special circumstance — turbofood
– turbofood, Commented Jan 14, 2022 at 0:05

12 revs, 8 users 68% · Accepted Answer · 2022-10-14 13:14:42Z

231

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?

edited Oct 14, 2022 at 13:14

community wiki

12 revs, 8 users 68%
bugs_

Sign up to request clarification or add additional context in comments.

3 Comments

Cjxcz Odjcayrwl Over a year ago

select 1 is also valid on postgresql

Yuci Over a year ago

select 1 also works for Microsoft SQL Server 2014 - 12.0.2000.8 (X64), Feb 20 2014 20:04:26, Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

davidemm Over a year ago

select 1 for Hive and Impala

johan · Accepted Answer · 2016-01-20 13:26:45Z

15

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

edited Jan 20, 2016 at 13:26

johan

5586 silver badges19 bronze badges

answered Dec 26, 2013 at 9:10

yossis

1,2732 gold badges11 silver badges11 bronze badges

Comments

Deepan Prabhu Babu · Accepted Answer · 2015-06-26 22:06:59Z

4

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

answered Jun 26, 2015 at 22:06

Deepan Prabhu Babu

94012 silver badges19 bronze badges

1 Comment

bugs_ Over a year ago

Thanks - I have just added it to list

Amit P · Accepted Answer · 2021-03-08 09:53:39Z

1

For MairaDB validation query is "select 1" .

answered Mar 8, 2021 at 9:53

Amit P

5449 silver badges22 bronze badges

1 Comment

bugs_ Over a year ago

Thanks - I have just added it to list

Collectives™ on Stack Overflow

DBCP - validationQuery for different Databases

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related