User has exceeded the 'max_user_connections' resource (current value: 4)

2

I have a problem with the open connections in the database, where an error is occurring if the application tries to create more connections than the limit. Assuming that 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. / p>

        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 at SessionFactory , I configured the application to use the Connection Pool hibernate.cfg.xml , but the error still occurs.

HibernateUtils.java

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

Here the method of recording

    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" />
    
asked by anonymous 26.05.2016 / 20:14

1 answer

3

Here is the answer for everyone who has the same problem regarding the c3p0 configuration.

The error has been solved only by setting hibernate.connection.provider_class to xml . As far as I've read, without setting the provider, c3p0 does not run.

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

Mandatory to have these 3 libs in their 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 encountered was idle connections that stay in connection pool , where if the application tries to execute an operation using such connections, error Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. For solution, it has changed the time the application is testing the active connections before 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>

Note: You should also be careful 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();
    }
}
    
30.05.2016 / 00:28