HQL query join tables

Question

I am using hibernate to connect to my database for a project.

I would like to have a query that gets the products out of my database with the discription and name in a certain language. The parameter I have is the short name for the language, so first I would have to get the id of the language and then get the text in the required languages.

I have tried the following hql query, without success.

from Products as p
where p.productlanguages.languages.shortname like 'eng'

This is an image of the part of the database where the data should come from:

I have got the desired result with an sql query, but I can't seem to get it to work in hibernate. But I would prefer to do this in hql.

SELECT * FROM products p 
INNER JOIN productlanguage pl ON pl.Products_id = p.id 
WHERE pl.Languages_id = 
(
SELECT id FROM languages 
WHERE Shortname = 'eng'
);

Could anyone tell me how to build this hql query? Thank you.

Answer 1

Try below:

   from Products p INNER JOIN p.productlanguages pl
   where pl.languages.shortname ='eng'

I am assuming that you have mapped Product-Productlanguages relationship as OneToMany and Productlanguages-Langages relationship as ManyToOne as depicted in your E-R diagram.

EDIT: There seems to be a typo in Productlanguage mapping at line public Languages getLanguages() {barcode, remove the barcode in the end.

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="Languages_id", nullable=false, insertable=false, updatable=false)
public Languages getLanguages() {barcode

    return this.languages;
}