Hibernate - Map View to Entity - view is not mapped [SELECT la FROM ListAgent la]

Question

In an effort to power an autocomplete, I resolved to map a PostgreSQL view. I followed a tutorial for this.

It seems that it does not work at all : I've always a java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: ListAgent is not mapped [SELECT la FROM ListAgent la].

However, I have tried many things to solve my problem:

• Map of a view in an entity (as in the tutorial);
• Using a @Subselect;
• Creation of a materialized view and its unique index;
• Setting exclude-unlisted-classes to false (persistence.xml);
• deactivation of JPA validator in build in Eclipse (due to problems with @Entity);
• Added constructors and setters (may be unnecessarily)

No success: always the same answer.

The Test-case

package fr.gouv.finances.douane.cotes.dao.agent;

import static org.junit.Assert.assertEquals;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

    /**
     * Tests unitaires de la classe PasswordDAOImpl
     * 
     * @author VANDEKERKHOVE
     * @version 0.0.1.SNAPSHOT
     */
    public class AgentDAOImplTest {
        
        // Stateless
        private AgentDAOImpl dao;
        
        // Emulation du serveur Wildfly
        private EntityManager em;
        private static EntityManagerFactory emf;

        @SuppressWarnings("unused")
        private List<?> las;
        
        /**
         * Initialisation de l'environnement de test
         * @throws java.lang.Exception
         */
        @BeforeClass
        public static void setUpBeforeClass() throws Exception {
            // Factory construite une fois
            emf = Persistence.createEntityManagerFactory("cote-s-ejbTest");
        }
        
        /**
         * Remise en etat de l'environnement de test
         * @throws java.lang.Exception
         */
        @AfterClass
        public static void tearDownAfterClass() throws Exception {
            // Suppression de la factory
            emf.close();
            emf = null; 
        }
        
        /**
         * Initialisation avant chaque test
         * @throws java.lang.Exception
         */
        @Before
        public void setUp() throws Exception {
            // Au debut de chaque testcase
            em = emf.createEntityManager();
            em.getTransaction().begin();
            
            // Stateless
            dao = new AgentDAOImpl();
            dao.setEm(em);
        }
        
        /**
         * Reset après chaque test
         * @throws java.lang.Exception
         */ 
        @After
        public void tearDown() throws Exception {
            // A la fin de chaque test
            em.getTransaction().commit();
            em.close();
            
            // Fin du Stateless
            dao.setEm(null);
            dao = null;
        }
        
        //=========================
        // Tests proprements dits
        //=========================
        @Test
        public void test() {
            List<ListAgent> las = em.createQuery("SELECT la FROM ListAgent la", ListAgent.class).getResultList();
            
            ListAgent la_enr = las.get(0);
            
            assertEquals("FROC Christian", la_enr.getName());
        }
}

The class ListAgent

package fr.gouv.finances.douane.cotes.dao.agent;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import org.hibernate.annotations.Immutable;
import org.hibernate.annotations.Subselect;

/**
 * Classe de requete pour la vue "l_agents"
 * 
 * @author Jean Paul Vandekerkhove
 * @version 0.0.2.SNAPSHOT
 */
@Entity
@Immutable
@Subselect("SELECT a.a_id AS id,\r\n" + 
        "    ((btrim((a.a_nom)::text) || ' '::text) || btrim((a.a_prenom)::text)) AS name\r\n" + 
        "   FROM agent a\r\n" + 
        "  WHERE ((a.a_actif = true) OR ((a.a_actif = false) AND (a.a_datesuppression >= CURRENT_DATE)));")
//@Table(name="l_agents", schema="cotes_admin")
public class ListAgent {
    
    //=========================
    // Attributs
    //========================= 
    @Id
    @Column(name="id", updatable = false, nullable = false)
    private Long id;
    
    @Column(name="name")
    private String name;

    //=========================
    //     Constructeurs
    //=========================     
    /**
     * Constructeur vide
     */
    public ListAgent() {
        super();
    }   
    
