Hibernate with c3p0 and Tomcat: too many connections

Question

I have a hibernate persistence layer with c3p0 and it runs on Tomcat. I have an issue with too many connections. After just a few users get on the site, it starts to mushroom the connections to the db. I have tried changing the maxConnectionAge to 0, I have changed the max_pool_size to 0. I have messed with a lot of the c3p0 settings per suggestions from many sites with people having the same issue. However, nothing seems to help, eventually the db gets too many connections and the app ceases to function. I'm hoping someone may have some other insights that may help. Below is some relavant code.

Hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
            "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
            "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.password">ibisalibi1</property>
    <property name="hibernate.connection.url">jdbc:mysql://ikidhub-db-1.cvo0kkopzfhs.us-west-1.rds.amazonaws.com/homemoviezoo</property>
    <property name="hibernate.connection.username">rjdamore</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="log4jdbc.drivers">jdbc:log4jdbc:mysql://ikidhub-db-1.cvo0kkopzfhs.us-west-1.rds.amazonaws.com/homemoviezoo</property>
    <!-- configuration pool via c3p0-->
            <property name="c3p0.acquire_increment">1</property>
            <property name="c3p0.idle_test_period">800</property> <!-- seconds -->
            <property name="c3p0.max_size">1</property>
            <property name="c3p0.max_statements">20</property>
            <property name="c3p0.min_size">1</property>
            <property name="c3p0.timeout">1800</property> <!-- seconds -->
            <property name="c3p0.maxConnectionAge">0</property>
            <property name="connection.provider_class">
                               org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>


    <property   name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="current_session_context_class">thread</property>
    <property name="hibernate.show_sql">true</property>
    <!--  <property name="hbm2ddl.auto">update</property>-->
    <mapping class="com.local.shared.School"/>
    <mapping class="com.local.shared.SchoolVideo"/>
    <mapping class="com.local.shared.Category"/>
    <mapping class="com.local.shared.Administrator"/>
</session-factory>

</hibernate-configuration>

SessionFactoryUtil

public class SessionFactoryUtil {

private static SessionFactory sessionFactory;
private static ServiceRegistry serviceRegistry;

private static SessionFactory configureSessionFactory() 
        throws HibernateException {

    Configuration configuration = new Configuration();
    configuration.configure();
    serviceRegistry = new ServiceRegistryBuilder()
            .applySettings(configuration.getProperties())
            .buildServiceRegistry();

    sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    return sessionFactory;
}


public static SessionFactory getInstance() {
    return configureSessionFactory();
    //return sessionFactory;
}

public Session getCurrentSession() {
    return sessionFactory.getCurrentSession();
}

public static void close() {
    if (sessionFactory != null) {
        sessionFactory.close();
        sessionFactory = null;
    }
}

  }

A typical method from my db_access_class

  @Override
public School loadSchoolInfo(School schoolInfo) {
    Transaction tx = null;
    Session session = SessionFactoryUtil.getInstance().getCurrentSession();
    try {
        tx = session.beginTransaction();
        School school = new School();
        String admin = schoolInfo.getAdministrator_name();

        Query q1 = (Query) session.getNamedQuery("school.admin");
        q1.setParameter("admin", admin);
        for (Iterator<?> iter = q1.iterate(); iter.hasNext();)
            school = (School) iter.next();
        schoolInfo.setAdministrator_name((school.getAdministrator_name()));
        schoolInfo.setEmail((school.getEmail()));
        schoolInfo.setSchool_name((school.getSchool_name()));

        tx.commit();
    } catch (RuntimeException e) {
        if (tx != null && tx.isActive()) {
            try {
                tx.rollback();
            } catch (HibernateException e1) {
                e1.printStackTrace();
            }
            throw e;
        }
             } finally {
            if (session != null) {
            if (session.isOpen())
                session.close();
        }
        }
        return schoolInfo;
}

Answer 1

Try changing c3p0.max_size to around 100 and c3p0.min_size to 10. The way it is set up now will only use one connection, which is likely the bottleneck you are experiencing. If you intentionally set these options to 1, I'm curious as to why, because if only one connection is to be used, you may as well throw away c3p0.