如何编写具有多个联接的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;

推荐答案

跟随 @GaëlMarziou的建议，我已经实现了一些简单的方法来创建Specification<Attendance>以便与Student和Course联接.

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

我将字段，获取器和设置器添加到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屋！