如何使用 Spring Data JPA 按日期和时间进行搜索？

Question

我倾向于 Spring Data JPA，我有一个应用程序，我尝试按日期搜索，但它不起作用。

问题是当我尝试在数据库中搜索日期时。我有一个 MySQL 数据库，如果我直接在 MySQL Workbench 中搜索，则可以正常工作，但如果我尝试从我的应用程序中搜索，则无法正常工作。我什么也得不到。如果我尝试搜索其他内容，我会得到结果。因此，当我尝试搜索日期时，我确定存在问题。我不知道是什么问题。任何反馈将不胜感激。谢谢你！

更新

一开始，该应用程序运行良好。我可以按数据库中的日期搜索。之后，我添加了 Spring Security 和更多实体，之后我无法按数据库中的日期进行搜索，并且我没有触及搜索方法。这很奇怪。现在我有第一个版本的应用程序并且可以运行，第二个版本不起作用。并且这两个应用程序都针对同一个数据库实例。但问题仅在于当我尝试按日期搜索时，如果我按 departureCity 和 arrivalCity 进行搜索，它会完美运行，当我尝试按日期搜索时，我什么也得不到，列表是空的。

这是无法运行的应用程序版本。这是 Github 链接 -> https://github.com/eveningstar33/flightreservationapp

这是另一个完美运行的应用程序：https ://github.com/eveningstar33/flightreservation

实体：

抽象实体类：

package com.dgs.flightreservationapp.entities;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.MappedSuperclass;

@MappedSuperclass

public class AbstractEntity {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

    public Long getId() {

        return id;

    }

    public void setId(Long id) {

        this.id = id;

    }

}

Answer 1

问题发生是因为数据转换com.mysql.cj.jdbc.ClientPreparedStatement：选择 flight0_.id 作为 id1_0_，flight0_.arrival_city 作为 arrival_2_0_，flight0_.date_of_departure 作为 date_of_3_0_，flight0_.departure_city 作为 departur4_0_，flight0_.estimated_departure_time 作为 estimate5_0_，flight0_.flight_number 作为 flight_n6_ 0_, flight0_.operating_airlines作为 operatin7_0_ 来自航班 flight0_，其中 flight0_.departure_city='AUS' and flight0_.arrival_city='NYC' and flight0_.date_of_departure='2018-02-04 18:30:00.0'如您所见，日期未转换为正确的格式，因此我们推出了自己的转换器来解决此问题。flightreservationapp/converters/LocalDateAttributeConverter.javapackage com.dgs.flightreservationapp.converters;import javax.persistence.AttributeConverter;import javax.persistence.Converter;import java.time.LocalDate;@Converter(autoApply = true)public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, String> {&nbsp; &nbsp; @Override&nbsp; &nbsp; public String convertToDatabaseColumn(LocalDate locDate) {&nbsp; &nbsp; &nbsp; &nbsp; return locDate == null ? null : locDate.toString();&nbsp; &nbsp; }&nbsp; &nbsp; @Override&nbsp; &nbsp; public LocalDate convertToEntityAttribute(String sqlDate) {&nbsp; &nbsp; &nbsp; &nbsp; return sqlDate == null ? null : LocalDate.parse(sqlDate);&nbsp; &nbsp; }}添加此文件后，您将开始获得结果您还有其他选择。为 JPA 方法添加注解&nbsp; &nbsp; List<Flight> findByDepartureCityAndArrivalCityAndDateOfDeparture(String from, String to,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;@DateTimeFormat(iso= DateTimeFormat.ISO.DATE)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LocalDate departureDate);然后您需要确保将时区也设置为 UTCpublic class FlightReservationAppApplication {&nbsp; &nbsp; public static void main(String[] args) {&nbsp; &nbsp; &nbsp; &nbsp; SpringApplication.run(FlightReservationAppApplication.class, args);&nbsp; &nbsp; }&nbsp; &nbsp; @PostConstruct&nbsp; &nbsp; void init() {&nbsp; &nbsp; &nbsp; &nbsp; TimeZone.setDefault(TimeZone.getTimeZone("UTC"));&nbsp; &nbsp; }}另一种选择是在你的程序中使用更高版本的 hibernatepom.xml&nbsp; &nbsp; &nbsp; &nbsp; <dependency>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <groupId>org.hibernate</groupId>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <artifactId>hibernate-java8</artifactId>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <version>5.1.0.Final</version>&nbsp; &nbsp; &nbsp; &nbsp; </dependency>并设置时区public class FlightReservationAppApplication {&nbsp; &nbsp; public static void main(String[] args) {&nbsp; &nbsp; &nbsp; &nbsp; SpringApplication.run(FlightReservationAppApplication.class, args);&nbsp; &nbsp; }&nbsp; &nbsp; @PostConstruct&nbsp; &nbsp; void init() {&nbsp; &nbsp; &nbsp; &nbsp; TimeZone.setDefault(TimeZone.getTimeZone("UTC"));&nbsp; &nbsp; }}

