CODEWITHSUNDEEP
spring-data-jpah2jpql
Mehdi Benmesssaoud
Mehdi Benmesssaoud

Reputation: 534

Convert H2 sql query to JPQL

I'm using H2 database in developpement, i wrote a native query, that supports only H2. I want now to convert it to JPQL, so i can use it in production mode.

Inside the query i'm using the DATE_ADD function, which adds a value from the database to the current date, i have tried to search the equivalent for JPQL, but i wasn't successful.

@Modifying
@Transactional
@Query(
        value = "UPDATE ORDER_TABLE O SET O.STATE='CANCELED' WHERE O.STATE='PENDING' AND DATEADD('HOUR',SELECT P.VALUE FROM PARAMETER P WHERE P.NAME ='PENDING_ORDER_TTL' , O.CREATED_AT) < NOW()",
        nativeQuery = true)
void updatePendingOrder();

Upvotes: 0

Views: 423

Answers (1)

Alan Hay
Alan Hay

Reputation: 23246

You need to calculate the Date in Java:

@Query(value = "UPDATE Order o SET o.state='CANCELED'" + 
             " WHERE o.state='PENDING' AND o.createdAt < :cutOff")
@Modifying
//Date/LocalDate/LocalDateTime or whatever
public void updateOrders(@Param("cutOff") Date cutOff)

Upvotes: 1

Related Questions