updating boolean value in spring data jpa using @Query, with hibernate
For me too, it worked with following annotations:
@Modifying
@Query("update User u set u.active=1 where a.id=?1")
@Transactional
void activeUser(Long id);
I'm using Spring 3.1 and Spring JPA Data. I was having a similar problem. I was constantly getting an error while trying to update multiple records in 1 query.
So, I had something like this.
@Query("UPDATE User u SET u.state = ?1 WHERE u.server.id = ?2")
public void updateAllUsers(long state, long serverid);
Error:
org.hibernate.hql.QueryExecutionRequestException: Not supported for DML operations
So, after googling for a while, I found out that you had to add @Modifying.
@Modifying
@Query("UPDATE User u SET u.state = ?1 WHERE u.server.id = ?2")
public void updateAllUsers(long state, long serverid);
But then I was getting the following error:
...
nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query;
nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
...
So, I figured my problem was now a transaction problem and I went back to google to research it and found out that you have to add @Transactional now. It appears that @Modifying also requires @Transactional.
@Modifying
@Transactional
@Query("UPDATE User u SET u.state = ?1 WHERE u.server.id = ?2")
public void updateAllUsers(long state, long serverid);
but then I got the following error:
No value for key [org.apache.commons.dbcp.BasicDataSource (...) ] bound to thread
Again I googled for a while and came to the conclusion that my configuration was wrong and it turned out to be true. I was missing some xml configs.
<beans:bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
<beans:property name="entityManagerFactory" ref="entityManagerFactory"/>
</beans:bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
It was long journey but I finally got it working. I hope this will help someone, trying to "pay it forward" as many others have helped me with their wonderful blogs, answers and comments.
For me it worked with following annotations :
@Modifying
@Query("update JsonContactImport x set x.isImported = true where x.id in :ids")
@Transactional
void updateImported(@Param("ids") List<Long> ids);
To execute modifying queries you need to annotate the method with an additional @Modifying
as outlined in the reference documentation like this:
@Modifying
@Query("update Content v set v.published = false where v.division = :division and v.section = :section")
void unPublishContent(@Param("division") String division,
@Param("section") String section);