JPA Specifications实现问卷-问题-答案多层关联查询问题

原有questionnaireType过滤逻辑存在两个核心错误：

• 嵌套两层子查询属于冗余写法，且关联逻辑倒置：直接用外层查询的questionJoin做Questionnaire.questions的in判断，没有建立子查询内部实体和外层Answer的正确关联，生成的SQL语义完全错误。
• 子查询未做ID匹配，无法将外层查询关联的Question和子查询中的Questionnaire做关联，运行时要么抛出SQL语法异常，要么返回不符合预期的结果集。

不需要写复杂嵌套子查询，有两种可正常运行的实现方式：

方案1：连续Join实现（推荐，性能最优） #

顺着实体关联关系直接做连接查询，写法最简单，执行效率最高：

注意：需要先在Question实体中补充多对多反向关联字段，不需要修改数据库表结构

// Question实体类中添加如下反向关联
@ManyToMany(mappedBy = "questions", fetch = FetchType.LAZY)
private List<Questionnaire> questionnaires = new ArrayList<>();

对应Specification实现：

public static Specification<Answer> filter (final AnswerFilter filter) {
    return (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();
        Join<Answer, Question> questionJoin = root.join(Answer_.question, JoinType.INNER);

        if (filter.getValue() != null) {
            predicates.add(criteriaBuilder.equal(root.get(Answer_.value), filter.getValue()));
        }

        if (filter.getQuestionType() != null) {
            predicates.add(criteriaBuilder.equal(questionJoin.get(Question_.type), filter.getQuestionType()));
        }

        if (filter.getQuestionnaireType() != null) {
            // 直接从Question关联到Questionnaire，内连接自动过滤不匹配记录
            Join<Question, Questionnaire> questionnaireJoin = questionJoin.join(Question_.questionnaires, JoinType.INNER);
            predicates.add(criteriaBuilder.equal(questionnaireJoin.get(Questionnaire_.type), filter.getQuestionnaireType()));
        }

        // 多对多连接会产生重复行，加distinct去重
        query.distinct(true);
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

方案2：EXISTS子查询实现（无需修改实体） #

如果不想修改现有实体类，用单层EXISTS子查询即可实现正确过滤，不需要嵌套子查询：

public static Specification<Answer> filter (final AnswerFilter filter) {
    return (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();
        Join<Answer, Question> questionJoin = root.join(Answer_.question, JoinType.INNER);

        if (filter.getValue() != null) {
            predicates.add(criteriaBuilder.equal(root.get(Answer_.value), filter.getValue()));
        }

        if (filter.getQuestionType() != null) {
            predicates.add(criteriaBuilder.equal(questionJoin.get(Question_.type), filter.getQuestionType()));
        }

        if (filter.getQuestionnaireType() != null) {
            Subquery<Questionnaire> qnSubquery = query.subquery(Questionnaire.class);
            Root<Questionnaire> qnRoot = qnSubquery.from(Questionnaire.class);
            // 关联Questionnaire和Question的中间表
            SetJoin<Questionnaire, Question> qnQuestionJoin = qnRoot.join(Questionnaire_.questions, JoinType.INNER);
            
            qnSubquery.select(qnRoot).where(
                criteriaBuilder.equal(qnRoot.get(Questionnaire_.type), filter.getQuestionnaireType()),
                // 匹配当前Answer所属的QuestionID
                criteriaBuilder.equal(qnQuestionJoin.get(Question_.id), questionJoin.get(Question_.id))
            );
            predicates.add(criteriaBuilder.exists(qnSubquery));
        }

        query.distinct(true);
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

两种写法生成的SQL都能正确实现三个过滤条件的匹配，返回符合要求的Answer数据。

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