org.hibernate.queryexception unexpected token with [

Question

We are using hibernate in our project. We have a query which requires a left outer join. The query is as follows :

How to know i am using JPQL or HQL while writing queries.

I have a scenario where i have to implement left outer join in the query.

So i have written 'with' along with left outer join keywords.

But i am getting following exception :

org.hibernate.queryexception unexpected token with [

I read that with is supported with HQL but not with JPQL. So I have 2 questions :

1) how to identify whether JPQL or HQL and which version is used in my project? 2) if 'with' is not supported in JPQL, then how to implement left outer join between two tables with a specific value of a property.

The query is as follows :

select v.vId,v.vName,cur.code,con.dxId from
vanTb v, regionTb r, currencyTb cur, connTb con 
where 
v.vNum = r.vNum 
and v.vCode = r.vCode
and v.vId = cur.vId 
and v.vId *= con.vId
and con.dlId = 1234 and v.status=1

*= -> left outer join in sybase ASE database.

And the named-query is as :

select v.vId,v.vName,cur.code,con.dxId from vanTb v inner join v.regionTb r inner join 
        v.currencyTb left outer join v.connTb con with con.dlId = :DlId where vendor.status = 1

Here regionTb, currencyTb and connTb and vanTb are name of entity beans and vanTb.java have regionTb, currencyTb and connTb as memeber variables.

Atleast post your query/code...so we can tell how to proceed. — Akshay

Naros Naros · Accepted Answer · 2017-02-14T13:24:28

A good rule of thumb typically is that if your application is creating an EntityManagerFactory and you're using an EntityManager to issue queries, you would be creating JPQL based queries. If your application was creating a SessionFactory and you're using a Session to issue queries, you typically would be providing HQL.

That said, JPA 2.1 per the documentation here, added support for what you're asking with the ON clause. So in order to apply such a condition in JPQL, you would write:

String sql = "SELECT e FROM Employee e LEFT JOIN e.address a ON a.city = :city"
Query<MyEntity> query = entityManager.createQuery( sql, MyEntity.class );
query.setParameter( "city", "New York" );
List<MyEntity> results = query.getResulList();

The HQL grammer does support the use of the WITH clause however as follows:

String sql = "SELECT e FROM Employee e LEFT JOIN e.address a WITH a.city = :city"
Query query = session.createQuery( sql );
query.setParameter( "city", "New York" );
List results = query.getResultList();

org.hibernate.queryexception unexpected token with [

1 Answers