org.springframework.orm.hibernate4.HibernateQueryException: Not supported for DML operations [UPDATE]

Question

I am trying to update a record in the database and I getting this error

org.springframework.orm.hibernate4.HibernateQueryException: Not supported for DML operations [UPDATE com.xxx.models.User u set u.notifiable = true WHERE u.emailAccess = :emailAccess AND u.isAdmin = false]; nested exception is org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.xxx.models.User u set u.notifiable = true WHERE u.emailAccess = :emailAccess AND u.isAdmin = false]

This is my hql attempt

@Modifying
    public User updateUser(String emailAccess) {

        String hql = "UPDATE User u set u.notifiable = true WHERE u.emailAccess = :emailAccess AND u.isAdmin = false";
        return (User) _sessionFactory.getCurrentSession().createQuery(hql).setParameter("emailAccess", emailAccess).list();

    }

After researching, I added the @Modifying annotation to the top of the method but the error still persists. Please what could be wrong?

in your return statement I see **.list()** and you are returning just one user. Is it correct? — duardito, Oct 04 '16 at 18:04
use 'executeUpdate()' instead of 'list' same issue here: http://stackoverflow.com/questions/17622147/not-supported-for-dml-operations-with-simple-update-query — seiya, Oct 04 '16 at 18:07

Dmitry Smorzhok · Answer 1 · 2016-10-04T18:23:54.700

You should invoke executeUpdate() on your query when you're going to update or delete your entities.

list() method which you're using is to select entities by given query, not to update them.

Your method signature and return types are incorrect. You can never be sure that update operation will actually update only a particular user. executeUpdate() method returns number of rows affected by your query, not the updated entity.

Moreover even list() from your original answer won't return a particular user. It (in case of select query) will return a list of users according to your conditions.

Alan Hay · Accepted Answer · 2016-10-04T18:22:59.657

0

@Modifying is a Spring Data annotation and you do not appear to be using Spring Data so that is of no use to you.

You need to call executeUpdate() and make the return method void as executeUpdate

Returns: The number of entities updated or deleted..

https://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/Query.html#executeUpdate()

or return the result of a second query.

public void updateUser(String emailAccess) {

    String hql = "UPDATE User u set u.notifiable = true WHERE u.emailAccess = :emailAccess AND u.isAdmin = false";
    _sessionFactory.getCurrentSession().createQuery(hql).setParameter("emailAccess", emailAccess).executeUpdate();

}

edited Oct 04 '16 at 18:22

answered Oct 04 '16 at 18:09

Alan Hay

22,665
4
56
110

Cannot cast from int to User. In respect to your solution – Francis Oct 04 '16 at 18:12
As the API docs state executeUpdate() returns The number of entities updated or deleted so an int and not a User. – Alan Hay Oct 04 '16 at 18:15
What exactly is missing from it? – Alan Hay Oct 04 '16 at 18:18
public void updateUser(String emailAccess) { String hql = "UPDATE User u set u.notifiable = true WHERE u.emailAccess = :emailAccess AND u.isAdmin = false"; _sessionFactory.getCurrentSession().createQuery(hql).setParameter("emailAccess", emailAccess).executeUpdate(); } – Francis Oct 04 '16 at 18:19
@Francis your logic is wrong. Your method can't return a particular user anyway. If you're running `update` statement you do not know how many rows in DB will be updated – Dmitry Smorzhok Oct 04 '16 at 18:20
Yes, I agree with you but can i add a return statement to void – Francis Oct 04 '16 at 18:21
replace the void return type with int – Francis Oct 04 '16 at 18:32

org.springframework.orm.hibernate4.HibernateQueryException: Not supported for DML operations [UPDATE]

2 Answers2