Hibernate JOIN error syntax exception

Question

In my project (java struts 2, hibernate), I want to select with join between 2 table.

Class User {
  private int userId;
  private String userName;
  private String dateOfBirth;
}

Class Bill {
  private int billId;
  private String dateOfBill;
  private Double moneyOfBill;
  private User user;
}

It is OK when I tried via sql in localhost DB directly

Select * From user u JOIN bill b ON (b.userId=u.userId) Group by b.userId Order by Sum(b.moneyOfBill) asc;

But it is error in my program via hibernate hql

From User U JOIN Bill B ON (B.user.userId=U.userId) Group by B.user.userId Order by Sum(B.moneyOfBill) ASC;

The error in eclipse console:

at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:101) at org.hibernate.engine.query.HQLQueryPlan.(HQLQueryPlan.java:80) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94) at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156) at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135) at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)

Please help. Thank you!

This solution fixes my problem: stackoverflow.com/questions/16354705/… — Bentley
– Bentley, Commented Nov 28, 2014 at 7:30

Saharsh Shah · Accepted Answer · 2014-11-26 07:14:59Z

0

Try this:

SELECT U.userId, U.userName, U.dateOfBirth
FROM Bill B 
JOIN USER U 
GROUP BY U.userId, U.userName, U.dateOfBirth 
ORDER BY SUM(B.moneyOfBill) ASC;

edited Nov 26, 2014 at 7:14

answered Nov 26, 2014 at 4:53

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bentley Over a year ago

Thanks Saharsh Shah, but I just want to get user data from User table, not bill data.

Bentley Over a year ago

I found the solution is here stackoverflow.com/questions/16354705/….

Collectives™ on Stack Overflow

Hibernate JOIN error syntax exception

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related