10

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.

Reddy
  • 16,522
  • 43
  • 127
  • 190
  • use SQL instead. HQL mostly works only when you have proper relationship and entity mapping. (You still can do Theta style join, but I don't really recommend doing so) – Adrian Shum Nov 16 '12 at 07:53
  • can you tell me how do i use Hibernate to execute SQL. The rest of the application uses Hibernate widely. – Reddy Nov 16 '12 at 08:01
  • Search for Native Query in Hibernate's reference – Adrian Shum Nov 16 '12 at 08:18

1 Answers1

17

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.

Yair Zaslavsky
  • 3,987
  • 2
  • 17
  • 25