如何用Spring Specification实现一对多关联查询及经验月数总和过滤

我正在关联seeker_job_application、seeker_profile和seeker_experience三个实体创建过滤器，需求是找出经验总月数大于或等于指定值（如20）的seeker_profile。由于一个seeker_profile对应多条seeker_experience记录，需要按profile分组并求和经验月数后再与指定值比较。请问能否通过Spring Specification实现该需求？如何校验求职者的经验总月数是否大于或等于指定值？

表间关系 #

• seeker_job_application与seeker_profile为一对一关系
• seeker_profile与seeker_experience为一对多关系

期望实现的SQL #

select r.sja_id,r.sp_id,r.name,r.company_name,r.total_month from (
select sja.id as sja_id , sp.id as sp_id , sp.`name`,se.company_name,sum(se.total_month) as total_month 
from seeker_job_application sja 
INNER JOIN seeker_profile sp on sp.id = sja.seeker_id
INNER JOIN seeker_experience se on se.seeker_id = sp.id
where job_id =1 group by sp.id ) as r where r.total_month > 20;

实体类代码 #

SeekerJobApplication.java #

@Entity
@Table(name = "seeker_job_application")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class SeekerJobApplication implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @NotNull
    @Column(name = "seeker_id", nullable = false)
    private Long seekerId;

    @NotNull
    @Column(name = "job_id", nullable = false)
    private Long jobId;

    @Column(name = "apply_date")
    private Instant applyDate;

    @Column(name = "profile_viewed")
    private Boolean profileViewed;

    @Column(name = "on_hold")
    private Boolean onHold;

    @Column(name = "interview_schedule")
    private Boolean interviewSchedule;

    @Column(name = "rejected")
    private Boolean rejected;

    @Column(name = "selected")
    private Boolean selected;

    @Column(name = "prefered_location_id")
    private Long preferedLocationId;

    @Column(name = "work_preference")
    private String workPreference;

    @Column(name = "resume_file_path")
    private String resumeFilePath;

    @Column(name = "status")
    private String status;

    @ManyToOne
    @JoinColumn(name="seeker_id",referencedColumnName = "id", insertable = false, updatable = false)
    private SeekerProfile seekerProfile;
}

SeekerProfile.java #

@Data
@Entity
@Table(name = "seeker_profile")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class SeekerProfile implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "mobile_number", nullable = false)
    private String mobileNumber;

    @Column(name = "password")
    private String password;

    @Column(name = "email", nullable = false)
    private String email;

    @Column(name = "house_number")
    private String houseNumber;

    @Column(name = "address_line_1")
    private String addressLine1;

    @Column(name = "address_line_2")
    private String addressLine2;

    @Column(name = "city")
    private String city;

    @Column(name = "postcode")
    private String postcode;

    @Column(name = "state")
    private String state;

    @Column(name = "country")
    private String country;

    @Column(name = "website")
    private String website;

    @Column(name = "linkedin")
    private String linkedin;

    @Column(name = "facebook")
    private String facebook;

    @Column(name = "gender")
    private String gender;

    @Column(name = "dob")
    private String dob;

    @Column(name = "resume")
    private String resume;

    @Column(name = "wfh")
    private String wfh;

    @Column(name = "profile_completed")
    private String profileCompleted;

    @OneToOne
    @JoinColumn(unique = true)
    private Location preferedLocation;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "seeker_skill", joinColumns = { @JoinColumn(name = "seeker_id") }, inverseJoinColumns = { @JoinColumn(name = "skill_id") })
    private Set<Skill> skills;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name="seeker_id",referencedColumnName = "id", insertable = false, updatable = false)
    private Set<SeekerExperience> seekerExperiences;

    @OneToMany
    @JoinColumn(name="seeker_id",referencedColumnName = "id", insertable = false, updatable = false)
    private Set<SeekerEducation> seekerEducation;
}

SeekerExperience.java #

@Entity
@Table(name = "seeker_experience")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class SeekerExperience implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @NotNull
    @Column(name = "seeker_id", nullable = false)
    private Long seekerId;

    @NotNull
    @Column(name = "job_title", nullable = false)
    private String jobTitle;

    @NotNull
    @Column(name = "company_name", nullable = false)
    private String companyName;

    @NotNull
    @Column(name = "start_date", nullable = false)
    private String startDate;

    @NotNull
    @Column(name = "end_date", nullable = false)
    private String endDate;

    @Column(name = "total_month")
    private Integer totalMonth;

    @Column(name = "location")
    private String location;

    @Column(name = "role_description")
    private String roleDescription;
}

可以通过Spring Specification实现该需求，核心思路是利用JPA Criteria API的分组（GROUP BY）和聚合函数（SUM），再结合筛选条件过滤符合要求的分组。以下是具体实现步骤：

1. 编写Specification逻辑 #

针对SeekerJobApplication实体编写Specification，完成关联查询、分组求和与条件校验：

import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.*;

public class SeekerJobApplicationSpecifications {

    public static Specification<SeekerJobApplication> hasMinTotalExperience(Integer minTotalMonths, Long jobId) {
        return (root, query, criteriaBuilder) -> {
            // 关联SeekerProfile
            Join<SeekerJobApplication, SeekerProfile> profileJoin = root.join("seekerProfile", JoinType.INNER);
            // 关联SeekerExperience
            Join<SeekerProfile, SeekerExperience> experienceJoin = profileJoin.join("seekerExperiences", JoinType.INNER);
            
            // 按SeekerProfile的ID分组
            query.groupBy(profileJoin.get("id"));
            
            // 计算经验总月数
            Expression<Integer> totalExperience = criteriaBuilder.sum(experienceJoin.get("totalMonth"));
            
            // 构建筛选条件：匹配jobId + 总经验≥指定值
            Predicate jobMatch = criteriaBuilder.equal(root.get("jobId"), jobId);
            Predicate experienceMeet = criteriaBuilder.ge(totalExperience, minTotalMonths);
            
            return criteriaBuilder.and(jobMatch, experienceMeet);
        };
    }
}

2. 让Repository支持Specification查询 #

确保你的SeekerJobApplicationRepository继承JpaSpecificationExecutor接口：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface SeekerJobApplicationRepository extends JpaRepository<SeekerJobApplication, Long>, JpaSpecificationExecutor<SeekerJobApplication> {
}

3. 业务层调用查询 #

在业务逻辑中使用该Specification获取符合条件的结果：

@Service
public class SeekerApplicationService {

    @Autowired
    private SeekerJobApplicationRepository repository;

    public List<SeekerJobApplication> findQualifiedCandidates(Integer minMonths, Long jobId) {
        Specification<SeekerJobApplication> spec = SeekerJobApplicationSpecifications.hasMinTotalExperience(minMonths, jobId);
        return repository.findAll(spec);
    }
}

额外说明 #

• 上述代码生成的SQL逻辑与你期望的完全一致：通过INNER JOIN关联三张表，按seeker_profile.id分组，求和total_month后筛选出符合经验要求且匹配jobId的记录。
• 如果需要返回自定义字段（如你SQL中的sja_id、sp_id、total_month），可以在Specification中指定查询字段，返回Tuple或自定义DTO：

// 在Specification的lambda中添加字段选择逻辑
query.select(criteriaBuilder.tuple(
    root.get("id").alias("sja_id"),
    profileJoin.get("id").alias("sp_id"),
    profileJoin.get("name").alias("name"),
    experienceJoin.get("companyName").alias("company_name"),
    totalExperience.alias("total_month")
));

调用时使用repository.findAll(spec, Tuple.class)获取结果，再转换为自定义DTO即可。

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