Syntax error in HQL Query "unexpected token"

Question

I am using a select query in hql. But i cannot use in my API.

Getting error as :

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

Can Someone tell me wat is the error in My HQL

Code for your reference:

Session session = SessionUtil.getSession();

Query query = session.createQuery("SELECT a.mobile, a.email, p.patientId FROM (SELECT l "
        + "from login l where email= :email and password= :password) a INNER JOIN patientprofile p ON a.loginId= p.loginId");
query.setParameter("email", email);
query.setParameter("password", password);
List<Login> logins = query.list();
session.close();
return logins;

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 44 [SELECT a.mobile, a.email, p.patientId FROM (SELECT l.LoginId, l.email, l.mobile from com.innovellent.hibernate.restapi.model.Login l where email= :email and password= :password) a INNER JOIN com.innovellent.hibernate.restapi.model.PatientProfile p ON a.loginId= p.loginId] — mehul
– mehul, Commented Sep 12, 2017 at 10:37

Youcef LAIDANI · Accepted Answer · 2017-09-12 10:45:39Z

1

I would like to use a native query instead, because both HQL and JPQL accept Subqueries just in SELECT, WHERE or HAVING clause so you can use :

Query query = session.createNativeQuery("SELECT a.mobile, a.email, p.patientId FROM "
        + "(SELECT * from login l where email= :email and password= :password) a "
        + "INNER JOIN patientprofile p ON a.loginId= p.loginId");
query.setParameter("email", email);
query.setParameter("password", password);

read more about this in JPQL documentation

Subqueries may be used in the WHERE or HAVING clause.

edited Sep 12, 2017 at 10:45

answered Sep 12, 2017 at 10:38

Youcef LAIDANI

60.2k21 gold badges111 silver badges178 bronze badges

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

Comments

gokhanbirincii · Accepted Answer · 2017-09-12 11:07:13Z

1

Note that HQL subqueries can occur only in the select or where clauses.

So you can use nativeQuery but I would recommend to use HQL whenever possible to avoid database portability hassles, and to take advantage of Hibernate's SQL generation and caching strategies. instead of native query you can use join method and link them.

Notice HQL works with persistent objects and their properties.HQL queries are translated by Hibernate into conventional SQL queries which in turns perform action on database.

answered Sep 12, 2017 at 11:07

gokhanbirincii

5851 gold badge6 silver badges16 bronze badges

Collectives™ on Stack Overflow

Syntax error in HQL Query "unexpected token"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related