How to create a dyncamic update query with JPQL?

Question

I have to update an entity by ensuring it's already there in the database(Primary key is not auto generated).Therefor I cannot use only thesave() method to overcome this issue.There must be a way to ensure that the inserted primary key is already existing.To overcome this issue I know I can follow several approaches like below.But I found creating my own update statement is the most optimal way to solve this problem.

So I need to create a dynamic update query with JPQL . I know you will say why don't you use a method like getOne() or findOne() and set the necessary fields for the entity and save() .But I think using a findOne() or getOne() leads to an additional db hit to fetch the relevant entities .So I can omit the fetch by going with a custom update query.

I guess using @DynamicUpdate also won't resolve the problem ,because as far as I know it's also fetch the entity from the database to compare the changed fields during an updation.

So both of the above mentioned approaches leads to an additional db hit.

So is there a way to write a custom jpql query to update only the fields which are not null.

I have achieved similar kind of behaviour with fetching by writing dynamic where clauses.But didn't find a way to do the same with update.

Ex for dynamic select with JPQL:

SELECT  d FROM TAndC d WHERE (:termStatus is null or d.termStatus= :termStatus ) AND (:termType is null or d.termType=:termType) AND (:termVersion is null or d.termVersion=:termVersion)"

Gurkan İlleez Gurkan İlleez · Accepted Answer · 2021-05-21T12:27:51

@Modifying
@Query("update entity e set e.obj1=:obj1 and e.obj2=:obj2 where e.id=:id")
public void updateCustom(String obj1, String obj2, String id);

I wrote a pseudo code. It is possible with spring data.

Also you can use jpa entity manager to create update statement and dynamic query programatically

How to create a dyncamic update query with JPQL?

1 Answers