spring-data-jpa自定义查询导致jdbc连接池占满

最近在测试环境遇到一次jdbc连接池占满的问题。背景如下：
有一个批量操作，分页去查表数据然后进行后续处理，该查询跨表并且需要返回自定义的字段。
spring-data-jpa提供了方便使用的JpaRepository接口，依次继承PagingAndSortingRepository、CrudRepository、Repository。
自定义查询一般步骤如下：

1. 定义包含自定义查询的接口

public interface XxxRepositoryCustom {
    Page<XxxRespVo> findXxx(XxxReqVo, Pageable pageable);
}
其中`findXxx`为自定义的查询接口

2. 定义表对应的接口，同时继承JpaRepository和XxxRepositoryCustom。

@Repository
public interface XxxRepository extends JpaRepository<Xxx, Long>, XxxRepositoryCustom, JpaSpecificationExecutor<Xxx> {
    ...
}

注：这里同时继承了`JpaSpecificationExecutor<Xxx>`接口，便于同时能使用`Specification`查询功能。

3. 实现XxxDao接口，实现自定义的查询方法

@Repository
public class XxxRepositoryImpl implements XxxRepositoryCustom {

    @PersistenceContext
    private EntityManager em;
    
    @Override
    public Page<XxxRespVo> findSimpleDepotProductList(XxxReqVo reqVo, Pageable pageable) {
         ...
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("select xx.aa,yy.bb,zz.cc")
                        .append(" from xx join yy join zz ...")
                        .append(" where param1=:param1")
                        .append(" where param2=:param2")
                        .append(" where param3=:param3")
                        .append("...");

        Query selectQuery = em.createNativeQuery(selectSql.toString());
        selectQuery.setParameter("xx", xx);

        List<XxxRespVo> list = selectQuery.unwrap(SQLQuery.class)
                .addScalar("param1", StandardBasicTypes.STRING)
                .addScalar("param2", StandardBasicTypes.STRING)
                .addScalar("param3", StandardBasicTypes.LONG)
                .setResultTransformer(Transformers.aliasToBean(XxxRespVo.class))
                .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
                .setMaxResults(pageable.getPageSize())
                .list();
        return new PageImpl<>(list, pageable, total);
    }
}

用单元测试调该查询方法成功，在测试环境跑，结果一会儿前端界面调该服务的其它都变慢了，查看日志发现了大量异常：

|ERROR|2020-10-27 16:30:21.811|DubboServerHandler-192.168.x.x:20001-thread-461|c.a.d.r.f.ExceptionFilter:85
      [DUBBO] Got unchecked and undeclared exception which called by 192.168.x.x. service: com.xxx.XxxService, method: findXxx, exception: org.springframework.orm.jpa.JpaSystemException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection, dubbo version: 2.6.2, current host: 192.168.x.x

org.springframework.orm.jpa.JpaSystemException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:314)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
       ...

Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
       ...

Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 50, maxActive 50
        at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1139)
        at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:960)
        at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:940)
        at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:930)
        at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:102)
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:84)
        ... 85 common frames omitted  

注意到Unable to acquire JDBC Connection和GetConnectionTimeoutException，获取数据库连接池超时了。
测试环境的jdbc连接池配置如下：

<jdbc.initialSize>2</jdbc.initialSize>
<jdbc.minIdle>2</jdbc.minIdle>
<jdbc.maxActive>50</jdbc.maxActive>
<jdbc.maxWait>60000</jdbc.maxWait>

最大活跃链接数50，等待时间60秒，跟日志中的异常信息对应。

推测可能是连接没有关闭，改小maxActive值，用单元测试循环去调，很快复现出了测试环境的报错异常。

检查发现，这里使用了自定义查询，但缺少事务的注解，连接没有自动关闭。

解决方法：加上spring的事务注解，因为是查询，设置为只读能一定程度提高性能。

@Transactional(readOnly = true)
@Override
public Page<XxxRespVo> findSimpleDepotProductList(XxxReqVo reqVo, Pageable pageable) {
...
}   

---

参考：
spring-data-jpa官方文档：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations