I use spring 3.0 (jdbcTemplate), Tomcat, MySQL, and C3p0 to handle my database operations. I use both jdbctemplate and simplejdbctemplate, which will take care of creating and closing connections, statements, result sets, etc. I use C3p0 for the connection pool, however the connections remain open, and eventually the connection ends in the application.
Here is the configuration of my data source:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" > <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="5"></property> <property name="maxPoolSize" value="100"/> <property name="minPoolSize" value="5"/> <property name="maxIdleTime" value="30"/> <property name="maxIdleTimeExcessConnections" value="30"/> <property name="maxConnectionAge" value="30"/> <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="30"></property> </bean>
I also use the TransactionManagement provided by spring - here is the configuration for this:
<tx:annotation-driven transaction-manager="txManager"/> <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean>
The rest of the data source configuration is as follows:
<bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate"> <constructor-arg><ref bean="dataSource"/></constructor-arg> </bean> <bean id="userDAO" class="com.Test.dao.UserDAOImpl"> <property name="jdbcTemplate"><ref bean="jdbcTemplate"/></property> </bean>
Finally, this is the method in which I update the records in 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); }
Is there something I'm doing wrong in my configuration, or am I missing something that needs to be added to the configuration or coding?
Here is the exception:
INFO [http-8080-1] (AbstractPoolBackedDataSource.java510) - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ java.beans.IntrospectionException: java.lang.reflect.InvocationTargetException [numThreadsAwaitingCheckoutDefaultUser] ] org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
Thanks in advance. Whale