Hibernate HQL Left Join Query

Question

I have two tables which I need to be joined but there in no relation specified in the entity. Can I write something like

select uc.id, uc.name, mpn.name from UCR uc, MpnMapping mpn

I'm getting an error called Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token

http://stackoverflow.com/questions/18545390/how-to-perform-left-join-in-hibernate-query-language - Try this but This question has no accepted or upvoted answer. I post it before test it by myself. — Lasitha Benaragama, May 08 '14 at 08:46

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Yes, you can do it by specifing the join in the where clause. It must be something like this:

select a, b from A a, B b where a.joinColumn = b.joinColumn

You can see more info in the below links:

Joining two unrelated tables in hibernate

http://www.codewrecks.com/blog/index.php/2009/09/04/theta-join-in-hql-join-with-unrelated-entities/

edited May 23 '17 at 12:05

Community

1
1

answered May 08 '14 at 08:48

angel_navarro

1,757
10
11

@angel_navarro so how do I make it a left outer join. – Akhil K Nambiar May 08 '14 at 10:16
Sorry @AkhilKNambiar, I assumed It was an inner join. For outer join on unrelated entities I think you cannot use HQL, so you can use NativeSQL (http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/querysql.html) or indicate the relationship in your entities. – angel_navarro May 08 '14 at 10:40

score 0 · Answer 2 · answered May 08 '14 at 08:57

Yes you can use new operator and a DTO class to set the values..

Query

 select new com.example.UCRMNP(uc.id, uc.name, mpn.name) from UCR uc, MpnMapping mpn;

DTO class

First can create a DTO class and specify all the column variables, like this

class UCRMNP{
 int id;
 String name;
 String name1;
 public UCRMNP( int id, String name, String name1){
 this.id=id;
 this.name=name;
 this.name1=name1;
}
}

Now you can set the retrieved data to that specific DTO class, You dont want to annotate it with anything, just specify the constructor and execute the Query using new operator.

Hibernate HQL Left Join Query

2 Answers2