JPA HQL查询：如何检查集合内部元素的关联ID属性值

现有一个可正常运行的JPA HQL查询方法filter16Main，用于筛选SocialEvent实体，关联了其multiPropsValuesSet集合。需求是新增查询条件：检查关联的MultiPropValue实体的id是否存在于入参eventTypes列表中每个MultiPropValue对象的connectedValuesIds列表内。添加条件后启动应用时出现org.hibernate.hql.internal.ast.QuerySyntaxException语法异常，希望在尽量不改动原有查询结构的前提下，找到简单可行的实现方式。

原可用查询代码 #

@Query(value =  "SELECT DISTINCT e from SocialEvent e "
        + "join e.multiPropsValuesSet m "
        + "WHERE ( "
        + "((m in ?1   OR COALESCE(?1, NULL) is null )"
        +")"
        +" AND  (COALESCE(?2, NULL) is null  OR m in ?2 ) "
        + "AND  (COALESCE(?3, NULL) is null  OR m in ?3 ) "
        + " ) "
        + "AND "
        + "((e.fromAge BETWEEN ?4 AND ?5) "
        + "OR (e.toAge BETWEEN ?4 AND ?5)"
        + "OR (?4 BETWEEN e.fromAge AND e.toAge )) "
        + "AND (e.date BETWEEN ?6 AND ?7) "
        + "ORDER BY e.date ASC"   /*, nativeQuery=true - cause errror*/)
List<SocialEvent> filter16Main(
        List <MultiPropValue> eventTypes,
        List <MultiPropValue> areas, List <MultiPropValue> jewLvlKeep,
        int fromAge, int toAge,
        Date from , Date to);

尝试添加条件后的查询代码 #

@Query(value =  "SELECT DISTINCT e from SocialEvent e "
            + "join e.multiPropsValuesSet m "
            + "WHERE ( "
            + "((("
            + "(m in ?1   OR COALESCE(?1, NULL) is null )"
            + " OR "
            + "(m.id in ?1.connectedValuesIds ) "   //--->new condition.    
            +")"
            +" AND  (COALESCE(?2, NULL) is null  OR m in ?2 ) "
            + "AND  (COALESCE(?3, NULL) is null  OR m in ?3 ) "
            + " ) "
            + "AND "
            + "((e.fromAge BETWEEN ?4 AND ?5) "
            + "OR (e.toAge BETWEEN ?4 AND ?5)"
            + "OR (?4 BETWEEN e.fromAge AND e.toAge )) "
            + "AND (e.date BETWEEN ?6 AND ?7) "
            + "ORDER BY e.date ASC"   /*, nativeQuery=true - cause errror*/)
    List<SocialEvent> filter16Main(
            List <MultiPropValue> eventTypes,
            List <MultiPropValue> areas, List <MultiPropValue> jewLvlKeep,
            int fromAge, int toAge,
            Date from , Date to);

