title: HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2a84e649 (This connection has been closed.)
body: <p>I'm using Postgresql and spring boot 2.0.4. The below error is thrown when trying to execute the queries one after the other. I've executed the following query, and the count keeps on increasing. </p>
  
  <pre><code>SELECT COUNT(*) FROM pg_stat_activity WHERE state ILIKE '%idle%';
  </code></pre>
  
  <p>I've used these properties and dependencies for connection pool. Yet, it gives the same error</p>
  
  <pre><code>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
  
  &lt;dependency&gt;
          &lt;groupId&gt;org.apache.commons&lt;/groupId&gt;
          &lt;artifactId&gt;commons-dbcp2&lt;/artifactId&gt;
          &lt;version&gt;2.1.1&lt;/version&gt;
  &lt;/dependency&gt;
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Edit - 1</strong></p>
  
  <pre><code>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(&lt;generated&gt;) ~[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(&lt;generated&gt;) ~[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]
  </code></pre>
  
  <p>EmailStatsDao</p>
  
  <pre><code>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&lt;EmailReportStats&gt; findByclientId(int clientId) {
          return emailReportsRepository.findByclientId(clientId);
      }
  
      @Override
      public &lt;S extends EmailReportStats&gt; S save(S s) {
          return emailReportsRepository.save(s);
  
      }
  
      @Override
      public &lt;S extends EmailReportStats&gt; Iterable&lt;S&gt; saveAll(Iterable&lt;S&gt; itrbl) {
          return emailReportsRepository.saveAll(itrbl);
      }
  
      @Override
      public Optional&lt;EmailReportStats&gt; findById(Integer id) {
          return emailReportsRepository.findById(id);
  
      }
  
      @Override
      public boolean existsById(Integer id) {
          return emailReportsRepository.existsById(id);
  
      }
  
      @Override
      public Iterable&lt;EmailReportStats&gt; findAll() {
          return emailReportsRepository.findAll();
  
      }
  
      @Override
      public Iterable&lt;EmailReportStats&gt; findAllById(Iterable&lt;Integer&gt; 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&lt;? extends EmailReportStats&gt; itrbl) {
          emailReportsRepository.deleteAll();
  
      }
  
      @Override
      public void deleteAll() {
          emailReportsRepository.deleteAll();
  
      }
  
      public List&lt;EmailReportStats&gt; getEmailStatsData(String pSqlQuery) {
          List&lt;EmailReportStats&gt; emailReportStatses = new ArrayList&lt;&gt;();
          List&lt;EmailReportStats&gt; emailReportStats = new ArrayList&lt;&gt;();
          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&lt;EmailReportStats&gt; getEmailStatsTableData(String pSqlQuery, int clientId) {
          List&lt;EmailReportStats&gt; emailReportStatses = new ArrayList&lt;&gt;();
          List&lt;EmailReportStats&gt; emailReportStats = new ArrayList&lt;&gt;();
          List&lt;Object[]&gt; 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&lt;EmailReportStats&gt; getEmailSparkLinesData(String pSqlQuery) {
          List&lt;EmailReportStats&gt; emailReportStats = new ArrayList&lt;&gt;();
          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;
      }
  }
  </code></pre>