"Not supported for DML operations" with simple UPDATE query

Question

I'm getting the error Not supported for DML operations when I use the following HQL...

@Query("UPDATE WorkstationEntity w SET w.lastActivity = :timestamp WHERE w.uuid = :uuid")
void updateLastActivity(@Param("uuid") String uuid, @Param("timestamp") Timestamp timestamp);

What could be causing the issue? It doesn't seem to be a common error given the few results I've found in Google.

Look at the accepted solution for this http://stackoverflow.com/a/12426144/172306 looks similar. — fn., Jul 30 '13 at 16:15
+1 for "@Modifying" (from your own comment in the answer of Ajay Bhojak) Your should add this as the default answer, as this is supposedly a spring JPA Repository question, you were questioning — minni, Aug 10 '15 at 07:58

score 39 · Accepted Answer · edited Oct 16 '15 at 07:48

39

Check the post hibernate hql ERROR: Not supported for DML operations in the hibernate users forum.

Most likely you called

querySt.list();

for your UPDATE query. Instead you should call

querySt.executeUpdate();

edited Oct 16 '15 at 07:48

BetaRide

14,756
26
84
153

answered Jul 30 '13 at 09:36

Ajay Bhojak

1,161
9
17

2

I just solve the same problem by adding the Modifying annotation. One more hint: the function return type must be void, because I copied the function from a normal query function so the return type was List, then I have the same error even after I added Modifying annotation. – cn123h Aug 15 '15 at 14:48
@cn123h had the same problem, copy paste had the return type as List instead of void – Vishnoo Rath Jul 14 '17 at 07:30

score 9 · Answer 2 · edited Feb 01 '21 at 16:21

I was also having the same problem with annotations.After searching and doing some tricks I was able to solve it. There are some below steps which you need to verify while using DML operation with JPA.

Use anotation @Modifying(org.springframework.data.jpa.repository.Modifying) and @Transactional(org.springframework.transaction.annotation.Transactional) on required method.
Use void as return type of method.

e.g:-

@Modifying
@Query("UPDATE ProcedureDTO o SET o.isSelectedByUser =?1")
@Transactional
public void getListOfProcedureBasedOnSelection(Boolean isSelected);```

score 1 · Answer 3 · answered Mar 10 '21 at 23:18

I had exact same problem, in my case I had to only add @Modifying annotation. According to documentation:

Indicates a query method should be considered as modifying query as that changes the way it needs to be executed. This annotation is only considered if used on query methods defined through a Query annotation. It's not applied on custom implementation methods or queries derived from the method name as they already have control over the underlying data access APIs or specify if they are modifying by their name. Queries that require a @Modifying annotation include INSERT, UPDATE, DELETE, and DDL statements.

score 0 · Answer 4 · answered Aug 11 '15 at 12:43

0

The same happened to me because, being q an object of class Query, q.list() is not to be used for updates or deletes, but q.executeUpdate()

answered Aug 11 '15 at 12:43

Weslor

21,424
2
17
31

score 0 · Answer 5 · answered Nov 02 '17 at 01:38

Make sure your service class method which calls updateLastActivity has @Transactional(org.springframework.transaction.annotation.Transactional) annotation. and modify the repository method to below,

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
...
@Modifying
@Query("UPDATE WorkstationEntity w SET w.lastActivity = :timestamp WHERE w.uuid = :uuid")
void updateLastActivity(@Param("uuid") String uuid, @Param("timestamp") Timestamp timestamp);

For more insights please use this answer.

"Not supported for DML operations" with simple UPDATE query

5 Answers5

Linked