ResourcePool could not get the resource from its main factory or source

Question

I am trying to connect to a database in Java using jdbcTemplate and I am getting the error below. I have Googled for a long time, and all the solutions that I found did not solve my problem. I tried several different databases (both SQLServer and MySQL) and no one worked.

SEVERE: Servlet.service() for servlet [Faces Servlet] in context with path [/promotion-handler-admin] threw exception [Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!] with root cause com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source. at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319) at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622) ... 

This is my properties file:

 app.driverClassName=net.sourceforge.jtds.jdbc.Driver app.url=jdbc:sqlserver://myUrl:port;databaseName=my_database app.username=myUsername app.password=myPassword 

WebApp / WEB-INF / ApplicationContext-database.xml:

 <beans:bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <beans:property name="driverClass" value="${app.driverClassName}" /> <beans:property name="jdbcUrl" value="${app.url}" /> <beans:property name="user" value="${app.username}" /> <beans:property name="password" value="${app.password}" /> <beans:property name="acquireIncrement" value="5" /> <beans:property name="idleConnectionTestPeriod" value="600" /> <beans:property name="maxPoolSize" value="10" /> <beans:property name="maxStatements" value="5" /> <beans:property name="minPoolSize" value="3" /> <beans:property name="preferredTestQuery" value="select 1 from DUAL" /> </beans:bean> <!-- TRANSACTION_MANAGERS --> <!-- See http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/transaction.html --> <!-- Default --> <beans:bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <beans:property name="dataSource" ref="dataSource" /> </beans:bean> 

DAO Class:

 @Repository public class CampaignDAO { private JdbcTemplate jdbcTemplate; @Resource(name = "dataSource") public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public List<Campaign> getCampaignList() { Long start = System.currentTimeMillis(); List<Campaign> queryList; try { queryList = jdbcTemplate.query("SELECT * FROM campaign", new RowMapper<Campaign>() { public Campaign mapRow(ResultSet rs, int line) throws SQLException { Campaign campaign = new Campaign(); campaign.setId(rs.getLong("id")); campaign.setExtraInfo(rs.getString("extra_info")); campaign.setBeginTime(rs.getDate("begin_time")); campaign.setEndTime(rs.getDate("end_time")); return campaign; } }); } finally { ... } return queryList; } 

Answer 1

For those who find this question in the future. What I was doing wrong was that I used the jtds driver and I forgot to add this to the url. Therefore, in the properties file, I had to do the following:

 app.url=jdbc:jtds:sqlserver://myUrl:port;databaseName=my_database 

Answer 2

For those who find this question in the future.

It may also be caused by a missing database driver.

In my case, I used maven-shade-plugin with minimizeJar parameter. This, of course, throws the jtds driver jtds because it is not mentioned anywhere.

This can be fixed as follows:

  <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-shade-plugin</artifactId> <version>1.6</version> <executions> <execution> <phase>package</phase> <goals> <goal>shade</goal> </goals> <configuration> <minimizeJar>true</minimizeJar> <filters> <filter> <!-- Make sure jtds is included. --> <artifact>net.sourceforge.jtds:jtds</artifact> <includes> <include>**</include> </includes> </filter> <filter> <artifact>*:*</artifact> <excludes> <exclude>META-INF/*.SF</exclude> <exclude>META-INF/*.DSA</exclude> <exclude>META-INF/*.RSA</exclude> <exclude>META-INF/*.sf</exclude> <exclude>META-INF/*.dsa</exclude> <exclude>META-INF/*.rsa</exclude> </excludes> </filter> </filters> </configuration> </execution> </executions> </plugin> 

Answer 3

This message may also be displayed if, like me, you launch the application using the Maven plugin for Tomcat:

 mvn clean install tomcat7:run 

and you have the provided scope element in your Maven dependency:

 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.36</version> <scope>provided</scope> </dependency> 

The provided scope will not allow the connector to be part of the war archive, and the Tomcat plugin will not find a connector to establish a connection to the database.

Just removing the provided scope from the dependencies solves the problem.

Answer 4

I got this problem on c3p0 0.9.5-pre6 with mchange-commons-java 0.2.6.3. After lowering to c3p0 0.9.5-pre5 and mchange-commons-java 0.2.6.2 the problem disappears.