How to determine connection pool size programmatically?

Question

Is there any way to determine database connection pool size (connection in used/connection remaining in connection pool) programmatically? We am using Hibernate with C3P0.

We are facing issues while connecting to db. Following exception is thrown and the data is not saved in db.

1005,MA,19/09/11 09:39:14,com.novosys.gtw.business.frontend.SnapshotMessageBusiness.save, Major: Cannot open connection
org.hibernate.exception.GenericJDBCException: Cannot open connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)
    at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:342)
    at $Proxy0.beginTransaction(Unknown Source)
    at com.novosys.gtw.util.base.BaseBusiness.save(BaseBusiness.java:199)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.SaveMessageFilter.decode(SaveMessageFilter.java:102)
    at org.apache.mina.filter.codec.demux.DemuxingProtocolCodecFactory$ProtocolDecoderImpl.doDecode(DemuxingProtocolCodecFactory.java:292)
    at org.apache.mina.filter.codec.CumulativeProtocolDecoder.decode(CumulativeProtocolDecoder.java:133)
    at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:158)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.WhitelistFilter.messageReceived(WhitelistFilter.java:231)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at com.novosys.gtw.business.backend.receivesnapshotmessage.filter.MoniterFilter.messageReceived(MoniterFilter.java:92)
    at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
    at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
    at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
    at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:220)
    at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:264)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
    at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)
    at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:78)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    ... 31 more
Caused by: 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)
    ... 34 more

We tried to resolve it by increasing connection pool size and also increasing no. of connections available at MySQL level, but of no use. We are now trying to sort of debug it to see if its due to connection pool size or due to MySQL connection size. We want to log no. of connection available/in use in connection pool size but could not get any help from google.

Environment: Java, Hibernate, C3P0, MySQL

Session session = null;
Transaction transaction = null;

try {
            session = HibernateUtil.getSessionFactory(datasource).getCurrentSession();
            transaction = session.beginTransaction();
            // db save called here
            session.getTransaction().commit();
        } catch (Exception e) {
            Logger.write(LoggerConstant.MAJOR_ERROR, e.getMessage(), e, methodName);
        } finally {
            try {
                if ((transaction != null) && (transaction.isActive())) {
                    transaction.rollback();
                }
            } catch (Exception e) {
                Logger.write(LoggerConstant.CRITICAL_ERROR, e.getMessage(), e, methodName);
            }
            try {
                if ((session != null) && (session.isOpen())) {
                    session.close();
                }
            } catch (Exception e) {
                Logger.write(LoggerConstant.CRITICAL_ERROR, e.getMessage(), e, methodName);
            }
        }

possible duplicate of Can I access the c3P0 connection pool properties programmatically? — user180100

ex0du5 ex0du5 · Accepted Answer · 2011-09-21T06:05:37

I don't believe your problem is the connection pool, per se, but more generally a connection leak. This problem is commonly related to the use of HibernateDaoSupport.getSession() without properly pairing with HibernateDaoSupport.releaseSession(). In general, you want something like

public SomeObject getSomething() 
{ 
    Session session = null;
    try 
    {
        session = this.getSession();
        Query query = session.createSQLQuery("SELECT * FROM SomeTable WHERE SomeClause").addEntity(SomeObject.class); 

        // extract object from query 

        return someObject; 
    } 
    finally 
    {
        if (session != null) 
            this.releaseSession(session);
    }        
}

This can be automated by using a HibernateCallback. You do this by providing the query to this.getHibernateTemplate().executeFind which will use a session in Hibernate with automated resource management.

How to determine connection pool size programmatically?

3 Answers