Using the subselect hibernate annotation with entity

Question

I want to create a view from two tables using annotations in java/JEE, so after some researching I found that the only way to do it is using @Subselectannotation so I created 3 entities

the test entity:

 @Table
    @Entity
    public class Test {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long id;
        @Column
        private String nom;
        @Column
        private String prenom;
        @Column
        private int age;
        @Column
        private String adresse;
        @Column
        private Date dateNaissance;
    // getters and setters
    }

The compte entity:

 @Entity
    public class Compte {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long id;
        @Column
        private long solde;
        @Column
        private long numero;
        @Column
        private String description;
    // getters and setters
    }

and the entity Compte_Test which has the same role as a view

@Entity
@Subselect("SELECT c2.id as idTest,c1.id as id,c1.solde as solde,c1.numero as numero, c2.nom as nom, c2.prenom as prenom FROM Compte c1 INNER JOIN Test c2 ON c1.id_test= c2.id")
@Synchronize({ "compte", "test" })
public class Compte_Test {

    @Id
    private long idCompte_Test;
    @Column
    private long idTest;
    @Column
    private long id;
    @Column
    private long solde;
    @Column
    private long numero;
    @Column
    private String nom;
    @Column
    private String prenom;

 // getters and setters
    }

I'm using spring data @Query annotation to get data from Compte_Test entity

@Query(value = "SELECT c from Compte_Test c ")
List<Compte_Test> fullTextSearch(Pageable pageable);

The problem is that it generates for me an error

org.postgresql.util.PSQLException: ERROR: column compte_tes0_.id_compte_test does not exist
  Position : 8
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]

have you checked the database for the column? Is it exactly as query expects? The name `idCompte_Test` is a bit unusual name and Hibernate by default seems to prefix capital-camel-case with `_` also so this miight be the problem. — pirho, Nov 12 '18 at 16:32
thanks @pirho for your time, I have changed the name of the id whithout the underscore but that changed nothing — Wassim Makni, Nov 13 '18 at 07:49
Ok but what is the field in db? is it exactly `id_compte_test`? — pirho, Nov 13 '18 at 07:58
Well, that is a bigger problem then. About the subselect, do you think you could achieve the same result by other means, like my answer to [this quiestion](https://stackoverflow.com/q/53239376/6413377) suggests? — pirho, Nov 13 '18 at 09:16

Using the subselect hibernate annotation with entity

0 Answers0