使用 JDBCTemplate 和 c3p0 的连接过多

发布于 2024-11-26 08:37:02 字数 1627 浏览 1 评论 0原文

我正在开发一个使用 Spring、JDBCTemplate 和 c3p0 进行数据库访问的 Web 应用程序。

我经常遇到服务器冻结的情况，我很确定这是由于繁忙的数据库连接数量造成的。如果我使用 jconsole 观察应用程序行为，我可以看到已达到 ComboPooledDataSource 的 maxPoolSize，并且服务器不再加载页面。

这里是有用的代码：

数据源定义：

<Resource auth="Container" description="GDLWeb DB Connection"
    driverClass="org.postgresql.Driver"
    maxPoolSize="16"
    minPoolSize="1"
    acquireIncrement="1"
    maxIdleTime="60"
    maxStatements="0"
    idleConnectionTestPeriod="1800"
    acquireRetryAttempts="30"
    breakAfterAcquireFailure="true"
    name="jdbc/gdlweb"
    user="gdlweb"
    password=""
    factory="org.apache.naming.factory.BeanFactory"
    type="com.mchange.v2.c3p0.ComboPooledDataSource"
    jdbcUrl="jdbc:postgresql://localhost:5432/postgres"
/>

典型的访问方法（在 DAO 类中）：

protected T getPersistentObject(
        final String tableName,
        final List<WhereClause> whereParams,
        final RowMapper<T> rowMapper) {

    try {
        log.debug(this, "get " + tableName + " " + whereParams);
        return (T) getTemplate().queryForObject(
                generateSelectStar(tableName, whereParams),
                extractValueMap(whereParams),
                rowMapper);
    } catch (final EmptyResultDataAccessException e) {
        log.warning(this, "No " + tableName + " found with " + whereParams + " in the DB!");
        return null;
    }
}

我尝试将 maxPoolSize 增加到 100，这是我的 postgresql 服务器中定义的 maxConnections。这样，我可以看到在 postgresql 服务器崩溃之前当前打开了 43 个繁忙连接。

我可能以错误的方式使用 JDBCTemplate，但我不知道在哪里。

谢谢。

原文

I am developing a web application with database access using Spring, JDBCTemplate and c3p0.

I often have a server freeze, and I am pretty sure it comes from the number of busy database connections. If I watch the application behavior, using jconsole, I can see that the maxPoolSize of the ComboPooledDataSource is reached, and the server doesn't load a page anymore.

Here is the useful code:

DataSource definition:

<Resource auth="Container" description="GDLWeb DB Connection"
    driverClass="org.postgresql.Driver"
    maxPoolSize="16"
    minPoolSize="1"
    acquireIncrement="1"
    maxIdleTime="60"
    maxStatements="0"
    idleConnectionTestPeriod="1800"
    acquireRetryAttempts="30"
    breakAfterAcquireFailure="true"
    name="jdbc/gdlweb"
    user="gdlweb"
    password=""
    factory="org.apache.naming.factory.BeanFactory"
    type="com.mchange.v2.c3p0.ComboPooledDataSource"
    jdbcUrl="jdbc:postgresql://localhost:5432/postgres"
/>

Typical access method (in DAO class):

protected T getPersistentObject(
        final String tableName,
        final List<WhereClause> whereParams,
        final RowMapper<T> rowMapper) {

    try {
        log.debug(this, "get " + tableName + " " + whereParams);
        return (T) getTemplate().queryForObject(
                generateSelectStar(tableName, whereParams),
                extractValueMap(whereParams),
                rowMapper);
    } catch (final EmptyResultDataAccessException e) {
        log.warning(this, "No " + tableName + " found with " + whereParams + " in the DB!");
        return null;
    }
}

I tried to increase the maxPoolSize to 100, which is the maxConnections defined in my postgresql server. This way, I could see that there were 43 busy connections currently openned, just before the postgresql server crashes.

I am probably using JDBCTemplate the wrong way, but I don't know where.

Thanks.

分享到QQ

分享到微博