JDBC/DBCP连接池的testQuery/validationQuery设置

转载了新浪@mryqu的博客,并添写了DBCP的设置,以下是正文:

 

在《Tomcat中使用Connector/J连接MySQL的超时问题》帖子中想要增加对连接池中连接的测试/验证,防止数据库认为连接已死而Web应用服务器认为连接还有效的问题,Mysql文档中提到Tomcat文档中的例子中用的是validationQuery,但是网上还有很多帖子写的是testQuery,到底用哪一个呢?

 

原来这跟连接池的实现有关:

连接池实现该功能属性名
The Tomcat JDBC Connection PoolvalidationQuery
The Apache Commons DBCP Connection PoolvalidationQuery
c3p0 - JDBC3 Connection and Statement PoolingpreferredTestQuery
Atomikos:Tomcat Spring ActiveMQ MySQL JMX Integration
分析Atomikos数据连接池源码,弄清testQuery
testQuery



此外,测试/验证连接池连接的SQL语句也因数据库而异:
Efficient SQL test query or validation query that will work across all (or most) databases
DBCP - validationQuery for different Databases

综合上述两个帖子,汇总结果如下:

JDBC:

数据库测试/验证查询
MySQLSELECT 1
PostgreSQLSELECT 1
Microsoft SQL ServerSELECT 1
SQLiteSELECT 1
H2SELECT 1
IngresSELECT 1
Oracleselect 1 from dual
DB2select 1 from sysibm.sysdummy1 或
SELECT current date FROM sysibm.sysdummy1
Apache DerbyVALUES 1 FROM SYSIBM.SYSDUMMY1 或
SELECT 1 FROM SYSIBM.SYSDUMMY1
HSQLDBSELECT 1 FROM any_existing_table WHERE 1=0 或
SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
Informixselect count(*) from systables

 

DBCP:

DatabasevalidationQuerynotes
hsqldbselect 1 from INFORMATION_SCHEMA.SYSTEM_USERS
Oracleselect 1 from dual
DB2select 1 from sysibm.sysdummy1
mysqlselect 1not tested
microsoft sqlselect 1not tested
postgresqlselect version();not tested
ingresselect 1not tested
derbyvalues 1not tested
H2select 1not tested

原文链接:http://blog.sina.com.cn/s/blog_72ef7bea0101hpj8.html

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页