    /**
     * Constructeur complet
     * 
     * @param id - Identifiant de l'agent
     * @param name - nom-prénom de l'agent
     */
    public ListAgent(Long id, String name) {
        super();
        this.id = id;
        this.name = name;
    }   

    //=========================
    //    Getters et Setters
    //=========================
    /**
     * Retourne l'identifiant de l'Agent
     * @return id - identifiant de l'Agent
     */
    public Long getId() {
        return id;
    }

    /**
     * MaJ de l'Identifiant de l'Agent
     * @param id - Identifiant de l'agent
     */
    public void setId(Long id) {
        this.id = id;
    }
    
    /**
     * Retourne l'identité complete de l'Agent
     * @return name - nom-prénom de l'Agent
     */
    public String getName() {
        return name;
    }

    /**
     * MaJ de l'identité complete de l'Agent
     * @param name - nom-prénom de l'Agent
     */
    public void setName(String name) {
        this.name = name;
    }
}

The POM

<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  
  <parent>
    <groupId>fr.gouv.finances.douane.cote-s</groupId>
    <artifactId>cote-s</artifactId>
    <version>0.0.1-SNAPSHOT</version>
  </parent>
  
  <artifactId>cote-s-ejb</artifactId>
  <packaging>ejb</packaging>
   
  <dependencies>
    
    <dependency>
        <groupId>org.jboss.spec.javax.ejb</groupId>
        <artifactId>jboss-ejb-api_3.2_spec</artifactId>
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>javax.enterprise</groupId>
        <artifactId>cdi-api</artifactId>
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.3.15.Final</version><!--$NO-MVN-MAN-VER$-->
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>org.hibernate.validator</groupId>
        <artifactId>hibernate-validator</artifactId>
        <scope>provided</scope>
    </dependency>
    
    <dependency>
        <groupId>javax.persistence</groupId>
        <artifactId>javax.persistence-api</artifactId>
        <scope>provided</scope>
    </dependency>
    
    <!-- Tests -->
    
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <scope>test</scope>
    </dependency>
    
    <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.12</version>
        <scope>test</scope>
    </dependency>
    
  </dependencies>
  
  <build>
    <finalName>${project.artifactId}</finalName>
    <plugins>
            
            <!-- package EJB 3 -->

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-ejb-plugin</artifactId>
                <version>3.1.0</version>
                <configuration>
                    <ejbVersion>3.2</ejbVersion>
                    <generateClient>true</generateClient>
                    <clientIncludes>
                        <clientInclude>**/*.class</clientInclude>
                    </clientIncludes>
                    <clientExcludes>
                        <clientExclude>**/*Bean.class</clientExclude>
                    </clientExcludes>
                </configuration>
            </plugin>
            
        </plugins>
    </build>
    
</project>

The main software used (all at the technical level)

• Eclipse 2020-06;
• OpenJDK Red Hat 11;
• Wildfly 19.1;
• PostgreSQL 11;

The persistence.xml (normal)

