C3p0 Connection pool does not close connections

Question

I am using c3p0 0.9.1.2, hibernate 3.2.1.ga and spring 2.5.5. The problem is that the database connection does not close. Here are the logs:

[March 22, 2012 12: 29: 56,091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool ["http-apr-8080" -exec-4] acquires a test pool that has already been exceeded. [managed by: 20; max: 20] [March 22, 2012 12: 29: 56,091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool ["http-apr-8080" -exec-4] awaitAvailable (): com.mchange.v2.c3p0.impl.NewPooledConnection@15cc604 [March 22 2012 12: 29: 56,091] DEBUG com.mchange.v2.resourcepool.BasicResourcePool ["http-apr-8080" -exec-4] trace com.mchange.v2.resourcepool.BasicResourcePool@6b0524 [managed: 20, unused: 0, excluded: 0] (for example, com.mchange.v2.c3p0.impl.NewPooledConnection@15cc604 )

Here is the data source configuration:

<!-- Local DataSource that works in any environment --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${database.driver}"/> <property name="jdbcUrl" value="${database.url}"/> <property name="user" value="${database.user}"/> <property name="password" value="${database.password}"/> <!--<property name="connectionCustomizerClassName" value="org.xxx.webapp.common.persistence.WatchConnectionCustomizer"/>--> <property name="maxStatements" value="500"/> <property name="maxIdleTime" value="1800"/> <property name="maxPoolSize" value="100"/> <property name="minPoolSize" value="2"/> <property name="initialPoolSize" value="2"/> <property name="acquireIncrement" value="3"/> <property name="idleConnectionTestPeriod" value="3000"/> </bean> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" > <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">${hibernate.dialect}</prop> <prop key="hibernate.show_sql">false</prop> <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop> <prop key="hibernate.connection.autocommit">${hibernate.connection.autocommit}</prop> <prop key="hibernate.transaction.auto_close_session">${hibernate.transaction.auto_close_session}</prop> <prop key="hibernate.connection.release_mode">${hibernate.connection.release_mode}</prop> <prop key="hibernate.bytecode.use_reflection_optimizer">${hibernate.bytecode.use_reflection_optimizer}</prop> <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop> <prop key="hibernate.cache.use_query_cache">true</prop> <prop key="hibernate.cache.use_second_level_cache">true</prop> <prop key="hibernate.cache.region.factory_class">net.sf.ehcache.hibernate.EhCacheRegionFactory</prop> </props> </property> <property name="annotatedClasses"> <list> ... </list> </property> <property name="dataSource"> <ref bean="dataSource" /> </property> </bean> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <property name="sessionFactory"> <ref bean="sessionFactory" /> </property> </bean> 

Here is our common dao

 public class GenericDAO<T, PK extends Serializable> extends HibernateDaoSupport implements IGenericDAO<T, PK> { private Class<T> clazz; private Logger logger = Logger.getLogger(GenericDAO.class); private static Session session; public GenericDAO(Class<T> clazz) { this.clazz = clazz; } public void refresh(T instanceToRefresh) throws DataAccessException { getHibernateTemplate().refresh(instanceToRefresh); //getCurrentSession().refresh(instanceToRefresh); } public void saveOrUpdate(T instanceToSaveOrUpdate) throws DataAccessException { //getCurrentSession().saveOrUpdate(instanceToSaveOrUpdate); getHibernateTemplate().saveOrUpdate(instanceToSaveOrUpdate); } public void persist(T instanceToPersist) throws DataAccessException { getHibernateTemplate().persist(instanceToPersist); //getCurrentSession().persist(instanceToPersist); } @SuppressWarnings("unchecked") public T merge(T instanceToMerge) throws DataAccessException { T instanceMerged = (T) getHibernateTemplate().merge(instanceToMerge); //T instanceMerged = (T) getCurrentSession().merge(instanceToMerge); return instanceMerged; } @SuppressWarnings("unchecked") public PK save(T newInstance) throws DataAccessException { return (PK) getHibernateTemplate().save(newInstance); //return (PK) getCurrentSession().save(newInstance); } public void delete(T persistentObject) throws DataAccessException { getHibernateTemplate().delete(persistentObject); //getCurrentSession().delete(persistentObject); } @SuppressWarnings("unchecked") public T load(PK id) { return (T) getHibernateTemplate().get(clazz, id); //return (T) getCurrentSession().get(clazz, id); } public void update(T transientObject) throws DataAccessException { //getCurrentSession().update(transientObject); getHibernateTemplate().update(transientObject); } @SuppressWarnings("unchecked") public List<T> loadAll() throws DataAccessException { //Session session = this.getCurrentSession(); //return session.createQuery("from " + clazz.getName()).list(); return getHibernateTemplate().loadAll(clazz); } } 

Thanks in advance.

Answer 1

Normally, the connection is automatically closed by sleep mode. However, a few notes:

• Long-term transactions may take up a connection
• Incorrect session management may mean that you are not closing the session, which in turn means that the connection remains in use.

A typical setup when using spring is to annotate service methods using @Transactional . This way spring will manage your transactions and sessions.

Answer 2

We used the following line in the dispatcher-servlet.xml , and the physical database connection is now closed.

 <tx:annotation-driven proxy-target-class="true" transaction-manager="transactionManager"/>