CODEWITHSUNDEEP
javasqlhibernatespring-mvchql
Ben
Ben

Reputation: 62384

"Not supported for DML operations" with simple UPDATE query

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.

Upvotes: 38

Views: 40865

Answers (5)

elhose
elhose

Reputation: 348

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.

Upvotes: 2

srp
srp

Reputation: 753

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.

  1. Use anotation @Modifying(org.springframework.data.jpa.repository.Modifying) and @Transactional(org.springframework.transaction.annotation.Transactional) on required method.

  2. Use void as return type of method.

e.g:-

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

Upvotes: 16

tk_
tk_

Reputation: 17348

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.

Upvotes: 1

Ajay Bhojak
Ajay Bhojak

Reputation: 1159

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();

Upvotes: 38

Weslor
Weslor

Reputation: 22440

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()

Upvotes: 0

Related Questions