Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Data JPA - How can I fetch data from a Date column using only month and day?

I have a simple Spring Data JPA project working with mysql and I need to fetch all registers that match the day and month. The column I need to filter is type Datetime.

1935-12-08 00:00:00

If I want to do this in a db level it works fine:

SELECT * FROM my_database.event where event_date LIKE '%-12-08%';

It treats the date as a string. Now I need to do this in my repository yet nothing seems to work. I tried the basic one:

List<Event> findByEventDateLike(
        @Param("eventDate") Date eventDate);

but it says it returns an illegal argument exception since it is a Date object. I tried other combinations but apparently spring data jpa can't compare dates with partial information.

NOTE: To keep it clean I'm trying to avoid @Query sentences but if it is the only way to go, it is a valid answer.

How can I do it?

like image 482
Matias Diez Avatar asked Dec 08 '17 17:12

Matias Diez


People also ask

How do I extract data between two dates in spring boot?

We can get the dates between two dates with single method call using the dedicated datesUntil method of a LocalDate class. The datesUntill returns the sequentially ordered Stream of dates starting from the date object whose method is called to the date given as method argument.

What does findById return in JPA?

Its findById method retrieves an entity by its id. The return value is Optional<T> . Optional<T> is a container object which may or may not contain a non-null value. If a value is present, isPresent returns true and get returns the value.

What is @query used for spring data?

Spring Data JPA @Query The @Query annotation declares finder queries directly on repository methods. While similar @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.


1 Answers

Use nativeQuery to emulate the query in the Database.

@Query(value = "SELECT * FROM events WHERE event_date Like %?1%", nativeQuery = true)
List<Match> findByMatchMonthAndMatchDay(@Param ("eventDate") String eventDate);

The DB takes care of the conversion between Date/String for the LIKE clause.

Passing the param as "-month-day" (e.g.: -12-08) will return a collection of events with that string in the date field.

like image 81
Matias Diez Avatar answered Sep 28 '22 06:09

Matias Diez