Connections not Being Released

[KeithHenn]

Hello,

I'm using C3p0 for connection pooling and using springs jdbc template to handle interaction with the database. Here is how my datasource is configured:

<bean id="dataSource" destroy-method="close" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	   		<property name="driverClass" value="${jdbc.driverClassName}"></property>
			<property name="jdbcUrl" value="${jdbc.url}"></property>
			<property name="user" value="${jdbc.username}"></property>
			<property name="password" value="${jdbc.password}"></property>
			<property name="initialPoolSize" value="3"></property>
			<property name="minPoolSize" value="5"></property>
			<property name="maxPoolSize" value="50"/>
			<property name="maxIdleTime" value="60"/>
			<property name="checkoutTimeout" value="100"/>
			<property name="maxStatements" value="50"></property>
			<property name="automaticTestTable" value="C3P0_TEST_TABLE"></property>
			<property name="testConnectionOnCheckin" value="true"></property>
			<property name="idleConnectionTestPeriod" value="60"></property>
    	</bean>

  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       	<property name="dataSource"><ref bean="dataSource"/></property>
       </bean>
 <bean id="userDAO" class="com.Test.dao.UserDAOImpl">
       		<property name="jdbcTemplate"><ref bean="jdbcTemplate"/></property>
       </bean>

Is there something else I need to do in order to properly close the connections? I'm eventually using up all of the connections in the database and throwing exceptions.

Any help would be greatly appreciated. Tahnk you.

[KeithHenn]

I also have transaction management configured as follows:

      <tx:annotation-driven transaction-manager="txManager"/>
       <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
       		<property name="dataSource" ref="dataSource"/>
       </bean>

Here is a method accessing the database

	@Transactional(readOnly=false)
	public void updateBenchMarkCumulative(List<BenchMarkCumulative> bmCumulativeList)
	{
		List<Object[]> parameters = new ArrayList<Object[]>();
		for(BenchMarkCumulative bmCumulative : bmCumulativeList)
		{
			parameters.add(new Object[]{bmCumulative.getCumulativeAmt(), bmCumulative.getPkBenchMarkCumulative()});
		}
		this.simpleJdbcTemplate.batchUpdate(UPDATE_BENCHMARK_CUMULATIVE, parameters);
	}

I'm really in a bind any advice would be greatly appreciated.

Thank you.
Keith

[jackura]

Why don't u try to use Hibernate with Spring??

             <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
		<property name="sessionFactory" ref="sessionFactory" />
		<property name="cacheQueries" value="true" />
	</bean>

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="annotatedClasses">
			<list>
				<!-- value>com.models.beans.POJO</value -->

			</list>
		</property>
		<property name="configurationClass">
			<value>org.hibernate.cfg.AnnotationConfiguration</value>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">
					org.hibernate.dialect.MySQLDialect
				</prop>
				<prop key="hibernate.show_sql">false</prop>
				<prop key="hibernate.jdbc.batch_size">100</prop>
				<prop key="hibernate.cache.use_second_level_cache">false</prop>
				<prop key="hibernate.current_session_context_class">thread</prop>
				<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory
				</prop>
				<prop key="hibernate.query.substitutions">true 'Y', false 'N'</prop>
				<prop key="hibernate.bytecode.use_reflection_optimizer">false</prop>
				<prop key="hibernate.use_outer_join">false</prop>
				<prop key="hibernate.c3p0.minPoolSize">5</prop>
				<prop key="hibernate.c3p0.maxPoolSize">20</prop>
				<prop key="hibernate.c3p0.timeout">600</prop>
				<prop key="hibernate.c3p0.max_statement">50</prop>
				<prop key="hibernate.connection.zeroDateTimeBehavior">convertToNull</prop> 
			</props>
		</property>
	</bean>

	<!-- data source -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://${database.url}/${database.name}?zeroDateTimeBehavior=convertToNull" />
		<property name="username" value="${username}" />
		<property name="password" value="${database.pwd}" />
	</bean>

	<!-- DAO Hibernate OPTIONAL to have a HIGHEST HIRERCHY CLASS to manage the general queries.-->
	<bean id="baseHibernateDao" class="com.daos.BaseHibernateDao">
		<property name="sessionFactory" ref="sessionFactory" />
		<property name="hibernateTemplate" ref="hibernateTemplate"/>
	</bean>

[KeithHenn]

Thanks for the suggestion but I'd prefer to not go the route of implementing Hibernate in this project.

Does anyone else have any suggestions as to what is causing me to run out of connections? Thank you.