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?
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With