converting mysql query into spring JPA query

Question

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

There are many jpql docs on the web. None will have date_sub, or interval. — user8558216
– user8558216, Commented Oct 19, 2017 at 15:57

teikitel · Accepted Answer · 2017-10-19 16:11:33Z

2

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

answered Oct 19, 2017 at 16:11

teikitel

6995 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting mysql query into spring JPA query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related