CODEWITHSUNDEEP
javahibernateplayframeworkjpql
user393964
user393964

Reputation:

JPQL where in array query

I'm trying to update every record for which I have the id in my arraylist but I'm getting this error:

IllegalStateException occured : org.hibernate.hql.QueryExecutionRequestException: Not supported for DML operations [update models.UserOnline uo SET currentRoom_id = :roomid where uo.id IN (:list)]

This is what I'm trying:

    Query update_query = JPA.em().createQuery("update UserOnline uo SET currentRoom_id = :roomid where uo.id IN (:list)");
    update_query.setParameter("roomid", null);
    update_query.setParameter("list", idlist);

    List<UserOnline> actual = update_query.getResultList();

Any ideas what's wrong?

Upvotes: 0

Views: 2733

Answers (3)

Lyju I Edwinson
Lyju I Edwinson

Reputation: 1834

Well, if we use Spring Repositories (CrudRepository or any of its type), and if we have a method declaration with an update Query That is,

@Query("update employee e set e.name= :name where e.id = :id") int updateEmployee(@Param("name") String name, @Param("id") Long id);

Then we will get the related Spring Exception org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations

Just add @Modifying annotation on the method and it will be fine.

Upvotes: 0

Dominik Dorn
Dominik Dorn

Reputation: 1831

Like Gonzalo already said, you'd have to use executeUpdate().

This is because you're actually MODIFYing data .

You only use getResultList() or getSingleResult() if you want to GET data out of the database.

a little helper: use executeUpdate() if your query has the form

UPDATE ... SET .. WHERE ..
or
DELETE ... WHERE ...

use getResultList() or getSingleResult() if the query looks like

SELECT ... FROM xxx WHERE ... 
or just
FROM xxx WHERE ...

Upvotes: 1

Gonzalo Garcia Lasurtegui
Gonzalo Garcia Lasurtegui

Reputation: 4941

I would try with update_query.executeUpdate();

From the docs.

Upvotes: 1

Related Questions