entitymanager.close() not closing connections to database

Question

I'm developing a quiz system and I'm new to JPA & Hibernate. I've used hibernate 4.2.3 and I've used c3p0 connection pooling. Code works fine but each EntityManager creates a connection which is never closed. And once the max number of connections are reached then application can't access database. I'm using MySQL 5.6.10, when I see connections in workbench I never see connections being destroyed. And application is not reusing the connections.

My guess is that connections are not returned to connection pool. I dunno how as i have written "manager.close()".

The same happens with hibernate internal connection pooling. (In case i remove c3p0.)

Here are the properties of "persistence.xml"

<property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />
<property name="hibernate.c3p0.max_size" value="10" />
<property name="hibernate.c3p0.min_size" value="2" />
<property name="hibernate.c3p0.acquire_increment" value="1" />
<property name="hibernate.c3p0.idle_test_period" value="5000" />
<property name="hibernate.c3p0.max_statements" value="20" />
<property name="hibernate.c3p0.timeout" value="500" />

Here is how i access EntityManagerFactory

public class EntityMangFactory {

    private static EntityManagerFactory emf=null;

    private static void initEntityManagerFactory()
    {
        emf=Persistence.createEntityManagerFactory("com.oes.jpa");  //persistence-unit-name//

    }

    public static EntityManagerFactory getEntityManagerFactory()
    {
        if(emf==null){
            initEntityManagerFactory();
        }
        return emf;
    }


}

Here is how i access the database.

public static List<MarksDTO> getMarks(int id){

        EntityManagerFactory factory= EntityMangFactory.getEntityManagerFactory();
        EntityManager manager= factory.createEntityManager();
        manager.getTransaction().begin();
        TypedQuery<MarksDTO> q= manager.createQuery("select new com.examsystem.DTO.MarksDTO(m.courseId,m.score,m.setNo,m.courseName) from MarksBean as m where TraineeID=:TraineeID",MarksDTO.class);
        q.setParameter("TraineeID", id);
        List<MarksDTO> ls=q.getResultList();


        manager.close();

        return ls;
    }

Please point me to where I'm wrong.

Thanks in advance.

Answer 1

I wasn't committing each transaction. Hence the problem.