CODEWITHSUNDEEP
javaspring-bootspring-data-jpa
kingified
kingified

Reputation: 313

Search records between two dates using Spring Data JPA native query?

I am trying to create an endpoint with Spring Data Jpa to enable users/client to find details of records using the date column from my table, however the records that are being fetched are only records from before the end date.

For example, I want records from between 18th - 20th, but the records that are fetched are records from 18th & 19th leaving out the records for 20th even though records for that date (20th) exists in the table. I need a way to retrieve the records of 18th and 19th along with that pertaining to the end date which is 20th in this case. Any help is appreciated.

Below are the approaches I have tried in my JPA repository using the JPA native query.

In the first approach, I used the native query BETWEEN but the records from the end date are not being fetched. This is the code snippet below:

@Query(value = "SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED BETWEEN :startDate AND :endDate", nativeQuery = true)
List<PaymentMaster> getAllBetweenDates(@Param("startDate") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate startDate,
                                       @Param("endDate") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate endDate);

Below is another approach I tried that gave me the same results as described above and as the previous approach. Here I used the native query GREATER THAN/EQUAL TO AND LESS THAN/EQUAL TO but still didn't get the desired outcome. Here's the code snippet from the repository:

@Query(value = "SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED >= :startDate AND LAST_UPDATED <= :endDate", nativeQuery = true)
List<PaymentMaster> getAllBetweenDates(@Param("startDate") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate startDate,
                                       @Param("endDate") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate endDate);

Here is a code snippet from my controller:

@PostMapping("/find/date-between")
public ResponseEntity<Object> findPaymentByDate(@RequestBody DateSearcherDto searcherDto) {
    List<PaymentMaster> paymentMasterList = paymentMasterRepository.getAllBetweenDates(searcherDto.getStartDate(), searcherDto.getEndDate());
    List<PaymentMasterDto> resultsDto = new ArrayList<>();
}

UPDATE

Please check out my DateSearcher DTO whicd I created below.


package com.oasis.firsbacklogbackend.dto;

import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.time.LocalDateTime;

@Data
public class DateSearcherDto {

    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd")
    private LocalDateTime startDate;

    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd")
    private LocalDateTime endDate;

}

Please your help would be appreciated. Thanks.

Upvotes: 3

Views: 18947

Answers (2)

kingified
kingified

Reputation: 313

Based on the answers of @gtiwari333 I modified my code and was able to get the desired outcome of fetching all the records including that of the end date. Below are the modifications for the sake of future readers.

My modified repository

    @Query(value = "SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED >= :startDate AND LAST_UPDATED <= :endDate", nativeQuery = true)
    List<PaymentMaster> getAllBetweenDates(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

My modified controller

@PostMapping("/find/date-between")
    public ResponseEntity<Object> findPaymentByDate(@RequestBody DateSearcherDto searcherDto) {

        LocalDateTime start = LocalDateTime.of(LocalDate.from(searcherDto.getStartDate()), LocalTime.of(0, 0, 0));
        LocalDateTime end = LocalDateTime.of(LocalDate.from(searcherDto.getEndDate()), LocalTime.of(23, 59, 59));

        List<PaymentMaster> paymentMasterList =
                paymentMasterRepository.getAllBetweenDates(start, end);

My DateSearcher Dto modifications:

package com.oasis.firsbacklogbackend.dto;

import lombok.Data;

import java.time.LocalDate;

@Data
public class DateSearcherDto {

    private LocalDate startDate;

    private LocalDate endDate;

}

This was how I solved the problem. With these modifications, it worked like magic... Thanks.

Upvotes: 4

gtiwari333
gtiwari333

Reputation: 25146

If you are doing date/time comparison, you have to put the lower bound to early morning and upper bound to late night. Also, update your query to take LocalDateTime

@Query(value = "SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED >= :startDate AND LAST_UPDATED <= :endDate", nativeQuery = true)
List<PaymentMaster> getAllBetweenDates(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

In your service:

LocalDateTime start = LocalDateTime.of(searcherDto.getStartDate(), LocalTime.of(0, 0, 0));
LocalDateTime end = LocalDateTime.of(searcherDto.getEndDate(), LocalTime.of(23, 59, 59));

paymentMasterRepository.getAllBetweenDates(start, end);

Upvotes: 3

Related Questions