updating boolean value in spring data jpa using @Query, with hibernate

Question

I have spring-data and hibernate configured and running. I can save records using spring-data but for some reason I am not able to run query that will update all Boolean fields in a table.

I tried this:

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

I also tried this:

 @Query("update Content v set v.published = 0 where v.division = :division and v.section = :section") void unPublishContent(@Param("division") String division,                           @Param("section") String section); 

Parameters division and section are coming true but no change on the table.

p.s. I am also using mysql database.

Answer 1

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.

Answer 2

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