Leakage of connections with Hibernate

Question

My system presented sporadic crashes and I ended up discovering that this happened due to processes that opened a connection but, due to some error, did not close it (Connection Leak).

I solved involving the problematic code in a try/catch and forcing the closure on finally

My question is: Is there any way to prevent this leak or some timeout setting where, after some downtime, the connection is returned/closed?

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence      http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="udPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <class>br.com.xpto.etc</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
      <property name="hibernate.show_sql" value="false"/>
      <property name="hibernate.format_sql" value="false"/>
      <property name="hibernate.multiTenancy" value="SCHEMA"/>
      <property name="hibernate.tenant_identifier_resolver" value="br.com.multitenant.SchemaResolver"/>
      <property name="hibernate.multi_tenant_connection_provider" value="br.com.multitenant.MultiTenantProvider"/>
      <property name="hibernate.enable_lazy_load_no_trans" value="true"/>
      <property name="hibernate.c3p0.acquire_increment" value="2"/>
      <property name="hibernate.c3p0.iddle_teste_period" value="70"/>
      <property name="hibernate.c3p0.max_size" value="15"/>
      <property name="hibernate.c3p0.max_statements" value="0"/>
      <property name="hibernate.c3p0.min_size" value="1"/>
      <property name="hibernate.c3p0.timeout" value="60"/>
    </properties>
  </persistence-unit>
</persistence>

Entitymanagerfactory

public class FabricaEntityManager {

    private static FabricaEntityManager INSTANCE;
    private static EntityManagerFactory emf;
    private final static Logger log=Logger.getLogger(FabricaEntityManager.class);

    private FabricaEntityManager() {}

    public static FabricaEntityManager getInstance() {
        if (INSTANCE == null) {
                synchronized (FabricaEntityManager.class) {
                    if (INSTANCE == null) {
                        INSTANCE = new FabricaEntityManager();
                        log.debug("Nova Fabrica");
                    }
                }
            }
        return INSTANCE;
    }

    public EntityManagerFactory getEntityManagerFactory(){
        if (emf==null){
            emf=Persistence.createEntityManagerFactory("udonlinePU");
            log.debug("Create EMF");
        }
        return emf;
    }
}

Trade example

public EntityManager getEntityManager(){
        if (manager == null || !manager.isOpen()) {
            manager=FabricaEntityManager.getInstance().getEntityManagerFactory().createEntityManager();
            log.debug(">> CreateEntityManager");
        }
        return manager;
    }
public List<Despesa> getByAll(){
        Query qry=getEntityManager().createQuery("select d from Despesa d ");
        List<Despesa> rs = qry.getResultList();
        getEntityManager().close();
        return rs;
    }

Answer 1

The right way is to use some global solution for transaction control. I can remember 3 now:

1. Using the Opensessioninview standard: This way you will have a Filterweb that when receiving the request would open a connection, and when you finish it would close. This would be done with the Try/Finally you created to solve the other problem.
2. With EJB - If you’re using a server that has EJB, let EJB handle the transaction for you.
3. With Spring - Use Spring and let it control the transaction for you.

If you do not use a framework/standard that controls the transaction you will have to do Try/Finally in all your methods.

OBS.: connection pool only serves to give you the connection, and nothing else. OBS2.: I honestly have never seen a configuration that kills a certain connection that is active at X time. This errp is usually a bad practice of programming.