java - java.sql.SQLException：连接 org.postgresql.jdbc.PgConnection 已关闭

Question

环境：

Java 8
WildFly 8 (WF 8)
PostgreSQL 11
Spring 4.3
Hibernate 5

DBCP 2.5 版
PostgreSQL JDBC 驱动程序：42.2.5

在我们拥有的这个 Java 应用程序中，我们通过 Quartz 运行一个作业。假设每 N 小时调用一个方法，该方法会产生 30-40 个线程，这些线程开始做一些工作。这项工作涉及访问 PostgreSQL 数据库。

当 WildFly 连续 4-5 天没有重新启动，并且部署在 WildFly 中的应用程序也没有重新启动......然后一段时间后我们开始收到此错误。

可能是什么原因？似乎 conn 池正在为应用程序提供一个已经关闭的 conn。但是我们testOnBorrow=true的数据源配置中确实有。那么这怎么可能呢？

任何想法我们可以如何摆脱这个错误并以最好的方式做到这一点？对我来说，这似乎是 DBCP 或 JDBC 驱动程序中的一个错误，但我不确定。

    27 May 2019 18:12:37 ERROR [taskExecutor-13] synchronizer.SynchronizerPostProcessUtil:162 - Batch Process Call Errored - DB is not updated
    org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [ select count(1) from kwt.Error_BatchUpdate(?,?,?) ]; SQL state [null]; error code [0]; could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:248)
        at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:368)
        at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:315)
        at com.company123.db.dao.common.HibernateDao.callStoredProcedureWithParameters(HibernateDao.java:947)
        at com.company123.db.dao.common.HibernateDao.callOpenXmlBatchProcessingWithParameters(HibernateDao.java:927)
        at com.company123.synchronizer.SynchronizerPostProcessUtil.executeBatchItems(SynchronizerPostProcessUtil.java:156)
        at com.company123.synchronizer.SynchronizerPostProcessUtil.processPostProcessItems(SynchronizerPostProcessUtil.java:76)
        at com.company123.synchronizer.AbstractSubAccountSynchronizer.createSubAccount(AbstractSubAccountSynchronizer.java:51)
        at com.company123.synchronizer.AbstractSubAccountSynchronizer.createSubAccount(AbstractSubAccountSynchronizer.java:23)
        at com.company123.synchronizer.SESynchronizer.syncSubAccount(SESynchronizer.java:990)
        at com.company123.synchronizer.SESynchronizer.syncLocalToSE(SESynchronizer.java:332)
        at com.company123.lifecycle.mb.LifeCycleRunnable.runMBLifeCycle(LifeCycleRunnable.java:188)
        at com.company123.lifecycle.mb.LifeCycleRunnable.run(LifeCycleRunnable.java:113)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
    Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
        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.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:181)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
        at org.hibernate.loader.Loader.doQuery(Loader.java:938)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
        at org.hibernate.loader.Loader.doList(Loader.java:2692)
        at org.hibernate.loader.Loader.doList(Loader.java:2675)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
        at org.hibernate.loader.Loader.list(Loader.java:2502)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
        at org.hibernate.query.internal.AbstractProducedQuery.uniqueResult(AbstractProducedQuery.java:1457)
        at com.company123.db.dao.common.HibernateDao$19.doInHibernate(HibernateDao.java:971)
        at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:361)
        ... 14 more
    Caused by: java.sql.SQLException: Connection org.postgresql.jdbc.PgConnection@25d46a62 is closed.
        at org.apache.commons.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:594)
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:289)
        at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:292)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:145)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:171)
        ... 32 more

标签： javapostgresqlwildfly-8postgresql-11