Menu
I have three tables A B and C. Now i want to execute this sql query in HQL:
select * from A as a
left join
B as b
on
a.id = b.id
left join
C as c
on
b.type=c.type;
Need help in writing equivalent HQL. I tried with this HQL...
Query q = session.createQuery(
"FROM A as a
LEFT JOIN
B as b
on
a.id=b.id
LEFT JOIN
C as c
on
b.type=c.type");
This query is throwing exception .....
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: LEFT near line 1, column 23 [FROM com.admin.A as a LEFT JOIN B as b where a.Id=b.Id LEFT JOIN C as c where b.type=c.type] at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:74) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:214) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83) at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
I also tried with "with" and "on" clauses instead of where...I get the same unexpected token on "on" or "with"
exception qith ON .....
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ON near line 1, column 41 [FROM com.admin.A as a LEFT JOIN B as b on a.Id=b.Id LEFT JOIN C as c onb.type=c.type] at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:74) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:214) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83) at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
I also tried with "with" clauses instead of where...I get the same unexpected token on or "with"
exception qith WITH .....
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ON near line 1, column 41 [FROM com.admin.A as a LEFT JOIN B as b on a.Id=b.Id LEFT JOIN C as c onb.type=c.type] at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:74) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:214) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83) at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
Please help.
680
Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.
Query q = session. createQuery( "FROM A as a LEFT JOIN B as b on a.id=b.id LEFT JOIN C as c on b. type=c. type");
Some of the commonly supported clauses in HQL are: HQL From: HQL From is same as select clause in SQL, from Employee is same as select * from Employee . We can also create alias such as from Employee emp or from Employee as emp . HQL Join : HQL supports inner join, left outer join, right outer join and full join.
I suppose you've already defined all the needed associations in your configuration. If so, in HQL it would look like this:
from A as a left join a.B as b left join b.C as c
There is no "ON" statement in HQL, hibernate does automatically based on your mappings and defined Associations.
Pay attention to a.B and b.C.
You refer to B and C based on already defined aliases a & c.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
