postgresql - HikariPool-1 - 无法验证连接 org.postgresql.jdbc.PgConnection@2a84e649(此连接已关闭。)

Question

我正在使用 Postgresql 和 spring boot 2.0.4。尝试一个接一个地执行查询时会引发以下错误。我执行了以下查询，并且计数不断增加。

SELECT COUNT(*) FROM pg_stat_activity WHERE state ILIKE '%idle%';

我已经将这些属性和依赖项用于连接池。然而，它给出了同样的错误

spring.datasource.dbcp2.initial-size=10
spring.datasource.dbcp2.max-total=25
spring.datasource.dbcp2.pool-prepared-statements=true
spring.datasource.hikari.max-lifetime=600000

<dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.1.1</version>
</dependency>

错误:

2018-09-27 16:43:25.481  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2a84e649 (This connection has been closed.)
    2018-09-27 16:43:40.490  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@9faf6ae (This connection has been closed.)
    2018-09-27 16:44:00.934  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@24723851 (This connection has been closed.)
    2018-09-27 16:44:00.935  WARN 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08003
    2018-09-27 16:44:00.936 ERROR 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not available, request timed out after 49443ms.
    2018-09-27 16:44:00.936  WARN 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08003
    2018-09-27 16:44:00.936 ERROR 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : This connection has been closed.
    2018-09-27 16:44:00.938 ERROR 9085 --- [nio-8081-exec-4] com.netelixir.dao.EmailStatsDao          : Exception occured due to javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

编辑 - 1

WARN 11920 --- [l-1 housekeeper] com.zaxxer.hikari.pool.ProxyLeakTask     : Connection leak detection triggered for org.postgresql.jdbc.PgConnection@603cfde6 on thread http-nio-8081-exec-6, stack trace follows

java.lang.Exception: Apparent connection leak detected
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.9.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:145) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:171) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:938) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2692) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2675) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1490) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at com.netelixir.dao.EmailStatsDao.getEmailStatsTableData(EmailStatsDao.java:146) ~[classes/:na]
    at com.netelixir.dao.EmailStatsDao$$FastClassBySpringCGLIB$$c924bff4.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at com.netelixir.dao.EmailStatsDao$$EnhancerBySpringCGLIB$$5e0a7d71.getEmailStatsTableData(<generated>) ~[classes/:na]
    at com.netelixir.service.AdminDashBoardService.getLastSevenDaysData(AdminDashBoardService.java:43) ~[classes/:na]
    at com.netelixir.controller.AdminDashBoardController.getChartData(AdminDashBoardController.java:44) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_181]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_181]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:866) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:147) ~[spring-session-core-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:81) ~[spring-session-core-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at com.netelixir.config.RequestFilter.doFilter(RequestFilter.java:33) ~[classes/:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:800) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1471) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_181]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_181]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181]

EmailStatsDao

import com.nidhi.model.EmailReportStats;
import com.nidhi.repository.EmailReportsRepository;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author nidhi
 */
@Repository
@Transactional
@Component
public class EmailStatsDao implements EmailReportsRepository {

    private static final Logger LOG = LoggerFactory.getLogger(EmailStatsDao.class);

    @Autowired
    EmailReportsRepository emailReportsRepository;

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    @PersistenceContext
    public EntityManager em;

    @Override
    public List<EmailReportStats> findByclientId(int clientId) {
        return emailReportsRepository.findByclientId(clientId);
    }

    @Override
    public <S extends EmailReportStats> S save(S s) {
        return emailReportsRepository.save(s);

    }

    @Override
    public <S extends EmailReportStats> Iterable<S> saveAll(Iterable<S> itrbl) {
        return emailReportsRepository.saveAll(itrbl);
    }

    @Override
    public Optional<EmailReportStats> findById(Integer id) {
        return emailReportsRepository.findById(id);

    }

    @Override
    public boolean existsById(Integer id) {
        return emailReportsRepository.existsById(id);

    }

    @Override
    public Iterable<EmailReportStats> findAll() {
        return emailReportsRepository.findAll();

    }

    @Override
    public Iterable<EmailReportStats> findAllById(Iterable<Integer> itrbl) {
        return emailReportsRepository.findAllById(itrbl);
    }

    @Override
    public long count() {
        return emailReportsRepository.count();
    }

    @Override
    public void deleteById(Integer id) {

        emailReportsRepository.deleteById(id);
    }

    @Override
    public void delete(EmailReportStats t) {
        emailReportsRepository.delete(t);

    }

    @Override
    public void deleteAll(Iterable<? extends EmailReportStats> itrbl) {
        emailReportsRepository.deleteAll();

    }

    @Override
    public void deleteAll() {
        emailReportsRepository.deleteAll();

    }

    public List<EmailReportStats> getEmailStatsData(String pSqlQuery) {
        List<EmailReportStats> emailReportStatses = new ArrayList<>();
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
            emailReportStatses = session.createNativeQuery(pSqlQuery).addEntity(EmailReportStats.class).list();
            if(!emailReportStatses.isEmpty()){
//                DateTimeFormatter formatter=DateTimeFormatter.ofPattern("dd MMM");
                for (EmailReportStats emailReport : emailReportStatses) {
                    emailReport.setDate(LocalDate.parse(emailReport.getDate()).format(DateTimeFormatter.ofPattern("dd MMM")));
                    emailReportStats.add(emailReport);
                }
            }
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }

    public List<EmailReportStats> getEmailStatsTableData(String pSqlQuery, int clientId) {
        List<EmailReportStats> emailReportStatses = new ArrayList<>();
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        List<Object[]> data=new ArrayList();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
           data=entityManagerFactory.createEntityManager().createQuery(pSqlQuery).getResultList();

//           data =  q.list();
            for(Object[] list: data){
                EmailReportStats reportStats=new EmailReportStats();
                 DecimalFormat decimalFormat = new DecimalFormat("#.##");
                reportStats.setRevenue(Float.valueOf(decimalFormat.format((Double)list[0])));
                reportStats.setOrders(Float.valueOf(decimalFormat.format((Double)list[1])));
                reportStats.setimpressions(((Long)list[2]).intValue());
                reportStats.setAccountId((Integer)list[3]);
                reportStats.setSearchEngineId((Integer)list[4]);
                reportStats.setDateType((String)list[5]);
            emailReportStats.add(reportStats);

            }
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }

    public List<EmailReportStats> getEmailSparkLinesData(String pSqlQuery) {
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
            emailReportStats = session.createNativeQuery(pSqlQuery).addEntity(EmailReportStats.class).list();
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }
}

Answer 1

在 Spring Boot 2.x 中，Hikari 是默认连接池，所以如果你有 JPA 那么你不需要添加 Hikari依赖于 pom但如果你想使用 dbcp2那么你需要排除 Hikari并添加 dbcp2依赖性。

所以在你的情况下，你混合了两者的设置。

如果只是想和Hikari呆在一起，那么删除dbcp2依赖项并更改您的数据源属性以使用 spring.datasource.hikari .

添加以下属性以验证连接:

spring.datasource.testWhileIdle=true
spring.datasource.test-on-borrow=true

要进一步检查是否有任何泄漏，请添加以下属性并检查您的池连接情况以及是否有任何泄漏。

spring.datasource.hikari.leakDetectionThreshold=2000
logging.level.com.zaxxer.hikari=DEBUG