如何编写具有多个连接的 Spring Data JPA 规范? [英] How to write a Spring Data JPA Specification with multiple joins?

问题描述

我正在处理的项目是使用 JHipster 生成的，支持实体过滤，在幕后使用 Spring Data JPA 规范.

The project I'm working on was generated with JHipster with support for entity filtering, which uses Spring Data JPA Specifications under the hood.

模型如下(在JDL中):

The model is as follows (in JDL):

entity Student {
    name String
}

entity Course {
    name String
}

entity Enrollment {
}

entity Attendance {
    date LocalDate
}

relationship OneToMany {
    Student to Enrollment(student required),
    Course to Enrollment(course required),
    Enrollment to Attendance(enrollment required)
}

filter all
service all with serviceClass

JHipster 生成用于通过 enrollmentId 过滤 Attendance 的样板，但我想扩展它以便能够通过 studentId 和 courseId 也是如此.

JHipster generates boilerplate for filtering Attendance by enrollmentId, but I'd like to extend it to be able to filter by studentId and courseId as well.

那么，我如何实现一个可以执行如下查询的规范:

So, how can I implement a Specification that would perform a query like:

SELECT
  attendance.date, student.name as student, course.name as course
FROM attendance
JOIN enrollment
  ON enrollment.id = attendance.enrollment_id
JOIN student
  ON student.id = enrollment.student_id
  AND studend.id = 1
JOIN course
  ON course.id = enrollment.course_id
  AND course.id = 2;

推荐答案

Following @GaëlMarziou的建议，我已经实现了简单的方法来创建 Specification 以加入 Student 和 Course.

Following @GaëlMarziou's advice, I've implemented simple methods create the Specification<Attendance> for joining with Student and Course.

我将字段、getter 和 setter 添加到 AttendanceCriteria 类，并重新编译以更新 JPA 元模型:

I added the fields, getters and setters to AttendanceCriteria class, and recompiled to update the JPA metamodel:

private LongFilter studentId;

private LongFilter courseId;

public LongFilter getStudentId() {
    return studentId;
}

public void setStudentId(LongFilter studentId) {
    this.studentId = studentId;
}

public LongFilter getCourseId() {
    return courseId;
}

public void setCourseId(LongFilter courseId) {
    this.courseId = courseId;
}

这是来自 AttencanceQueryService 类的更新片段:

Here's the updated snippet from AttencanceQueryService class:

private Specification<Attendance> createSpecification(AttendanceCriteria criteria) {
    Specification<Attendance> specification = Specification.where(null);
    if (criteria != null) {
        if (criteria.getId() != null) {
            specification = specification.and(buildSpecification(criteria.getId(), Attendance_.id));
        }
        if (criteria.getDate() != null) {
            specification = specification.and(buildRangeSpecification(criteria.getDate(), Attendance_.date));
        }
        if (criteria.getEnrollmentId() != null) {
            specification = specification.and(buildReferringEntitySpecification(criteria.getEnrollmentId(), Attendance_.enrollment, Enrollment_.id));
        }
        if (criteria.getStudentId() != null) {
            specification = specification.and(buildJoinSpecification(criteria.getStudentId(), Attendance_.enrollment, Enrollment_.student, Student_.id));
        }
        if (criteria.getCourseId() != null) {
            specification = specification.and(buildJoinSpecification(criteria.getCourseId(), Attendance_.enrollment, Enrollment_.course, Course_.id));
        }
    }
    return specification;
}

private <REFERENCE, JOIN, FILTER extends Comparable<? super FILTER>> Specification<Attendance> buildJoinSpecification(RangeFilter<FILTER> filter, SingularAttribute<? super Attendance, REFERENCE> reference, SingularAttribute<REFERENCE, JOIN> joinField, SingularAttribute<JOIN, FILTER> valueField) {
    Specification<Attendance> result = Specification.where((Specification) null);
    if (filter.getEquals() != null) {
        result = this.equalsSpecification(reference, joinField, valueField, filter.getEquals());
    }
    if (filter.getIn() != null) {
        result = this.valueIn((SingularAttribute) reference, joinField, valueField, filter.getIn());
    }
    return result;
}

private <REFERENCE, JOIN, FILTER> Specification<Attendance> equalsSpecification(SingularAttribute<? super Attendance, REFERENCE> reference, SingularAttribute<REFERENCE, JOIN> joinField, SingularAttribute<JOIN, FILTER> idField, FILTER value) {
    return (root, query, builder) ->
        builder.equal(root.join(reference).join(joinField).get(idField), value);
}

private <REFERENCE, JOIN, FILTER> Specification<Attendance> valueIn(SingularAttribute<? super Attendance, REFERENCE> reference, SingularAttribute<REFERENCE, JOIN> joinField, SingularAttribute<JOIN, FILTER> valueField, Collection<FILTER> values) {
    return (root, query, builder) -> {
        CriteriaBuilder.In<FILTER> in = builder.in(root.join(reference).join(joinField).get(valueField));
        for (FILTER value : values) {
            in = in.value(value);
        }
        return in;
    };
}

我希望这对某人有所帮助，功劳归于 @GaëlMarziou 和 Blackdread/Yoann Caplain.

I hope this helps someone, and the credit goes to @GaëlMarziou and Blackdread/Yoann Caplain.

这篇关于如何编写具有多个连接的 Spring Data JPA 规范?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！