Troubleshoot a connection exception in the c3p0 connection pool

Recently I wrote a database collection program. The process is to periodically collect data from the SQLSERVER database to the Oracle database. Data is generated once an hour, and each time the data volume is about 2 W. Sping3 + hibernate4 is used in the environment, and C3p0 is used in the database connection pool.

In strange cases, "c3p0 connection is already closed" is reported at intervals"

The configuration of the database connection pool I started is as follows: the oracle database starts transactions, but the collected SQL Server database does not start transactions.

jdbc.driverClass=oracle.jdbc.OracleDriverjdbc.jdbcUrl=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.18.240)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))jdbc.user=appdsjdbc.password=appdsc3p0.acquireIncrement=5c3p0.maxIdleTime=60c3p0.maxPoolSize=80c3p0.minPoolSize=10c3p0.initialPoolSize=10c3p0.maxStatements=0c3p0.idleConnectionTestPeriod=60c3p0.acquireRetryAttempts=30c3p0.acquireRetryDelay=1000c3p0.breakAfterAcquireFailure=falsec3p0.testConnectionOnCheckout=falsejdbcdata.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriverjdbcdata.jdbcUrl=jdbc:sqlserver://10.12.18.241:1433;databaseName=data;jdbcdata.user=cawasdatauserjdbcdata.password=cawas606c3p0ObsDataDB.maxPoolSize=30

I found the problem. I first added the debugging information configuration on c3p0:

C3p0. debugUnreturnedConnectionStackTraces = true
C3p0. unreturnedConnectionTimeout = 90 (my connection timeout is 60 s, so this setting is 90 s)

Add response configuration for applicationContext data source configuration

<Property name = "breakAfterAcquireFailure" value = "$ {c3p0. breakAfterAcquireFailure}"/>
<Property name = "testconnectioncheckout" value = "$ {c3p0. testConnectionOnCheckout}"/>

It turns out that there are many unrecycled connections. Normally, there will be some connections that time out and are not recycled, but there won't be so many connections.

Add configuration (http://hi.baidu.com/austincao/item/fc9907da3d854e44fa576861) in hibernate sessionFacory)

Spring3.1 removed the HibernateDaoSupport class. Hibernate4 needs to get the session through getCurrentSession. And set
<Prop key = "hibernate. current_session_context_class"> org. springframework. orm. hibernate4.SpringSessionContext </prop>

In Spring @ Transactional declarative transaction management, "currentSession" is defined as the Session currently managed by the Spring transaction manager. In this case, configure:
Hibernate. current_session_context_class = org. springframework. orm. hibernate4.SpringSessionContext.

In addition, when using sessionFactory. getCurrentSession () in hibernate to obtain the session, you must declare the transaction for the method.

The collected Code also includes transactions.