Hibernate JOIN [some entity] ON unexpected token

Question

I use Hibernate 5.4.32.Final, when I try to execute this query via spring data:

@Query("select new some.package.dto.TwoFieldDto(o.gln, u.email) "
            + "from OrganizationEntity o "
            + "join UserEntity u on u.organization.id = o.id "
            + "where o.gln in (:glnList)")
    List<TwoFieldDto<String, String>> findEmailListByGlnIn(List<String> glnList);

On starting application Hibernate throw SyntexException when it reaches to UserEntity token, and gives out unexpected token.

If I try to execute query from the UserEntity side, query compiles successfully. (Yes, I haven't get a link from OrganizationEntity to UserEntity)

Is this Hibernate version not supported JOIN ON syntex? (OrganizationEntity doesn't contain link on UserEntity, but UserEntity has it).

Answer 1

The @Queryannotation expects JPQL by default which has its own syntax. As far as I know, you can not do someting like JOIN .. ON in JPQL . I do not know the association between your entities but it should look someting like this:

@Query("select new some.package.TwoFieldDto(o.gln, u.email) "
            + "from OrganizationEntity o "
            + "join o.UserEntity u "
            + "where o.gln in (:glnList)")

But for that to work, there has to be an association between OrganizationEntity and UserEntity.

As an alternative you can always use a native query, where you could do a JOIN .. ON in your native SQL dialect:

@Query(value = "SELECT ....", nativeQuery = true)

But this should only be an option if you are sure that you can not do it with JPQL.