Update with Values in Spring JPA Native Query for Postgresql

Question

I am trying to achieve below using Spring JPA.

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2) // this will be dynamic
) as c(column_b, column_a) 
where c.column_b = t.column_b;

Is there a way to pass the values from spring jpa using native query?

Answer 1

I would suggest that you first serialize your dynamic data as text representing a JSON array of any length - or even an empty one - like this

[
 [1, "234"],
 [2, "345"]
]

and use this native query passing the above text as a single parameter:

@Query(
value = "update test as t set column_a = c.column_a
         from (
          select j[0]\\:\\:integer column_a, j[1]\\:\\:text column_b
          from jsonb_array_elements (?1\\:\\:jsonb) j
         ) c
         where c.column_b = t.column_b",
nativeQuery = true
)
void updateFunction(String dynamicJSONData);

Notes:

• In JPA the postfix cast operator :: is escaped as \\:\\:;
• Under PostgreSQL prior to PG14 use the arrow syntax (j->>0)\\:\\:integer instead of j[0]\\:\\:integer and j->>1 instead of j[1]\\:\\:text

Alternative

Serialization format:

[
 {"column_a":1, "column_b":234},
 {"column_a":2, "column_b":345}
]

Query:

update test as t set column_a = c.column_a
from (
  select column_a, column_b
  from jsonb_to_recordset(?1::jsonb) as (column_a integer, column_b text)
) c 
where c.column_b = t.column_b;

This approach - serialize complex dynamic data as JSON text then pass it as a single String parameter to a native query and let the query do the rest - is quite generic and works well in many different cases.