Search code examples
javaspringhibernatespring-data-jpajpql

converting mysql query into spring JPA query

I have the following mysql query that I want to convert to SPring JPA.

SELECT * FROM SERVER s WHERE s.COMPLETE_DT BETWEEN (DATE_SUB('2014-11-07 16:30:20', INTERVAL 20 MINUTE)) 
AND (DATE_SUB('2014-11-07 16:30:20', INTERVAL 10 MINUTE))

I am doing it the following way 

@Query("SELECT s FROM Server s WHERE s.completeDt BETWEEN (DATE_SUB(%?1%, INTERVAL 20 MINUTE)) AND (DATE_SUB(%?1%, INTERVAL 10 MINUTE)))
List<Server> findDate(Date dt);

but getting an error

"Interval" not recognized

Solution

  • With Spring Data you could use a query like :

    List<Server> findByCompleteDtBetween(final LocalDateTime startDate, final LocalDateTime endDate);

    And compute startDate and endDate in your service to fit the targeted interval.

    See more : https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation