How can I prevent Hibernate + c3p0 + MySql creating large numbers of sleeping connections?

Question

I'm using GWT with Hibernate, c3p0 and MySQL to produce a web app with a limited audience (max 50 users per day). During testing I found that Hibernate was opening a connection with each session but not closing it, irrespective of use of the close() method.

My current configuration is as follows:

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=
hibernate.connection.username=
hibernate.connection.password=
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.current_session_context_class=thread
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=1
hibernate.c3p0.timeout=10
hibernate.c3p0.max_statements=50
hibernate.c3p0.idle_test_period=10
hibernate.c3p0.unreturned_connection_timeout=1
hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider

With each new connection to the application a new pool is created. For example if I set the pool size to 3, 2 connections to the application result in 6 connections until the application is closed.

The intended behaviour is to simply close or reuse the connections after each transaction. How can I achieve this?

Answer 1

The concept of a connection pool is exactly that. You have a pool of opened connnections, and when you need to do a transaction, you get a connection already opened. This way, you save a lot of time opening and closing connections. But you pay the price to keep the connections opened when you are not using them.

You have more info about c3p0 configuration

Update Apparently the OP was calling buildSessionFactory once per session. This has to be called once per lifetime of the application.

Here's the utility class that builds the sessionFactory of Hibernate and provides the session class to whoever asks for it. This is the cumberstone for a DAO class.

import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.classic.Session;


public class HibernateUtil {

      private static final SessionFactory sessionFactory;

      static {
          try {
              // Create the SessionFactory from hibernate.cfg.xml
              sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
          } catch (Throwable ex) {
              // Make sure you log the exception, as it might be swallowed
              System.err.println("Initial SessionFactory creation failed." + ex);
              throw new ExceptionInInitializerError(ex);
          }
      }

      public static SessionFactory getSessionFactory() {
          return sessionFactory;
      }

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

}

Answer 2

Its not a good ideia to use a Connection Pool if you want to close the connection after each transaction. It's exacly what connection pools want to avoid... You should just turn off C3PO. Hibernate will handle the connection by himself (open and close as simple JDBC connection in each transaction)

Answer 3

During testing I found that Hibernate was opening a connection with each session but not closing it, irrespective of use of the close() method

When using a connection pool, calling Connection#close() doesn't physically close the connection but return it to the pool for future reuse. In other words, the connection stays open and that's the whole point of using a pool.

---

I call the following: AnnotationConfiguration().buildSessionFactory().getCurrentSession();

Well, that's the problem. You are creating a SessionFactory over and over (each creating its own pool) while you should create it only once for the lifetime of your application. If you are not using any particular framework, this is typically done in some utility class (the famous HibernateUtil class).

The official Hibernate Tutorial has a very basic example of such a class. Or see this one which is a bit richer.