Entitymanager query always return an empty list using JPA on a JEE webapp

Question

Im dealing with a problem since a long time on my JEE Project with Maven;

My entityManager.createQuery().getResultList() always return an empty list.

The database connection is ok, Ive got data in my tables and it works perfectly well using only JDBC. I think I tried almost everything that I saw on the internet, Im just propably missing a point...

I also tried with and without EJB and moving the librairies on the WEB-INF directory if Tomcat didnt recognize them.

I tried with JPQL request as below but also with the NamedQuery annotation and still the same result.

This is my code (DAO) :

public List<Conge> getLeaves(String username) {
    EntityManagerFactory entityManagerFactory = null;
    EntityManager entityManager = null;
    List<Conge> listLeaves = new ArrayList<Conge>();
    try {
        entityManagerFactory = Persistence.createEntityManagerFactory("ProjetJEE");
        entityManager = entityManagerFactory.createEntityManager();
        List<Employe> a = entityManager.createQuery("select c.login from Employe c").getResultList();
        System.out.println("a" + a); //always empty
    } finally {
        if ( entityManager != null ) entityManager.close();
        if ( entityManagerFactory != null ) entityManagerFactory.close();
    }
   return listLeaves;
}

My Employe.java (Entity) :

package org.gdc.models;

import java.io.Serializable;
import javax.persistence.*;
import java.util.List;


/**
 * The persistent class for the Employe database table.
 * 
 */
@Entity
@NamedQuery(name="Employe.findAll", query="SELECT e FROM Employe e")
public class Employe implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    private String login;

    private String adresse;

    @Column(name="code_postal")
    private String codePostal;

    private String mail;

    private String nom;

    private String pnom;

    @Column(name="solde_congés")
    private int soldeCongés;

    private String ville;

    //bi-directional one-to-one association to Authentification
    @OneToOne(mappedBy="employe")
    private Authentification authentification;

    //bi-directional many-to-one association to Conge
    @OneToMany(mappedBy="employe")
    private List<Conge> conges;

    //bi-directional many-to-one association to Ref_Equipe
    @ManyToOne
    @JoinColumn(name="equipe")
    private Ref_Equipe refEquipe;

    //bi-directional many-to-one association to Ref_Fonction
    @ManyToOne
    @JoinColumn(name="fonction")
    private Ref_Fonction refFonction;

    //bi-directional many-to-one association to Rendez_vous
    @OneToMany(mappedBy="employe")
    private List<Rendez_vous> rendezVouses;

    public Employe() {
    }

    public String getLogin() {
        return this.login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public String getAdresse() {
        return this.adresse;
    }

    public void setAdresse(String adresse) {
        this.adresse = adresse;
    }

    public String getCodePostal() {
        return this.codePostal;
    }

    public void setCodePostal(String codePostal) {
        this.codePostal = codePostal;
    }

    public String getMail() {
        return this.mail;
    }

    public void setMail(String mail) {
        this.mail = mail;
    }

    public String getNom() {
        return this.nom;
    }

    public void setNom(String nom) {
        this.nom = nom;
    }

    public String getPnom() {
        return this.pnom;
    }

    public void setPnom(String pnom) {
        this.pnom = pnom;
    }

    public int getSoldeCongés() {
        return this.soldeCongés;
    }

    public void setSoldeCongés(int soldeCongés) {
        this.soldeCongés = soldeCongés;
    }

    public String getVille() {
        return this.ville;
    }

    public void setVille(String ville) {
        this.ville = ville;
    }

    public Authentification getAuthentification() {
        return this.authentification;
    }

    public void setAuthentification(Authentification authentification) {
        this.authentification = authentification;
    }

    public List<Conge> getConges() {
        return this.conges;
    }

    public void setConges(List<Conge> conges) {
        this.conges = conges;
    }

    public Conge addConge(Conge conge) {
        getConges().add(conge);
        conge.setEmploye(this);

        return conge;
    }

    public Conge removeConge(Conge conge) {
        getConges().remove(conge);
        conge.setEmploye(null);

        return conge;
    }

    public Ref_Equipe getRefEquipe() {
        return this.refEquipe;
    }

    public void setRefEquipe(Ref_Equipe refEquipe) {
        this.refEquipe = refEquipe;
    }

    public Ref_Fonction getRefFonction() {
        return this.refFonction;
    }

    public void setRefFonction(Ref_Fonction refFonction) {
        this.refFonction = refFonction;
    }

    public List<Rendez_vous> getRendezVouses() {
        return this.rendezVouses;
    }

    public void setRendezVouses(List<Rendez_vous> rendezVouses) {
        this.rendezVouses = rendezVouses;
    }

    public Rendez_vous addRendezVous(Rendez_vous rendezVous) {
        getRendezVouses().add(rendezVous);
        rendezVous.setEmploye(this);

        return rendezVous;
    }

    public Rendez_vous removeRendezVous(Rendez_vous rendezVous) {
        getRendezVouses().remove(rendezVous);
        rendezVous.setEmploye(null);

        return rendezVous;
    }

}

