How to pass Hashmap into spring data jpa method and use that in update statement?

user_1180313 0
Viewed 1280

I would like to update multiple rows of a table using Case clause in the update query.

I have an Map<String, String> which contains values of 2 columns. Key acts as the identifier of the row and the value in the map is the value of the column I want to update.

How can I do this in a spring data JPA @Query?

I would like to achieve something like

@Modifying

@Query("update RequestDetail rd set value = VALUE(:statusDetails) where name='Status' and RequestUuid=KEY(:statusDetails)")
void updateBulkStatus(Map<String, String> statusDetails);

But this is giving the exception - antlr.SemanticException: node did not reference a map.

Goal is to avoid multiple update queries to DB.

What better ways we have to update multiple rows with multiple values to a single column.

spring-data-jpa spring-boot jpql java
1 Answers

You will need to execute something like:

Update RequestDetail rd set rd.value = CASE WHEN (rd.name = :name1) THEN :value1 WHEN (rd.name = :name2) THEN :value2 WHEN (rd.name = :name3) THEN :value3 END

But you will not necessarily know how many items will be in the map, so you will need to generate your query text, like

String sql = "Update RequestDetail rd set CASE ";
int index = 1;
for (Map.Entry<String,String> entry : statusDetails.entrySet()) {
    sql += " WHEN (rd.name = :name" + index + ") THEN :value" + (index++) + " ";
}
sql += " END";

You will also need to pass the parameters to your query.

user_4365600
Related