How to find conflicting date range between two date columns in Spring JPA?

Question

I have this following entity

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;
}

If I have a record that a particular Recruitment record has startDate for a candidate suppose January 1st 2023 and endDate january 31st 2023.

if I want to add another recruitment record for the same candidate and if I select the start date between January 1st to January 31st I want to throw an exception.

How can I get the data between startDate and endDate Column date range?

score 0 · Answer 1 · answered Jan 24 '23 at 20:25

In case i understand you correctly, you can declare Spring Data JPA Repository, and corresponding method:

public interface RecruitmentRepository extends JpaRepository<Recruitment, Long> {
    @Query("select r from Recruitment r where r.startDate > ?1 and r.endDate < ?2")
    List<Recruitment> findBetween(Instant startDate,
                                  Instant endDate);
}

score 0 · Answer 2 · answered Jan 26 '23 at 06:57

Here's the solution that worked for me

 @Query("select r from Recruitment  as r where :startDate between r.startDate and r.endDate and r.candidate= :candidate")
    List<Recruitment> findAllByDateAndCandidate(@Param("startDate") Instant startDate,  @Param("candidate") Candidate candidate);

How to find conflicting date range between two date columns in Spring JPA?

2 Answers2