User has exceeded the 'max_user_connections' Resource (Current value: 4)

Question

I have a problem regarding open connections in the database, where error is occurring if the application tries to create more connections than the limit. Assuming the application creates and closes the connections for each interaction in the database (Insert/update/select), how to make the application "wait" for a new connection? I am using Hibernate + java.

        EntityManagerFactory factory = Persistence.createEntityManagerFactory("model");
        EntityManager manager = factory.createEntityManager();

        manager.getTransaction().begin();    
        manager.persist(produto);
        manager.getTransaction().commit();  

        manager.close();
        factory.close();

Edit

I took a look over SessionFactory, configured the app to use Connection Pool hibernate.cfg.xml , but the error still occurs.

Hibernateutils.java

sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();

Here the Save Method

    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();
    session.save(produto);
    session.getTransaction().commit();

Hibernate.cfg.xml

    <!-- Pool de conexão -->
    <property name="hibernate.c3p0.min_size">1</property>
    <property name="hibernate.c3p0.max_size">4</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">3000</property>

    <!-- entidade mapeada -->
    <mapping class="br.com.sales.model.ProdutosVO" />

Answer 1

Here’s the answer for everyone with the same problem regarding c3p0 configuration.

Error was fixed just by setting the hibernate.connection.provider_class in the xml. As far as I have read, without setting the Provider, the c3p0 does not run.

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> 

Compellingly have these 3 libs in your classhPath

c3p0-0.9.2.1.jar
hibernate-c3p0-4.2.4.Final.jar
mchange-commons-java-0.2.3.4.jar

Another problem also found went to the idle connections that are in the connection pool, where if the application tries to perform an operation using such connections, error occurs Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. For solution, the time the application is testing the active connections before the timeout.

<property name="hibernate.c3p0.timeout">300</property>
// Test_period tem de ser um numero menor que o timeout.
// Neste caso, a aplicação fica testando as conexões a cada 100s
<property name="hibernate.c3p0.idle_test_period">100</property>

Obs: Also care should be taken to close the Session after each operation.

Example:

@SuppressWarnings("unchecked")
public List<Empresa> getEmpresasConnectionPool() throws Exception{
    Session session = null;
    try {
        session = HibernateUtil.getSessionFactory().openSession();
        List<Empresa> lista = session.createQuery("select t from Empresa as t ORDER BY id ASC").list();
        return lista;       
    } catch (Exception e) {
        throw new Exception(Validation.getCause(e));
    }finally {
        session.close();
    }
}