使用 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<>();}UPDATEPlease 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;@Datapublic 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 本机查询在两个日期之间搜索记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！