4

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?

Montolide
  • 773
  • 3
  • 12
  • 27

3 Answers3

7

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)

Slater Victoroff
  • 21,376
  • 21
  • 85
  • 144
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Yeah, native can be a solution, but I would love a HQL solution, there isn't any? :( – Montolide Aug 02 '12 at 12:25
  • 1
    Just tried the solution with the SQL, and what I didn't know is that you can set the result to an entity using `query.addEntity(TableA.class)`, or else the result would be an array of Objects. So this way works perfect to me. Thanks! – Montolide Aug 02 '12 at 14:15
4

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).

dimas
  • 6,033
  • 36
  • 29
  • If you have relation between A And B you can use join to join them and put just the condition in the where. – Avihai Marchiano Aug 02 '12 at 13:07
  • @user1495181 Thank you for editing). I missed to change and on where. – dimas Aug 02 '12 at 13:31
  • I like the way you rewrite the hql, but I'm not sure if it would work to my case (that is more complex than my example query :P). Using native SQL is better to me, in this case ;) – Montolide Aug 02 '12 at 14:16
1

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

Avihai Marchiano
  • 3,837
  • 3
  • 38
  • 55
  • Can you please elaborate more? – Dejell Aug 20 '13 at 16:21
  • Suppose that you have Manager(1:m)Employe. The relation between them is defined in the hibernate mapping (one-to-many), so when you want to query for manager that their employee has a salary bigger that 100 than you dont need to specify the relation in the query like you do in SQL query with the join ...on. With hibernate you query 'from manager m join m.employee e where e. salary > 100 (i didnt specify how to do the join . hibernate know it by the mapping). However if your join is not defined in the mapping (not good practice) than you can do explicit join in the where: – Avihai Marchiano Aug 21 '13 at 06:55