Spring Boot JPA update using composite primary keys

Question

I have events table with composite key on id, created_at. My SQL query is as below:

update events set status = true where (id, created_at) in ((1, '2024-06-17 12:00:44.674394+01'), (id2, time2), (id3, time3)...)

How to achieve this in Spring boot JPA? I tried below code:

@Modifying
@Transactional
@Query(
      value = """
          UPDATE events
          SET status = true 
          WHERE (id, created_at) IN (:pkValues)
          """,
      nativeQuery = true
  )
  int updateStatus(List<Object[]> pkValues);

But I got error:

[ERROR: operator does not exist: record = bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 87] [n/a].

Any help is highly appreciated.

Answer 1

• Create your Id as @Embeddable e.g. MyPkEmebddable
• Make use of your Id Type in your class using @EmbeddedId e.g. @EmbeddedId MyPkEmbeddable yourIdField;
• Make your JPQL Statement using the type

@Query(value = "UPDATE events SET status = true WHERE yourIdField IN (:pkValues)... )

int updateStatus(List<MyPkEmbeddable> pkValues)

JPA layers Objects around the tables, so a composite primary key is set up as an @EmbeddableId

see https://www.baeldung.com/spring-jpa-embedded-method-parameters

This should do the trick :-)