不同数据库的 DBCP-validationQuery

我使用 DBCP 池，我想使用 TestOnBorrow和 TestOnReturn来测试连接是否仍然有效。
不幸的是，我必须设置属性 validationQuery 才能使其正常工作。

问题: ValidationQuery 中应该有什么值？

我知道: validationQuery 必须是一个 SQLSELECT 语句，返回至少一行。

问题是我们使用各种数据库(DB2、 Oracle、 hsqldb)。

71513 次浏览
小开
最佳答案

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?

小开

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

小开

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

小开

For MairaDB validation query is "select 1" .


提交答案