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.

TchiYuan · Accepted Answer · 2012-09-14T14:18:34.160

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.

should not be necessary as Spring Data automatically applies annotation based transactions to the repository interfaces. — Oliver Drotbohm, Mar 29 '13 at 15:23
also make sure the annotation is imported from org.springframework.transaction.annotation not javax — barryku, Sep 23 '14 at 22:04

score 10 · Answer 2 · answered Apr 19 '12 at 07:44

10

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

answered Apr 19 '12 at 07:44

Oliver Drotbohm

80,157
18
225
211

So… did that work for you or not? I am a bit puzzled what you're saying ;) – Oliver Drotbohm Apr 22 '12 at 09:08
1

sorry oliver, was bit tired and I made a typo. No it did not help. Form what I understand is that this option "@Modifying" is useful if your repository is annotated with read only. In that way you can have I repository that is mainly read only but, it still allows you to have write methods in it. – aki Apr 25 '12 at 15:12
This has got nothing to do with `readOnly` - at least not directly. `readOnly` is about transactions, `@Modifying` is about a call on the `EntityManager`. Of course it doesn't make sense to try to execute a modifying method in a read-only transaction. – Oliver Drotbohm Apr 26 '12 at 07:52

score 8 · Answer 3 · answered May 27 '15 at 00:54

8

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

answered May 27 '15 at 00:54

Tanja

81
1
1

score 4 · Answer 4 · edited Apr 08 '15 at 11:59

4

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

edited Apr 08 '15 at 11:59

Celtic

56
5

answered Sep 22 '13 at 16:42

romu31

821
9
17

score 0 · Answer 5 · answered Apr 10 '19 at 20:28

Working with Spring 5, @Transactional annotation is still required.

@Repository
public interface DoorStyleRepository extends JpaRepository<DoorStyle, Long> {

    @Modifying
    @Transactional
    @Query("UPDATE DoorStyle ds SET ds.enabled = false")
    void setEnabledFalse();
}

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

5 Answers5

Linked