<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_2.xsd" version="2.2">
    <persistence-unit name="cote-s-ejb" transaction-type="JTA">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>    
        <jta-data-source>java:/CotesDS-ds</jta-data-source>
        <class>fr.gouv.finances.douane.cotes.dao.agent.Agent</class>
        <class>fr.gouv.finances.douane.cotes.dao.grade.Grade</class>
        <class>fr.gouv.finances.douane.cotes.dao.service.Service</class>
        <class>fr.gouv.finances.douane.cotes.dao.droit.Droit</class>
        <class>fr.gouv.finances.douane.cotes.dao.droitAgent.DroitAgent</class>
        <class>fr.gouv.finances.douane.cotes.dao.cycleCourt.CycleCourt</class>
        <class>fr.gouv.finances.douane.cotes.dao.desiderata.Desiderata</class>
        <class>fr.gouv.finances.douane.cotes.dao.disponibilite.Disponibilite</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtCyclecourt.Mvtcyclecourt</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtDesiderata.Mvtdesiderata</class>
        <class>fr.gouv.finances.douane.cotes.dao.password.Password</class>
        <class>fr.gouv.finances.douane.cotes.dao.activite.Activite</class>
        <class>fr.gouv.finances.douane.cotes.dao.coteService.CoteService</class>
        <class>fr.gouv.finances.douane.cotes.dao.quantieme.Quantieme</class>
        <class>fr.gouv.finances.douane.cotes.dao.sdf.Sdf</class>
        <class>fr.gouv.finances.douane.cotes.dao.vacation.Vacation</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtArtt.Mvtartt</class>
        <class>fr.gouv.finances.douane.cotes.dao.typeConge.Typeconge</class>
        <class>fr.gouv.finances.douane.cotes.dao.absences.Absence</class>
        <class>fr.gouv.finances.douane.cotes.dao.prevision.Prevision</class>
        <class>fr.gouv.finances.douane.cotes.dao.recuperation.Recuperation</class>
        <class>fr.gouv.finances.douane.cotes.dao.conges.Conges</class>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL95Dialect"/>
            <property name="hibernate.default_schema" value="cotes_admin"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>          
            <!-- JBoss -->
            <property name="jboss.entity.manager.jndi.name" value="java:/emCotes"/>
            <property name="jboss.entity.manager.factory.jndi.name" value="java:/emCotesFactory"/>
        </properties>
    </persistence-unit>       
</persistence>

The persistence.xml (tests)

<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_2.xsd" version="2.2">
    <persistence-unit name="cote-s-ejbTest" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>    
        <class>fr.gouv.finances.douane.cotes.dao.agent.Agent</class>
        <class>fr.gouv.finances.douane.cotes.dao.grade.Grade</class>
        <class>fr.gouv.finances.douane.cotes.dao.service.Service</class>
        <class>fr.gouv.finances.douane.cotes.dao.droit.Droit</class>
        <class>fr.gouv.finances.douane.cotes.dao.droitAgent.DroitAgent</class>
        <class>fr.gouv.finances.douane.cotes.dao.cycleCourt.CycleCourt</class>
        <class>fr.gouv.finances.douane.cotes.dao.desiderata.Desiderata</class>
        <class>fr.gouv.finances.douane.cotes.dao.disponibilite.Disponibilite</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtCyclecourt.Mvtcyclecourt</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtDesiderata.Mvtdesiderata</class>
        <class>fr.gouv.finances.douane.cotes.dao.password.Password</class>
        <class>fr.gouv.finances.douane.cotes.dao.activite.Activite</class>
        <class>fr.gouv.finances.douane.cotes.dao.coteService.CoteService</class>
        <class>fr.gouv.finances.douane.cotes.dao.quantieme.Quantieme</class>
        <class>fr.gouv.finances.douane.cotes.dao.sdf.Sdf</class>
        <class>fr.gouv.finances.douane.cotes.dao.vacation.Vacation</class>
        <class>fr.gouv.finances.douane.cotes.dao.mvtArtt.Mvtartt</class>
        <class>fr.gouv.finances.douane.cotes.dao.typeConge.Typeconge</class>
        <class>fr.gouv.finances.douane.cotes.dao.absences.Absence</class>
        <class>fr.gouv.finances.douane.cotes.dao.prevision.Prevision</class>
        <class>fr.gouv.finances.douane.cotes.dao.recuperation.Recuperation</class>
        <class>fr.gouv.finances.douane.cotes.dao.conges.Conges</class>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL95Dialect"/>
            <property name="hibernate.default_schema" value="cotes_admin"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/cotes"/>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.username" value="cotes_admin"/>
            <property name="hibernate.connection.password" value="cotes_admin"/>            
        </properties>
    </persistence-unit>       
</persistence>

Thank you in advance for your Help.

Best Regards

Answer 1

I put the pseudo-entity in the persistence.xml, and reset the exclude-unlisted-classes to true. Finaly, After removing the "::text)", the ";" final, and readjusted the parentheses, the test changed to green. The question is a priori closed.