使用Spring的Tomcat数据源池中的所有连接都处于活动状态

Question

我的Spring Boot应用程序使用JDBCTemplate将SQL查询发送到PostgreSQL数据库。似乎每次模板从池中获取连接时，都不会释放连接。活动连接（datasource.primary.active）的数量一直在增加。

在日志中，使用JDBCTemplate进行SQL查询后，我可以看到：

DEBUG o.s.j.d.DataSourceUtils - Returning JDBC Connection to DataSource

但空闲连接数保持不变，活动连接数没有减少。当达到最大值时，就无法检索连接以执行查询。

所以，我认为数据源池的连接不会返回，你知道吗？

这是Actuator获得的数据源配置：

  "dataSource": {
        "prefix": "spring.datasource.tomcat",
        "properties": {
            "connectionProperties": null,
            "propagateInterruptState": false,
            "validator": null,
            "useDisposableConnectionFacade": true,
            "defaultCatalog": null,
            "validationInterval": 3000,
            "jmxEnabled": true,
            "ignoreExceptionOnPreLoad": false,
            "logAbandoned": false,
            "commitOnReturn": false,
            "password": "******",
            "maxIdle": 100,
            "testWhileIdle": false,
            "removeAbandoned": false,
            "poolProperties": {
                "dbProperties": {
                    "user": "postgres",
                    "password": "******"
                },
                "url": "jdbc:postgresql://localhost:5432/tvir",
                "driverClassName": "org.postgresql.Driver",
                "defaultAutoCommit": null,
                "defaultReadOnly": null,
                "defaultTransactionIsolation": -1,
                "defaultCatalog": null,
                "connectionProperties": null,
                "initialSize": 10,
                "maxActive": 100,
                "maxIdle": 100,
                "minIdle": 10,
                "maxWait": 30000,
                "validationQuery": "SELECT 1",
                "validationQueryTimeout": -1,
                "validatorClassName": null,
                "validator": null,
                "testOnBorrow": true,
                "testOnReturn": false,
                "testWhileIdle": false,
                "timeBetweenEvictionRunsMillis": 5000,
                "numTestsPerEvictionRun": 0,
                "minEvictableIdleTimeMillis": 60000,
                "accessToUnderlyingConnectionAllowed": true,
                "removeAbandoned": false,
                "removeAbandonedTimeout": 60,
                "logAbandoned": false,
                "name": "Tomcat Connection Pool[1-574817798]",
                "password": "******",
                "username": "postgres",
                "validationInterval": 3000,
                "jmxEnabled": true,
                "initSQL": null,
                "testOnConnect": false,
                "jdbcInterceptors": null,
                "fairQueue": true,
                "useEquals": true,
                "abandonWhenPercentageFull": 0,
                "maxAge": 0,
                "useLock": false,
                "suspectTimeout": 0,
                "dataSource": null,
                "dataSourceJNDI": null,
                "alternateUsernameAllowed": false,
                "commitOnReturn": false,
                "rollbackOnReturn": false,
                "useDisposableConnectionFacade": true,
                "logValidationErrors": false,
                "propagateInterruptState": false,
                "ignoreExceptionOnPreLoad": false,
                "useStatementFacade": true
            },

以及用于查询db的代码：

JdbcTemplate jdbcTemplate = appCtx.getBean(JdbcTemplate.class);

ResultSet columns = jdbcTemplate.getDataSource().getConnection().getMetaData().getColumns(null, null, source.getTable().toLowerCase(), null);

String selectList = "";


while (columns.next())
{

    String colName = columns.getString("COLUMN_NAME");
    String colType = columns.getString("DATA_TYPE");


    if(!selectList.equals("")) {
        selectList += ", ";
    }


    if((""+java.sql.Types.INTEGER).equalsIgnoreCase(colType) || 
            (""+java.sql.Types.DOUBLE).equalsIgnoreCase(colType) ||
            (""+java.sql.Types.BIGINT).equalsIgnoreCase(colType) ||
            (""+java.sql.Types.FLOAT).equalsIgnoreCase(colType) ) {
        selectList += "SUM(" + colName + ")";
    } else {
        selectList += "MAX(" + colName + ")";
    }

    selectList += " AS "+colName;
}

            String sql = "SELECT "+selectList+" FROM "+source.getTable()+" "+
                            "WHERE "+source.getDateColumn()+" >= ? "+
                            "AND "+source.getDateColumn()+" <= ? ";


List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, Date.valueOf(startDate), Date.valueOf(endDate));

Answer 1

Spring boot允许您配置数据源的行为方式。你会在官方文件上找到完整的清单。为您的案例检查以下属性：

spring.datasource.maxActive
spring.datasource.maxIdle

根据您使用的连接池，您还可以使用Spring Boot属性对其进行调优（所有内容都在文档中）。