CODEWITHSUNDEEP
javaspringhibernatejpadatetime-format
Ben
Ben

Reputation: 215

Hibernate return 0 record when using with @DateTimeFormat annotation

I have problem when trying to display data to UI.

When I use this query in MySQL to get the Datetime date from my table. It does return the records I want

select flight0_.id as id1_0_, flight0_.arrival_city as arrival_2_0_, flight0_.date_of_departure as date_of_3_0_, flight0_.departure_city as departur4_0_, flight0_.estimated_departure_time as estimate5_0_, flight0_.flight_number as flight_n6_0_, flight0_.operating_airlines as operatin7_0_ from flight flight0_ where flight0_.departure_city='AUS' and flight0_.arrival_city='NYC' and flight0_.date_of_departure='02-05-2018'

This is the result: enter image description here

But when I use Spring to input the same data to the form and submit it, nothing returns. The input I use is the same.

enter image description here

This is the result: enter image description here

I specified the @DateTimeFormat annotation in my Controller Class, using pattern the same with what displayed in the database, which is "yyyy-MM-dd"

@RequestMapping("findFlights")
    public String findFlights(@RequestParam("from") String from, @RequestParam("to") String to,
            @RequestParam("departureDate") @DateTimeFormat(pattern = "yyyy-MM-dd") Date departureDate,
            ModelMap modelMap) {
        List<Flight> flights = repository.findFlights(from, to , departureDate);
        modelMap.addAttribute("flights", flights);
    
        return "displayFlights";
    }

Here is my Repository class:

public interface FlightRepository extends JpaRepository<Flight, Long> {
    @Query("select f from Flight f where f.departureCity=:departureCity and f.arrivalCity=:arrivalCity and f.dateOfDeparture=:dateOfDeparture")
    List<Flight> findFlights(@Param("departureCity") String from, @Param("arrivalCity") String to, @Param("dateOfDeparture") Date departureDate);
}

For testing, I tried to remove that annotation and stop querying from that column, it works as expected. So I'm pretty sure that the @DateTimeFormat annotation is not working at all. Or it works but somehow the Hibernate doesn't understand the pattern I defined.

enter image description here

I also tried to input the value displayed in the screen, which is 2018-02-05 08:00:00.0 but no lucks.

Can someone give me any ideas on this? Please help!

Upvotes: 1

Views: 492

Answers (2)

Ben
Ben

Reputation: 215

Thanks, I figured it out myself. I'm currently working in KL, Malaysia. So the default 'UTC' timezone is not match with MySQL Datetime format. (which still using SYSTEM time). I made the change in application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/reservation?useLegacyDatetimeCode=false&serverTimezone=Asia/Kuala_Lumpur

And everything works fine now.

Upvotes: 1

Anatoly Samoylenko
Anatoly Samoylenko

Reputation: 701

You write flight0_.date_of_departure='02-05-2018' in your MySQL query. But 2018-02-05 in your input form and pattern = "yyyy-MM-dd" in your Controller method. If this method gets 2018-02-05 then it "think" that 02 is a month.

I think that problem in the date format yyyy-MM-dd and yyyy-dd-MM. Besides we are talking about American Airlines :)

1 - Try to see the value of dates in debugger.

2 - Try unambiguous dates such as 2017-12-20 and maybe you will see an exception.

3 - Try f.dateOfDeparture>=:dateOfDeparture in your query (maybe problem in time, not in date).

Upvotes: 0

Related Questions