Inner join using HQL

Question

I am trying to inner join two tables on one column. From DB side, there's no mapping as it's something I don't want to discuss.

I want to execute HQL query using INNER JOIN and retrieve ROLE objects/results.

Here's my hql so far

session.createQuery("from ROLE as role INNER JOIN INVOLVEMENT as involvement ON role.id = involvement.roleid WHERE involvement.id = X").list();

I see ON is not available on HQL. how do i explicitly tell Hibernate to JOIN on this column only.

I tried below one too

select roleSpec from ROLE as role, INVOLVEMENT as involvement WHERE role.ID = involvement.role_id and involvement.id =27251352

But I am getting ROLE not mapped in exception.

Answer 1

Please check that your ROLE is indeed a mapped entity. In addition, you don't need to perform "ON" - hibernate knows what is the join column (I know how to define this at JPA ) - so no need to provide it at the statement. It should be -

session.createQuery("from Role as role INNER JOIN Involvement as involvement WHERE involvement.id = X").list();

I assume you have Role class mapped to ROLE table, and Involvement class mapped to Involement table.
Maybe you used table names by mistake, and this is why you get the "not mapped" error.
Last time I wrote HQL (and not JPA-QL) I used the following link as reference, it provides all the info needed.