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?

 

posted @   nuccch  阅读(6244)  评论(0编辑  收藏  举报
编辑推荐:
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
阅读排行:
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· PPT革命!DeepSeek+Kimi=N小时工作5分钟完成?
· What?废柴, 还在本地部署DeepSeek吗?Are you kidding?
· DeepSeek企业级部署实战指南:从服务器选型到Dify私有化落地
· 程序员转型AI:行业分析
点击右上角即可分享
微信分享提示