Hibernate not releasing connections from connection pool
I'm creating an application with Hibernate JPA and I use c3p0 for connection pooling with MySQL. I have an issue with the number of connections to the MySQL database as it hits the 152 opened connections, this is not wanted since I define in my c3p0 config file the max pool size to 20, and of course I close every entity manager I get from the EntityManagerFactory after committing every transaction.
For every time a controller is executed, I notice more than 7 connections are opened, and if I refresh, then 7 connections are opened again without the past idle connections being closed. And in every DAO function I call, the em.close() is executed. I admit here that the issue is in my code, but I don't know what I am doing wrong here.
This is the Sondage.java entity:
@Entity
@NamedQuery(name="Sondage.findAll", query="SELECT s FROM Sondage s")
public class Sondage implements Serializable {
private static final long serialVersionUID = 1L;
public Sondage() {}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String name;
private byte needLocation;
//bi-directional many-to-one association to ResultatSondage
@OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
@OrderBy("sondage ASC")
private List<ResultatSondage> resultatSondages;
//bi-directional many-to-one association to SondageSection
@OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
private List<SondageSection> sondageSections;
}
And here's my DAO class:
@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
EntityManager em = PersistenceManager.getEntityManager();
List<Sondage> allSondages = new ArrayList<>();
try {
em.getTransaction().begin();
Query query = em.createQuery("SELECT s FROM Sondage s");
allSondages = query.getResultList();
em.getTransaction().commit();
} catch (Exception ex) {
if (em.getTransaction().isActive()) {
em.getTransaction().rollback();
}
allSondages = null;
} finally {
em.close();
}
return allSondages;
}
As you see, em is closed. In my JSP, I do this: I know this is not the good way of doing thing in the view side.
<body>
<div class="header">
<%@include file="../../../Includes/header.jsp" %>
</div>
<h2 style="color: green; text-align: center;">الاستمارات</h2>
<div id="allsurveys" class="pure-menu custom-restricted-width">
<%
List<Sondage> allSondages = (List<Sondage>) request.getAttribute("sondages");
for (int i = 0; i < allSondages.size(); i++) {
%>
<a href="${pageContext.request.contextPath }/auth/dosurvey?id=<%= allSondages.get(i).getId()%>"><%= allSondages.get(i).getName()%></a>
<%
if (request.getSession().getAttribute("user") != null) {
Utilisateur user = (Utilisateur) request.getSession().getAttribute("user");
if (user.getType().equals("admin")) {
%>
<a href="${pageContext.request.contextPath }/aauth/editsurvey?id=<%= allSondages.get(i).getId()%>">تعديل</a>
<%
}
}
%>
<br />
<%
}
%>
</div>
</body>
I'm guessing that every time I call user.getType(), a request is established ? If so, how can I prevent this?
For c4p0 config file, I included it in persistence.xml, I saw several posts saying that I need to put the c3p0 config file in c3p0-config.xml, but with my setup the c3p0 is initialized with the values I pass in the persistence.xml file, also the mysql connections are reaching 152 connections but the maxpoolsize is at 20, here's the persistence.xml file
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="CAOE" transaction-type="RESOURCE_LOCAL">
<class>com.caoe.Models.ChoixQuestion</class>
<class>com.caoe.Models.Question</class>
<class>com.caoe.Models.Reponse</class>
<class>com.caoe.Models.ResultatSondage</class>
<class>com.caoe.Models.Section</class>
<class>com.caoe.Models.Sondage</class>
<class>com.caoe.Models.SondageSection</class>
<class>com.caoe.Models.SousQuestion</class>
<class>com.caoe.Models.Utilisateur</class>
<properties>
<property name="hibernate.connection.provider_class"
value=" org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.password" value=""/>
<property name="hibernate.connection.url"
value="jdbc:mysql://localhost:3306/caoe?useUnicode=yes&characterEncoding=UTF-8"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.c3p0.max_size" value="50" />
<property name="hibernate.c3p0.min_size" value="3" />
<property name="hibernate.c3p0.max_statements" value="20" />
<property name="hibernate.c3p0.acquire_increment" value="1" />
<property name="hibernate.c3p0.idle_test_period" value="30" />
<property name="hibernate.c3p0.timeout" value="35" />
<property name="hibernate.c3p0.checkoutTimeout" value="60000" />
<property name="hibernate.connection.release_mode" value="after_statement" />
<property name="debugUnreturnedConnectionStackTraces"
value="true" />
</properties>
</persistence-unit>
</persistence>
EDIT: I'm deploying the Application to a red hat server with Tomcat and MySQL Installed. I'm just wondering why Hibernate is opening too much connections to MySQL, with all entity managers closed no connection will remain open, but this is not the case. I'm guessing and correct me if I'm true that the connections are opened when I do something like this:
List<Sondage> allSondages = SondageDao.getAllSondages();
for (Sondage sondage : allSondages) {
List<Question> questions = sondage.getQuestions();
//code to display questions for example
}
Here when I use sondage.getQuestions(), does Hibernate open a connection to the database and doesn't close it after, am I missing something in the configuration file that close or return connection to pool when it's done with it. Thanks in advance for any help.
EDIT2 : Since people are asking for versions, here they are : JAVA jre 1.8.0_25 Apache Tomcat v7.0 hibernate-core-4.3.10 hibernate c3p0 4.3.10.final hibernate-jpa 2.1 Thanks in advance
The mysql version is Mysql 5.6.17 if that can help...
EDIT 4: as people are getting confused about witch version of the code I posted is buggy, let me edit this so you'll know what happens exactly:
First I'll start by showing what's the buggy code, as you guys don't care about what's working:
@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
EntityManager em = PersistenceManager.getEntityManager();
List<Sondage> allSondages = new ArrayList<>();
try {
em.getTransaction().begin();
Query query = em.createQuery("SELECT s FROM Sondage s");
allSondages = query.getResultList();
em.getTransaction().commit();
} catch (Exception ex) {
if (em.getTransaction().isActive()) {
em.getTransaction().rollback();
}
allSondages = null;
} finally {
em.close();
}
return allSondages;
}
So this is basically what I did for all my dao functions, I know transaction is not needed here, since I saw questions pointing that transactions are important for connection to close. beside this , I getEntityManager from PersistenceManager class that has an EntityManagerFactory singleton Object, so getEntityManager creates an entityManager from the EntityManagerFactory singleton Object:=> code is better than 1000 word : PesistenceManager.java:
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
public class PersistenceManager
{
private static EntityManagerFactory emf = null;
public static EntityManager getEntityManager()
{
return getEntityManagerFactory().createEntityManager();
}
public static EntityManagerFactory getEntityManagerFactory()
{
if(emf == null) {
emf = Persistence.createEntityManagerFactory("CAOE");
return emf;
}
else
return emf;
}
}
Yes this is cool and all good, but where's the problem?
The problem here is that this version opens the connections and never close them, the em.close() have no effect, it keeps the connection open to the database.
The noob fix:
What I did to fix this issue is create an EntityManagerFactory for every request, it mean that the dao looks something like this:
@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
//this is the method that return the EntityManagerFactory Singleton Object
EntityManagerFactory emf = PersistenceManager.getEntitManagerFactory();
EntityManager em = emf.createEntityManager();
List<Sondage> allSondages = new ArrayList<>();
try {
em.getTransaction().begin();
Query query = em.createQuery("SELECT s FROM Sondage s");
allSondages = query.getResultList();
em.getTransaction().commit();
} catch (Exception ex) {
if (em.getTransaction().isActive()) {
em.getTransaction().rollback();
}
allSondages = null;
} finally {
em.close();
emf.close();
}
return allSondages;
}
Now this is bad and I'll just keep it while I don't have answer for this question (it seems like forver :D ). So with this code basically All connections gets closed after hibernate doesn't need them. Thanks in advance for any efforts you put in this question :)
Answer
I think that Hibernate and C3P0 are behaving correctly here. In fact you should see that there are always at least three connections to the database open as per your C3P0 configuration.
When you execute a query Hibernate will use a connection from the pool and then return it when it is done. It will not close the connection. C3P0 might shrink the pool if the min size is exceeded and some of the connections time out.
In your final example you see the connections closed because you've shut down your entity manager factory and therefore your connection pool as well.