Join in @NamedQuery: Unexpected token ON

In Hibernate, I created a query using JOIN to join two tables. The query executes fine in Oracles SQL Developer. However, if I add it to a @NamedQuery, the server starts with this error:

Error in named query: loadFooByAnother: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ON near line 1, column xxx

My named query is:

SELECT foo FROM FooTable foo JOIN BarTable bar
  ON foo.something=bar.somethingId
    WHERE bar.anotherId=:another

Is it not possible to use JOIN .. ON syntax in Hibernate?

Solution

You need to use the with directive, if you use HQL:

SELECT foo 
FROM FooEntity foo 
JOIN foo.bar b with b.name = :name
WHERE foo.prop = :prop

This is for supplying a custom ON clause. From your example, judging from how you joined tables, I think you tried to execute a native SQL using a @NamedQuery.

If you want to run a native SQL query, you have to use @NamedNativeQuery instead.

If you want to use HQL, you need to use Entities and to join Entity associations (not tables).

If you use JPQL then the with directive has to be replaced by the on directive, but again, you need to navigate entity associations, meaning you have to map them first.