Hibernate JOIN [some entity] ON unexpected token

user_7980462 0
Viewed 106

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).

spring-data hibernate java
2 Answers

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.

user_23108110

In additional to the @Philipp answer: the best way to execute such a join from Hibernate is to add the bidirectional @OneToMany dependency to the OrganizationEntity, like this:

 @OneToMany(mappedBy = UserEntity_.ORGANIZATION, fetch = FetchType.LAZY)
 private List<UserEntity> organizationUsers;

And then you can easily write:

   @Query("select o.gln, u.email from OrganizationEntity o "
            + "join o.organizationUsers u "
            + "where o.pan in (:pans)")
    List<Object[]> findQuery(List<String> pans);
user_79804620
Related