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

I think that the problem is on your MySQL driver, try this one: `com.mysql.jdbc.Driver` — Ahmed HENTETI, Nov 23 '19 at 23:53
@A.Henteti Thx for your help. In the persistence.xml ? I tried it and got this warning "Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary." and still an empty list :/ — Cetelem, Nov 24 '19 at 00:33
your JPQL query doesn't seem correct. You are selecting only one field but you are mapping to a list of Objects. Try `select c from Employe c` — Ermal, Nov 24 '19 at 11:02
Yea I tried like this too, still the same result. I also tried with other table and I also drop and recreate my entire database. Nothing's worked — Cetelem, Nov 24 '19 at 12:50

score 1 · Answer 1 · answered Nov 23 '19 at 23:56

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.

Ty for your help. I didnt use @PersistenceContext because there are some limitations with Tomcat, it's not a JEE container. See this : [https://stackoverflow.com/questions/11256746/using-jpa2-in-tomcat-6-persitencecontext-doesnt-work-entitymanager-is-null] But Im pretty sure that the problem isnt here because the database connection is working. — Cetelem, Nov 24 '19 at 00:48
If you want to use Tomcat than I think you need Spring. Otherwise I don't understand who will handle for you the transactions. The queston you sent, is true, is talking about the "Limitations to JPA" but, you can achieve the usage of JPA and transactions using Spring. — Ermal, Nov 24 '19 at 11:55
Ok I'd have done that, but it's a little webapp so I think Ill go back to the good old JDBC. Ty for your time — Cetelem, Nov 24 '19 at 15:15

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

1 Answers1