c3p0配置之preferredTestQuery参数默认值探秘
http://www.mchange.com/projects/c3p0/
c3p0的配置参数preferredTestQuery用于检测数据库连接测试,检测数据库是否能连接成功。
Default: null Defines the query that will be executed for all connection tests, if the default ConnectionTester (or some other implementation of QueryConnectionTester, or better yet FullQueryConnectionTester) is being used.
Defining a preferredTestQuery that will execute quickly in your database may dramatically speed up Connection tests. (If no preferredTestQuery is set, the default ConnectionTester executes a getTables() call on the
Connection's DatabaseMetaData. Depending on your database, this may execute more slowly than a "normal" database query.) NOTE: The table against which your preferredTestQuery will be run must exist in the
database schema prior to your initialization of your DataSource. If your application defines its own schema, try automaticTestTable instead. [See "Configuring Connection Testing"]
与之对应的是参数:connectionTesterClassName,配置用于c3p0连接测试的实现类。
Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester The fully qualified class-name of an implememtation of the ConnectionTester interface, or QueryConnectionTester if you would like instances to have access to a user-configured preferredTestQuery. This can be
used to customize how c3p0 DataSources test Connections, but with the introduction of automaticTestTable and preferredTestQuery configuration parameters, "rolling your own" should be overkill for most users.
[See "Configuring Connection Testing"]
connectionTesterClassName参数值必须实现接口:com.mchange.v2.c3p0.ConnectionTester,跟踪源码发现,其继承关系如下:
- com.mchange.v2.c3p0.ConnectionTester - com.mchange.v2.c3p0.QueryConnectionTester - com.mchange.v2.c3p0.FullQueryConnectionTester - com.mchange.v2.c3p0.UnifiedConnectionTester - com.mchange.v2.c3p0.AbstractConnectionTester - com.mchange.v2.c3p0.impl.DefaultConnectionTester
通常,我们都可能不会配置这2个参数,而是直接使用c3p0的默认配置。
那么,它们的默认值分别是什么呢?
com.mchange.v2.c3p0.impl.C3P0Defaults中定义了c3p0的默认参数配置,其中:
private final static ConnectionTester CONNECTION_TESTER = new DefaultConnectionTester();
显然,当没有明确定义参数connectionTesterClassName值时,c3p0默认使用的是com.mchange.v2.c3p0.impl.DefaultConnectionTester实现。
com.mchange.v2.c3p0.impl.DefaultConnectionTester定义如下方法:
public int activeCheckConnection(Connection c, String query, Throwable[] rootCauseOutParamHolder) { // if (Debug.DEBUG && Debug.TRACE == Debug.TRACE_MAX && logger.isLoggable( MLevel.FINER ) ) // logger.finer("Entering DefaultConnectionTester.activeCheckConnection(Connection c, String query). [query=" + query + "]"); if (query == null) return activeCheckConnectionNoQuery( c, rootCauseOutParamHolder); else { ..... } }
当没有明确定义preferredTestQuery值时,c3p0执行如下查询:
private int activeCheckConnectionNoQuery(Connection c, Throwable[] rootCauseOutParamHolder) { // if (Debug.DEBUG && Debug.TRACE == Debug.TRACE_MAX && logger.isLoggable( MLevel.FINER ) ) // logger.finer("Entering DefaultConnectionTester.activeCheckConnection(Connection c). [using default system-table query]"); ResultSet rs = null; try { rs = c.getMetaData().getTables( null, null, "PROBABLYNOT", new String[] {"TABLE"} ); return CONNECTION_IS_OKAY; } }
com.mysql.jdbc.DatabaseMetaData实现如下:
public java.sql.ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, final String[] types) throws SQLException { ... try {
// 执行sql语句,检测mysql是否可以连通 results = stmt.executeQuery((!DatabaseMetaData.this.conn.versionMeetsMinimum(5, 0, 2) ? "SHOW TABLES FROM " : "SHOW FULL TABLES FROM ") + StringUtils.quoteIdentifier(catalogStr, DatabaseMetaData.this.quotedId, DatabaseMetaData.this.conn.getPedantic()) + " LIKE " + StringUtils.quoteIdentifier(tableNamePat, "'", true)); } catch (SQLException sqlEx) { if (SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE.equals(sqlEx.getSQLState())) { throw sqlEx; } } ... }
调试后发现,此时执行的sql语句为:SHOW FULL TABLES FROM `dbname` LIKE 'PROBABLYNOT'(dbname为实际数据库名称)即为preferredTestQuery参数的默认值。
执行该语句不返回任何记录,但是可以通过该语句检测mysql是否可以连通。
总结:
1. 通常不需要明确指定connectionTesterClassName参数,使用默认实现即可。
2. preferredTestQuery参数值最好明确配置,不要使用默认值。该参数通常配置为:"select 1" ,效率比"SHOW FULL TABLES FROM `dbname` LIKE 'PROBABLYNOT''高。
【参考】
http://josh-persistence.iteye.com/blog/2229929 深入浅出数据库连接池c3p0
https://stackoverflow.com/questions/30521146/how-configure-connection-existence-check-in-c3p0 How configure connection existence check in C3P0?
作者:编程随笔
出处:http://www.cnblogs.com/nuccch/
声明:本文版权归作者和博客园共有,欢迎转载,但请在文章页面明显位置给出原文连接。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· PPT革命!DeepSeek+Kimi=N小时工作5分钟完成?
· What?废柴, 还在本地部署DeepSeek吗?Are you kidding?
· DeepSeek企业级部署实战指南:从服务器选型到Dify私有化落地
· 程序员转型AI:行业分析