使用 Spring Data JPA 本机查询在两个日期之间搜索记录? [英] Search records between two dates using Spring Data JPA native query?

问题描述

我正在尝试使用 Spring Data Jpa 创建一个端点，以使用户/客户端能够使用我表中的日期列查找记录的详细信息，但是正在获取的记录只是结束日期之前的记录.

例如，我想要 18 日到 20 日之间的记录，但提取的记录是 18 日和 20 日之间的记录.即使表中存在该日期(20 日)的记录，19 日也会遗漏 20 日的记录.我需要一种方法来检索 18 日和 19 日的记录以及与结束日期有关的记录，在这种情况下为 20 日.任何帮助表示赞赏.

以下是我在 JPA 存储库中使用 JPA 原生查询尝试的方法.

在第一种方法中，我使用了本机查询 BETWEEN，但未获取结束日期的记录.这是下面的代码片段:

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

下面是我尝试过的另一种方法，它给了我与上述和以前的方法相同的结果.在这里，我使用了本机查询 GREATER THAN/EQUAL TO AND LESS THAN/EQUAL TO 但仍然没有得到想要的结果.这是存储库中的代码片段:

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

这是我的控制器的代码片段:

@PostMapping("/find/date-between")公共响应实体<对象>findPaymentByDate(@RequestBody DateSearcherDto searcherDto) {列表<PaymentMaster>paymentMasterList = paymentMasterRepository.getAllBetweenDates(searcherDto.getStartDate(), searcherDto.getEndDate());ListresultsDto = new ArrayList<>();}

更新

请查看我在下面创建的 DateSearcher DTO.

<预><代码>包 com.oasis.firsbacklogbackend.dto;导入 lombok.Data;导入 org.springframework.format.annotation.DateTimeFormat;导入 java.time.LocalDateTime;@数据公共类 DateSearcherDto {@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd")私人本地日期时间开始日期；@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = "yyyy-MM-dd")私人本地日期时间结束日期；}

请您的帮助将不胜感激.谢谢.

解决方案

如果你在做日期/时间比较，你必须把下限放在清晨，上限放在深夜.此外，更新您的查询以采用 LocalDateTime

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

为您服务:

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

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.

解决方案

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

这篇关于使用 Spring Data JPA 本机查询在两个日期之间搜索记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！