使用JPQL关联集合谓词触发BadJpqlGrammarException问题排查

问题场景 #

原本在Hibernate 5环境下可正常执行的JPQL查询，切换到EclipseLink 4.0后触发语法错误，相关代码及报错信息如下：

报错的JPQL查询代码 #

@Query("SELECT distinct u FROM User u LEFT JOIN FETCH u.groups g where (LOWER(u.givenName) LIKE LOWER(:pattern) OR LOWER(u.familyName) LIKE LOWER(:pattern)) " +
        "AND g.groupName = :group AND u.userType = domainobjects.enumeration.UserType.T")
Slice<User> findByNamesSubstringInSameGroup(@Param("pattern") String pattern, @Param("group") String group, Pageable pageable);

触发的错误信息 #

org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:233 no viable alternative at input 'g.groupName=:group'; Bad JPQL grammar [SELECT distinct u FROM User u JOIN u.groups g where (LOWER(u.givenName) LIKE LOWER(:pattern) OR LOWER(u.familyName) LIKE LOWER(:pattern)) AND u.userType = domainobjects.enumeration.UserType.T and g.groupName = :group ]

相关实体类定义 #

@Entity
@Table(name = "TUSER")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User {

    @Id
    @GeneratedValue
    private Integer userId;

    @JsonIgnore
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
    @JoinTable(name = "User_LdapGroup",
            joinColumns = @JoinColumn(name = "userId"),
            inverseJoinColumns = @JoinColumn(name = "groupId"))
    private Set<LdapGroup> groups;

}

@Entity
public class LdapGroup {

    @Id
    @GeneratedValue
    private Integer groupId;

    private String groupName;

}

解决方法 #

将查询参数名从group重命名为groupName后，问题解决。修改后的代码示例：

@Query("SELECT distinct u FROM User u LEFT JOIN FETCH u.groups g where (LOWER(u.givenName) LIKE LOWER(:pattern) OR LOWER(u.familyName) LIKE LOWER(:pattern)) " +
        "AND g.groupName = :groupName AND u.userType = domainobjects.enumeration.UserType.T")
Slice<User> findByNamesSubstringInSameGroup(@Param("pattern") String pattern, @Param("groupName") String group, Pageable pageable);

内容的提问来源于stack exchange，提问作者nnl