Hibernate cannot open connection

Question

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/core/reference/en/html_single/#transactions-connection-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.

isn't there a Caused by of the exception?

Bozho
– Bozho

2010-08-12 15:43:31 +00:00
Commented Aug 12, 2010 at 15:43 — Bozho
– Bozho, Commented Aug 12, 2010 at 15:43

axtavt · Accepted Answer · 2010-08-12 16:00:53Z

12

Javadoc for HibernateDaoSupport.getSession() says:

Note that this is not meant to be invoked from HibernateTemplate code but rather just in plain Hibernate code. Either rely on a thread-bound Session or use it in combination with releaseSession(org.hibernate.Session).

So, the session obtained via getSession() should be released via releaseSession():

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

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

        return results; 
    } finally {
        if (s != null) this.releaseSession(s);
    }        
}

But the better way to deal with session is to use a HibernateCallback:

public MyPOJO findByQuery(int hour) 
{ 
    return this.getHibernateTemplate().executeFind(new HibernateCallback<List<MyPOJO>>() {
        List<MyPOJO> doInHibernate(org.hibernate.Session session) {
            Query query = session.createSQLQuery(    
                "SELECT * FROM MyPOJO WHERE someDate >= DATE_SUB(now(), INTERVAL ? HOUR)")    
                    .addEntity(MyPOJO.class);    
            return query.setInteger(0, hours).list();    
        }
    });
}

edited Aug 12, 2010 at 16:00

answered Aug 12, 2010 at 15:50

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DanInDC Over a year ago

Ahh! I looked for ever for a way to do it via getHibernateTemplate(). Thanks a lot axtavt.

Rihards Over a year ago

Thank you a lot! I would never have figured out why my application fails each 10 minutes when using getSession()..

LuckyLikey · Accepted Answer · 2018-12-03 09:08:52Z

1

I solved the same problem by updating the mysql-connector-java-5.1.23-bin.jar file.

edited Dec 3, 2018 at 9:08

LuckyLikey

3,9312 gold badges37 silver badges59 bronze badges

answered Dec 3, 2018 at 6:57

vishnu.pp

115 bronze badges

Collectives™ on Stack Overflow

Hibernate cannot open connection

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related