CODEWITHSUNDEEP
hibernatejoinhql
nimcap
nimcap

Reputation: 10523

Join without association in HQL

Lets say I have two tables(A, B) like:

A {id, a, c}
B {id, b, c}

I have also their entities. I want to write an HQL so that the result set will be like (where A.c = B.c):

(a1, b1, c1)
(a2, b2, c2)
(a3, b3, c3)
...

Since on clauses are not supported by hibernate I am stuck and I don't know how to write the query.

Upvotes: 41

Views: 46131

Answers (4)

user14393644
user14393644

Reputation: 31

From Hibernate +5.1 version Hibernate introduced Join queries similar to SQL joins between unrelated entities.

Ex. Select a,b From Table a join Table b on a.id=b.id where condition

Upvotes: 3

Noman Akhtar
Noman Akhtar

Reputation: 730

if both the table are related to each other via foreign key then you can try this:

let:

A {id(primary key), a, c} B {id(foreign key), b, c}

define a variable of type parent(A) with setter and getter in B class's pojo.after that you can write query in this way. let it 'AA'

select tbl_b.a,tbl_a.a,tbl_b.b,tbl_a.b,tbl_b.c,tbl_a.c from B tbl_b inner join tbl_b.AA tbl_b

Upvotes: 0

user2291385
user2291385

Reputation:

if you want to make outer join you can make sub query and manage OR operation ( it works for me ). it's make the query more complex and worst performant but works :-)

Upvotes: 2

Il-Bhima
Il-Bhima

Reputation: 10880

You have to use the cross join notation:

from A as table_a , B as table_b
where table_a.c = table_b.c

Of course there is no way to implement outer joins in this manner, so you might have some trouble if that's your case.

For the analogous case with criteria refer to: hibernate-criteria-joining-table-without-a-mapped-association

Upvotes: 53

Related Questions