高效的SQL测试查询或验证查询，将在所有(或大多数)数据库中工作

Question

许多数据库连接池库提供了测试它们的SQL连接是否空闲的能力。例如，JDBC池库c3p0有一个名为preferredTestQuery的属性，它将在配置的间隔内在连接上执行。类似地，Apache有validationQuery。

我看到的许多示例 查询都是针对MySQL的，建议使用SELECT 1;作为测试查询的值。但是，这个查询不适用于某些数据库(例如，SELECT 1需要FROM子句的HSQLDB )。

是否存在一个与数据库无关的查询，该查询具有相当的效率，但对所有SQL数据库都有效？

编辑：

如果没有(似乎是这样)，是否有人会建议一组SQL查询，这些查询将适用于不同的数据库提供程序？我的意图是以编程方式根据数据库提供程序配置确定可以使用的语句。

Answer 1

表格为jOOQ的select(inline(1))查询列出了以下内容：

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

Answer 2

在进行了一些研究之后，并从以下一些答案中得到了帮助：

SELECT 1

• H2
• MySQL
• Microsoft (根据NimChimpsky)
• PostgreSQL
• SQLite
• 蜂巢

SELECT 1 FROM DUAL

• 甲骨文

SELECT 1 FROM any_existing_table WHERE 1=0

或

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

或

CALL NOW()

• HSQLDB (用1.8.0.10版进行测试) 注意:我尝试在第二个查询中使用WHERE 1=0子句，但它不作为Apache的validationQuery__的值，因为该查询不返回任何行

VALUES 1或SELECT 1 FROM SYSIBM.SYSDUMMY1

• Apache (通过台索格)

SELECT 1 FROM SYSIBM.SYSDUMMY1

• DB2

select count(*) from systables

• Informix

Answer 3

如果您的驱动程序兼容JDBC 4，则不需要专门的查询来测试连接。相反，有Connection.isValid来测试连接。

从2006年开始，JDBC 4是Java 6的一部分，您的驱动程序现在应该支持它了！

著名的连接池(如HikariCP )仍然有一个用于指定测试查询的配置参数，但强烈反对使用它：

connectionTestQuery 如果您的驱动程序支持JDBC4，我们强烈建议不要设置此属性。这是针对不支持JDBC4 Connection.isValid() API的“遗留”数据库的。这是在从池中向您提供连接之前执行的查询，以验证到数据库的连接是否仍然有效。同样，尝试在没有此属性的情况下运行池，如果您的驱动程序不兼容HikariCP，则JDBC4将记录错误以通知您。默认值:无