Hi all, I'm having trouble with hibernate not able to open a connection. I have a DAO:

public class MyDao extends HibernateDaoSupport
{
DataSource dataSource;

public void setDataSource(DataSource dataSource)
{
this.dataSource = dataSource;
}

public MyPOJO findByQuery(int hour)
{
Query query = this.getSession().createSQLQuery(
"SELECT * FROM MyPOJO WHERE someDate >= DATE_SUB(now(), INTERVAL ? HOUR)")
.addEntity(MyPOJO.class);

List<MyPOJO> results = query.setInteger(0, hours).list();

return results;
}
}

and then in a test case call findByQuery(1) 8 times, it works, but if I call a 9th time it fails with:



org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
Caused by: org.apache.commons.dbcp.SQLNestedException: Could not retrieve connection info from pool
at org.apache.commons.dbcp.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(SharedPoolDataSource.java:169)
at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:631)
at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:615)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
... 35 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:827)
at org.apache.commons.dbcp.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(SharedPoolDataSource.java:165)
... 39 more



This is what my hibernate properties look like:

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.MySQL5Dialect
</prop>
<prop key="hibernate.current_session_context_class">
thread
</prop>
<prop key="hibernate.format_sql">false</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.use_sql_comments">false</prop>
<prop key="hibernate.jdbc.use_get_generated_keys">true</prop>
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</prop>
<prop key="hibernate.connection.release_mode">auto</prop>
</props>
</property>

If I change the release_mode to 'after_statement' (ala http://docs.jboss.org/hibernate/stable/ ... on-release) it will work, but I don't understand that and feel like that is just a band-aid for something bigger that I am doing wrong.

I've also tried to flush and close the this.getSession() with no luck either. I can see the close() gets called AFTER all of the calls to findByQuery(1) have completed.

This is on Hibernate 3.2.6, Spring 3.0 and MySQL 5.1. Let me know what more information I can provide.