如何在Spring JPA中检测同一候选人招聘记录的日期范围冲突？

问题描述 #

现有如下Recruitment实体类：

public class Recruitment implements Serializable {

    private static final long serialVersionUID = 1L;

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

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

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

    @Enumerated(EnumType.STRING)
    @Column(name = "status")
    private Status status;

    @ManyToOne
    private Candidate candidate;
}

需求：当为同一候选人新增/更新招聘记录时，若新记录的日期范围与该候选人已有记录的日期范围重叠（例如已有记录为2023-01-01至2023-01-31，新记录的startDate落在该区间内），则抛出异常。

解决方案 #

核心逻辑是判断同一候选人下是否存在满足「已有记录的开始时间 < 新记录的结束时间，且已有记录的结束时间 > 新记录的开始时间」的记录，只要满足该条件，两个日期范围就存在重叠。

1. 定义Repository层查询方法 #

通过Spring Data JPA实现冲突检测的查询，有两种方式：

方式一：方法命名自动生成查询

利用Spring Data JPA的方法命名规则，自动生成SQL：

public interface RecruitmentRepository extends JpaRepository<Recruitment, Long> {
    // 检测新增记录的冲突：同一候选人下，存在记录的startDate < 新记录endDate，且endDate > 新记录startDate
    boolean existsByCandidateAndStartDateBeforeAndEndDateAfter(Candidate candidate, Instant newEndDate, Instant newStartDate);

    // 检测更新记录的冲突：排除当前记录本身
    boolean existsByCandidateAndIdNotAndStartDateBeforeAndEndDateAfter(Candidate candidate, Long excludeId, Instant newEndDate, Instant newStartDate);
}

方式二：自定义JPQL查询

如果需要更灵活的逻辑，可编写JPQL查询：

public interface RecruitmentRepository extends JpaRepository<Recruitment, Long> {
    @Query("SELECT COUNT(r) > 0 FROM Recruitment r WHERE r.candidate = :candidate AND r.startDate < :newEndDate AND r.endDate > :newStartDate")
    boolean existsDateConflict(@Param("candidate") Candidate candidate, @Param("newStartDate") Instant newStartDate, @Param("newEndDate") Instant newEndDate);

    @Query("SELECT COUNT(r) > 0 FROM Recruitment r WHERE r.candidate = :candidate AND r.id != :excludeId AND r.startDate < :newEndDate AND r.endDate > :newStartDate")
    boolean existsDateConflictExcludeSelf(@Param("candidate") Candidate candidate, @Param("excludeId") Long excludeId, @Param("newStartDate") Instant newStartDate, @Param("newEndDate") Instant newEndDate);
}

2. Service层实现校验逻辑 #

在Service层调用Repository的查询方法，完成冲突检测，若存在冲突则抛出自定义异常：

步骤1：自定义异常类

public class DateRangeConflictException extends RuntimeException {
    public DateRangeConflictException(String message) {
        super(message);
    }
}

步骤2：Service层业务逻辑

@Service
public class RecruitmentService {
    private final RecruitmentRepository recruitmentRepository;

    // 构造方法注入Repository
    public RecruitmentService(RecruitmentRepository recruitmentRepository) {
        this.recruitmentRepository = recruitmentRepository;
    }

    public Recruitment saveRecruitment(Recruitment recruitment) {
        boolean hasConflict;

        if (recruitment.getId() == null) {
            // 新增记录：直接检测冲突
            hasConflict = recruitmentRepository.existsDateConflict(
                recruitment.getCandidate(),
                recruitment.getStartDate(),
                recruitment.getEndDate()
            );
        } else {
            // 更新记录：排除当前记录本身
            hasConflict = recruitmentRepository.existsDateConflictExcludeSelf(
                recruitment.getCandidate(),
                recruitment.getId(),
                recruitment.getStartDate(),
                recruitment.getEndDate()
            );
        }

        if (hasConflict) {
            throw new DateRangeConflictException("该候选人已有重叠的招聘记录，无法保存");
        }

        return recruitmentRepository.save(recruitment);
    }
}

3. 边界条件调整 #

如果业务需求中，日期边界重合不算重叠（例如新记录的startDate等于旧记录的endDate），保持现有<和>即可；如果需要将边界重合视为重叠，将查询条件改为<=和>=即可，例如：

@Query("SELECT COUNT(r) > 0 FROM Recruitment r WHERE r.candidate = :candidate AND r.startDate <= :newEndDate AND r.endDate >= :newStartDate")
boolean existsDateConflict(...)

内容的提问来源于stack exchange，提问作者kamrul Islam Tushar