My pom.xml :

    <project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>ProjetJEE</groupId>
    <artifactId>ProjetJEE</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>Leave Management System</name>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>eclipselink</artifactId>
            <version>2.7.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>persistence-api</artifactId>
            <version>1.0.2</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.ejb</groupId>
            <artifactId>ejb-api</artifactId>
            <version>3.0</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <sourceDirectory>src</sourceDirectory>
        <resources>
            <resource>
                <directory>src</directory>
                <excludes>
                    <exclude>**/*.java</exclude>
                </excludes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.0</version>
                <configuration>
                    <release>12</release>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.2.1</version>
                <configuration>
                    <warSourceDirectory>WebContent</warSourceDirectory>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

And my persistence.xml :

<?xml version="1.0" encoding="UTF-8"?>
<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="ProjetJEE"
        transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <class>org.gdc.models.Authentification</class>
        <class>org.gdc.models.Conge</class>
        <class>org.gdc.models.CongePK</class>
        <class>org.gdc.models.Employe</class>
        <class>org.gdc.models.Ref_Equipe</class>
        <class>org.gdc.models.Ref_Etat_Conge</class>
        <class>org.gdc.models.Ref_Fonction</class>
        <class>org.gdc.models.Ref_Motif_Conge</class>
        <class>org.gdc.models.Ref_Type_Conge</class>
        <class>org.gdc.models.Rendez_vous</class>
        <class>org.gdc.models.Rendez_vousPK</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>

        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/ProjetJEE" />
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="mysql" />
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
<!--            <property name="eclipselink.logging.level" value="FINE" />
            <property name="eclipselink.ddl-generation" value="create-tables" /> -->
        </properties>
    </persistence-unit>
</persistence>

On Eclipse with JEE, Maven, Mysql, Tomcat9, JPA

If you need something else to help me, let me know...

Thanks in advance

Answer 1

The only observation I have is that you are creating EntityManager from the factory and you are not injecting it. This way I think you will find problems during the application lifecycly. You have to handle by yourself the transactions (commit, rollback, etc). I see you are closing the connections in the final block, but I don't know if you have a transaction or you need to open a new one.

Please see: PersistenceUnit vs PersistenceContext
and https://www.logicbig.com/tutorials/java-ee-tutorial/jpa/entity-context.html

I don't know why is the reason you are doing that way, but in your case I would do something like this:

public abstract class GenericDAO  {    
    protected EntityManager entityManager;

    @PersistenceContext(unitName="canvasEM")
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
}

@Repository
public class EmployeDAOImpl extends GenericDAO implements EmployesDAO{
    public List<Employe> getEmployes() {
        List<Employe> list = entityManager.createQuery("select c from Employe c").getResultList();  
        return list;
    }       
}       

This is Spring, but I guess there is a way how to Inject the EntityManager directly in the EJB.