Answer 2

我认为您需要在实体中注释日期字段@Temporal(TemporalType.DATE)喜欢@Entitypublic class Flight extends AbstractEntity {&nbsp; &nbsp; private String flightNumber;&nbsp; &nbsp; private String operatingAirlines;&nbsp; &nbsp; private String departureCity;&nbsp; &nbsp; private String arrivalCity;&nbsp; &nbsp; @Temporal(TemporalType.DATE)&nbsp; &nbsp; private Date dateOfDeparture;&nbsp; &nbsp; private Timestamp estimatedDepartureTime;}

Answer 3

在你的非工作存储库中，你有这个额外的配置application.properties：spring.datasource.url=jdbc:mysql://localhost:3306/reservation?useSSL=false&serverTimezone=UTC该部分serverTimezone=UTC导致您输入的日期和数据库中的日期不匹配=>错误的结果所以现在，要么将两者设置为相同的时区，要么避免在 Java 中使用 Local*** 类

Answer 4

MySqlDate类型不包含任何时间部分，因此Date您在实体上使用的类型Flight及其时间部分可能会破坏您的查询。尝试Date用LocalDateJava8 Date/Time API 替换，它也只包含没有任何时间信息的日期。private LocalDate dateOfDeparture;&@Param("dateOfDeparture") LocalDate departureDate最重要的是，您不需要@Query为这样一个简单的逻辑使用 a 。您可以使用方法名称作为查询，这是 Spring Data JPA 的一个很好的特性。public interface FlightRepository extends JpaRepository<Flight, Long>&nbsp; {&nbsp; &nbsp; List<Flight> findByDepartureCityAndArrivalCityAndDateOfDeparture(String from, String to, LocalDate departureDate);}并从您的控制器中调用它；@PostMapping("/processFlights")public String processFlights(..) {&nbsp; &nbsp; List<Flight> flights = flightRepository.findByDepartureCityAndArrivalCityAndDateOfDeparture(from, to, departureDate);&nbsp; &nbsp; modelMap.addAttribute("flights", flights);&nbsp; &nbsp; return "displayFlights";}虽然它看起来很糟糕，因为名字变得太长了。您可以添加一个默认方法来包装它以隐藏丑陋的命名。public interface FlightRepository extends JpaRepository<Flight, Long>&nbsp; {&nbsp; &nbsp; List<Flight> findByDepartureCityAndArrivalCityAndDateOfDeparture(String from, String to, LocalDate departureDate);&nbsp; &nbsp; // you can use this from your controller&nbsp; &nbsp; default List<Flight> findFlights(String from, String to, LocalDate departureDate) {&nbsp; &nbsp; &nbsp; &nbsp; return findByDepartureCityAndArrivalCityAndDateOfDeparture(from, to, departureDate);&nbsp; &nbsp; }}这样做将隐藏由于在其中使用@Query注释和硬编码 JPQL 而导致的复杂性和可能的错误，除非对于更高级的情况绝对必要。我在你的中添加了以下代码flightreservationapp；@SpringBootApplicationpublic class FlightReservationAppApplication {&nbsp; &nbsp; public static void main(String[] args) {&nbsp; &nbsp; &nbsp; &nbsp; ConfigurableApplicationContext run = SpringApplication.run(FlightReservationAppApplication.class, args);&nbsp; &nbsp; &nbsp; &nbsp; FlightRepository flightRepository = run.getBean(FlightRepository.class);&nbsp; &nbsp; &nbsp; &nbsp; Flight flight = new Flight();&nbsp; &nbsp; &nbsp; &nbsp; flight.setDepartureCity("AUS");&nbsp; &nbsp; &nbsp; &nbsp; flight.setArrivalCity("NYC");&nbsp; &nbsp; &nbsp; &nbsp; flight.setDateOfDeparture(LocalDate.of(2018, 1, 5));&nbsp; &nbsp; &nbsp; &nbsp; flightRepository.save(flight);&nbsp; &nbsp; &nbsp; &nbsp; List<Flight> list = flightRepository.findByDepartureCityAndArrivalCityAndDateOfDeparture("AUS", "NYC", LocalDate.of(2018, 1, 5));&nbsp; &nbsp; &nbsp; &nbsp; System.out.println(list.size());&nbsp; // prints out 1&nbsp; &nbsp; }}存储库有效，我无法运行其他端点等，但查询/模式等没有问题。