Inner join with select on HQL

Question

I want to do something like that with HQL:

SELECT *
FROM tableA a
INNER JOIN (select fieldA, sum(fieldB) as sum from tableB) b
ON a.fieldA = b.fieldA and a.fieldC = b.sum;

But this gives an error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: (...

There is any way to make this using HQL and Hibernate?

Answer 1

You can try to do such thing with HQL:

String sqlText = 
        "select entityA 
         from EntityA entityA, EntityB entityB 
         where entityA.fieldA=entityB.fieldA 
         and entityA.fieldC=(select sum(entityB.fieldB) 
                             from EntityB entityB 
                             where entityB.fieldA=entityA.fieldA)"

Query query = session.createQuery(sqlText);

It should work similar to your sql. About your statement - as I know you cannot use inner view in HQL because it is object oriented.

Here is a good article about joins in HQL.

EDIT:

According to notes from user1495181 above query can be rewritten like (but I'm not sure):

String sqlText = 
        "select entityA 
         from EntityA entityA
         join entityA.entitiesB entityB
         Where entityA.fieldC=(select sum(entityB.fieldB) 
                             from EntityB entityB 
                             where entityB.fieldA=entityA.fieldA)"

But I prefer first variant because as for me it is more understandable (especially for peoples who used to work with native SQL).

Answer 2

try the native SQL solution approach:

need toimport this first:

import org.hibernate.SQLQuery;

then somewhere in your code:

SQLQuery query = session.createSQLQuery(
    "SELECT * FROM tableA a
    INNER JOIN 
    (SELECT fieldA, sum(fieldB) as sum from tableB) b
    ON a.fieldA = b.fieldA and a.fieldC = b.sum"
);

more on this link
and HERE ( Joins in Hibernate Query Language)

Answer 3

You cannot define the join with on keyword. Hibernate know how to do the join based on your mapping. If you define a relation in the mapping between a and b than hibernate will do the join based on the relation that you defined. If you have relation between a and b than do inner join without using on and put the join criteria in the where clause