最近在测试环境遇到一次jdbc连接池占满的问题。背景如下：
有一个批量操作，分页去查表数据然后进行后续处理，该查询跨表并且需要返回自定义的字段。
spring-data-jpa提供了方便使用的JpaRepository接口，依次继承PagingAndSortingRepository、CrudRepository、Repository。
自定义查询一般步骤如下：
- 定义包含自定义查询的接口
public interface XxxRepositoryCustom {
Page<XxxRespVo> findXxx(XxxReqVo, Pageable pageable);
}
其中`findXxx`为自定义的查询接口
- 定义表对应的接口，同时继承
JpaRepository和XxxRepositoryCustom。
@Repository
public interface XxxRepository extends JpaRepository<Xxx, Long>, JpaSpecificationExecutor<Xxx> {
...
}
注：这里同时继承了`JpaSpecificationExecutor<Xxx>`接口，便于同时能使用`Specification`查询功能。
- 实现
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