报错信息 #

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 1. near line 1, column 178 [SELECT DISTINCT e from com.lingar.SocialEvents.socialEvent.entities.SocialEvent e join e.multiPropsValuesSet m WHERE ( (((m in ?1   OR COALESCE(?1, NULL) is null ) OR (m.id in ?1.connectedValuesIds ) ) AND  (COALESCE(?2, NULL) is null  OR m in ?2 ) AND  (COALESCE(?3, NULL) is null  OR m in ?3 ) ) AND ((e.fromAge BETWEEN ?4 AND ?5) OR (e.toAge BETWEEN ?4 AND ?5)OR (?4 BETWEEN e.fromAge AND e.toAge )) AND (e.date BETWEEN ?6 AND ?7) ORDER BY e.date ASC]

相关实体代码 #

SocialEvent实体 #

public class SocialEvent {
    
    //...
    @ManyToMany
    @CollectionTable(name="social_events_multi_values")
    private Set<MultiPropValue> multiPropsValuesSet = new HashSet<MultiPropValue>();
}

TextPiece父类 #

@Data
@MappedSuperclass
public abstract class TextPiece {
    //...
    @ElementCollection
    @CollectionTable    
    private List<Long> connectedValuesIds = new ArrayList<>();
 }

MultiPropValue实体 #

public class MultiPropValue extends TextPiece{
    private @Id 
//...
}

HQL不支持直接通过?1.connectedValuesIds这种方式访问集合参数的嵌套属性，因为参数?1是一个List<MultiPropValue>，无法直接在HQL中遍历其元素的connectedValuesIds。以下两种方式可解决问题：

方式一：提前提取所有connectedValuesIds作为单独参数传入 #

修改方法参数，新增一个List<Long> eventTypeConnectedIds，在调用方法前从eventTypes中提取所有connectedValuesIds并合并成一个列表，再传入查询：

修改后的查询代码 #

@Query(value =  "SELECT DISTINCT e from SocialEvent e "
        + "join e.multiPropsValuesSet m "
        + "WHERE ( "
        + "((m in ?1 OR COALESCE(?1, NULL) is null )"
        + " OR (COALESCE(?4, NULL) is null OR m.id in ?4 ))"
        + " AND  (COALESCE(?2, NULL) is null OR m in ?2 ) "
        + "AND  (COALESCE(?3, NULL) is null OR m in ?3 ) "
        + " ) "
        + "AND "
        + "((e.fromAge BETWEEN ?5 AND ?6) "
        + "OR (e.toAge BETWEEN ?5 AND ?6)"
        + "OR (?5 BETWEEN e.fromAge AND e.toAge )) "
        + "AND (e.date BETWEEN ?7 AND ?8) "
        + "ORDER BY e.date ASC")
List<SocialEvent> filter16Main(
        List<MultiPropValue> eventTypes,
        List<MultiPropValue> areas, List<MultiPropValue> jewLvlKeep,
        List<Long> eventTypeConnectedIds,
        int fromAge, int toAge,
        Date from, Date to);

调用示例（伪代码） #

List<MultiPropValue> eventTypes = ...;
List<Long> connectedIds = eventTypes.stream()
    .flatMap(m -> m.getConnectedValuesIds().stream())
    .collect(Collectors.toList());
List<SocialEvent> result = filter16Main(eventTypes, areas, jewLvlKeep, connectedIds, fromAge, toAge, from, to);

这种方式改动最小，完全保留原有查询结构，HQL语法合法，兼容性强。

方式二：使用HQL的EXISTS子查询关联参数 #

如果不想新增参数，可使用HQL的EXISTS子查询（需Hibernate 5.1+支持集合参数遍历）：

修改后的查询代码 #

@Query(value =  "SELECT DISTINCT e from SocialEvent e "
        + "join e.multiPropsValuesSet m "
        + "WHERE ( "
        + "((m in ?1 OR COALESCE(?1, NULL) is null )"
        + " OR (EXISTS (SELECT 1 FROM ?1 et WHERE m.id MEMBER OF et.connectedValuesIds)))"
        + " AND  (COALESCE(?2, NULL) is null OR m in ?2 ) "
        + "AND  (COALESCE(?3, NULL) is null OR m in ?3 ) "
        + " ) "
        + "AND "
        + "((e.fromAge BETWEEN ?4 AND ?5) "
        + "OR (e.toAge BETWEEN ?4 AND ?5)"
        + "OR (?4 BETWEEN e.fromAge AND e.toAge )) "
        + "AND (e.date BETWEEN ?6 AND ?7) "
        + "ORDER BY e.date ASC")
List<SocialEvent> filter16Main(
        List<MultiPropValue> eventTypes,
        List<MultiPropValue> areas, List<MultiPropValue> jewLvlKeep,
        int fromAge, int toAge,
        Date from, Date to);

这种方式无需新增参数，但依赖Hibernate版本对集合参数子查询的支持，兼容性稍弱。

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