JPA Repository: set field of most recent element that matches criteria

Question

I have a JpaRepository which has many elements that match the same myCriteria. I want to change the boolean value of the latest element matching myCriteria. This is my entity:

@Entity
@Table(name = "my_entity")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class MyEntity {
    private boolean myField;
    private String myCriteria;
    private LocalDateTime createdAt;
}

And this is my repository and query:

public interface MyRepository extends JpaRepository<MyEntity, UUID> {

    @Modifying
    @Query("update MyEntity myEntity set myEntity.myField = true where myEntity.myCriteria = :myCriteria order by myEntity.createdAt limit 1")
    void setAddressed(@Param("myCriteria") String myCriteria);

}

It appears that I am not using order by properly as I am getting the following error: expecting EOF, found 'order' near line 1. My question is how to construct this query properly?

Thank you for your time

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect — João Matos, Sep 18 '20 at 17:27

score 1 · Accepted Answer · answered Sep 19 '20 at 08:03

First of all I have to say that you can not use limit clause in the HQL/JPQL. See for example this question.

The second problem is that you use the UUID type for the primary key of your entity. Imagine for a moment that it would be a Long type. In this case you would be able to rewrite your query in this way:

public interface MyRepository extends JpaRepository<MyEntity, Long> {

  @Modifying
  @Query("update MyEntity m set m.myField = true where m.id = (select max(me.id) from MyEntity me where me.myCriteria = :myCriteria)")
  void setAddressed(@Param("myCriteria") String myCriteria);
}

But for your case I see only one way - a native query.

public interface MyRepository extends JpaRepository<MyEntity, UUID> {

  @Modifying
  @Query(value = "update my_entity set my_field = true where my_id = (select my_id from my_entity where my_criteria = :myCriteria order by my_created_at limit 1)", nativeQuery = true)
  void setAddressed(@Param("myCriteria") String myCriteria);
}

Thank you for your answer. Yesterday I ended up going for a native query similar to the one you presented - I will add an answer with the resulting query but your answer should be the accepted one as it also provides an alternative approach without native query — João Matos, Sep 19 '20 at 11:24

score 0 · Answer 2 · answered Sep 19 '20 at 18:32

To complement the accepted answer, here is the native query that did the trick:

@Modifying
@Query(value =
        "UPDATE my_entity SET my_field = true FROM "
      + "( SELECT * FROM my_entity WHERE my_criteria = :myCriteria ORDER BY created_at desc LIMIT 1 ) "
      + "AS subquery WHERE my_entity.my_criteria = subquery.my_criteria", nativeQuery = true)
void setAddressed(@Param("myCriteria") String myCriteria);

JPA Repository: set field of most recent element that matches criteria

2 Answers2