Hibernate JOIN [some entity] ON unexpected token-CodePudding

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

@Query("select new by.urspectr.ediprov.util.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.

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

CodePudding user response：

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 by.urspectr.ediprov.util.dto.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.

CodePudding user response：

You should not use explicit JOIN ON in HQL. Instead you can use Implicit joining in HQL:

@Query("SELECT new by.urspectr.ediprov.util.dto.TwoFieldDto(o.gln, u.email) "
              "FROM OrganizationEntity o "
              "INNER JOIN UserEntity u "
              "WHERE o.gln in (:glnList)")