CODEWITHSUNDEEP
javahibernatepostgresqljpa
Sid
Sid

Reputation: 341

Wrong query generation by hibernate JPA - ManyToMany

here my entities classes linked by ManyToMany:

Product : 

    package fr.test;

    import java.util.List;

    import javax.persistence.Column;
    import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.JoinTable;
    import javax.persistence.ManyToMany;
    import javax.persistence.OneToMany;
    import javax.persistence.Table;

@Entity
@Table(name = "product")
public class ProductDTO {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "product_id")
    private int id;
        public int getId() {return id;}
        public void setId(int id) {this.id = id;}

    @Column(name = "nom_product")
    private String nom;
        public String getNom() {return nom;}
        public void setNom(String nom) {this.nom = nom;}

    @Column(name = "application")
    private String application;
        public String getGroupeApplication() {return groupeApplication;}
        public void setGroupeApplication(String groupeApplication) {this.groupeApplication = groupeApplication;}

    @Column(name = "grp_app")
    private String groupeApplication;
        public String getApplication() {return application;}
        public void setApplication(String application) {this.application = application;}

    @ManyToMany
    @JoinTable(name = "product_mot_cle")
    private List<MotCleDTO> motscleInterdits;
        public List<MotCleDTO> getMotscleInterdits() {return motscleInterdits;}
        public void setMotscleInterdits(List<MotCleDTO> motscleInterdits) {this.motscleInterdits = motscleInterdits;}

    @ManyToMany
    @JoinTable(name ="product_extension")
    private List<ExtensionDTO> extensionsDisponibles;
        public List<ExtensionDTO> getExtensionsDisponibles() {return extensionsDisponibles;}
        public void setExtensionsDisponibles(List<ExtensionDTO> extensionsDisponibles) {this.extensionsDisponibles = extensionsDisponibles;}

    @OneToMany(mappedBy="prodDiff")
    List<DiffusionDTO> destinatairesPrincipaux;
        public List<DiffusionDTO> getDestinatairesPrincipaux() {return destinatairesPrincipaux;}
        public void setDestinatairesPrincipaux(List<DiffusionDTO> destinatairesPrincipaux) {this.destinatairesPrincipaux = destinatairesPrincipaux;}

    @OneToMany(mappedBy="product")
    private List<LivraisonDTO> prodLivr;
        public List<LivraisonDTO> getProdLivr() {return prodLivr;}
        public void setProdLivr(List<LivraisonDTO> prodLivr) {this.prodLivr = prodLivr;}

    @ManyToMany(mappedBy="prodList")
    private List<EnvironnementDTO> envList;
        public List<EnvironnementDTO> getEnvList() {return envList;}
        public void setEnvList(List<EnvironnementDTO> envList) {this.envList = envList;}


    public ProductDTO() {
    }

    public ProductDTO(int id, String nom, String appli, String grpAppli) {
        this.id = id;
        this.nom = nom;
        this.application = appli;
        this.groupeApplication = grpAppli;
    }
}

and Environnment :

@Entity
@Table(name="environnement")
public class EnvironnementDTO {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    @Column(name="environnement_id")
    private int id;
        public int getId() {return id;}
        public void setId(int id) {this.id = id;}

    @Column(name="machine_alias")
    private String machineAlias;
        public String getMachineAlias() {return machineAlias;}
        public void setMachineAlias(String machineAlias) {this.machineAlias = machineAlias;}

    @Column(name="instance")
    private String instance;
        public String getInstance() {return instance;}
        public void setInstance(String instance) {this.instance = instance;}

    @Column(name="port")
    private String port;
        public String getPort() {return port;}
        public void setPort(String port) {this.port = port;}

    @OneToMany(mappedBy="environnement")
    private List<LivraisonDTO> livrEnv;
        public List<LivraisonDTO> getLivrEnv() {return livrEnv;}
        public void setLivrEnv(List<LivraisonDTO> livrEnv) {this.livrEnv = livrEnv;}

    @ManyToMany
    @JoinTable(name="lien_product_environnement", 
    joinColumns=@JoinColumn(name="environnement_id", referencedColumnName="environnement_id"),
    inverseJoinColumns=@JoinColumn(name="product_id",referencedColumnName="product_id"))
    private List<ProductDTO> prodList;
        public List<ProductDTO> getProdList() {return prodList;}
        public void setProdList(List<ProductDTO> prodList) {this.prodList = prodList;}


    public EnvironnementDTO() {
    }

    public EnvironnementDTO(int id, String machineAlias, String instance, String port) {
        this.id = id;
        this.machineAlias = machineAlias;
        this.instance = instance;
        this.port = port;
    }



}

here my JPQL query :

SELECT env FROM EnvironnementDTO env JOIN ProductDTO p WHERE p.id=2

The generated query on postgres is the following :

select environnem0_.environnement_id as environn1_3_, environnem0_.instance as instance2_3_, environnem0_.machine_alias as machine_3_3_, environnem0_.port as port4_3_ from environnement environnem0_ inner join product productdto1_ on where productdto1_.product_id=2

as you can see : the sql executed on postgres dot not follow the mapping table for many to many designated in @JoinTable on EnvironnementDTO..

We double-checked our annotations, seems jpa or hibernate does not use them to generate the good query !

I'm aware it's certainly a mistake on my side... but don't understand what's happening.

Upvotes: 2

Views: 237

Answers (1)

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23552

You have to mention the association on which you want to join in the query

SELECT env FROM EnvironnementDTO env JOIN env.prodList p WHERE p.id=2

Upvotes: 2

Related Questions