How to update the JPA/Hibernate @Version field in a Spring Data JPA @Modifying @Query query?

Question

For testing the @Version annotation, I am using the following setup to manage persistence:

Spring Boot Starter 1.5.3.RELEASE
Hibernate 5.2.10.Final
Spring Data Envers 1.1.3.RELEASE -> Spring Data JPA 1.11.3.RELEASE

Databases tested:

H2
PostgreSQL
MariaDB
Oracle

The entities are utilizing an @Version annotated field which will be incremented on update. One example entity looks like this:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Version;

@Entity
@Table(name = "\"users\"")
public class User {

    @Id
    private Long id;

    private boolean active;

    @Version
    @Column(nullable = false)
    private long version = 0L;

    // getters/setters (not shown)

}

The Spring Data JPA based UserRepository provides custom modifying queries besides the CRUD ones:

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long> {

    @Modifying
    @Query("UPDATE User u SET u.active = false WHERE u.id = ?1")
    void deactivate(Long id);

    @Modifying
    @Query("UPDATE User u SET u.active = false WHERE u.id IN :ids")
    void deactivateAll(@Param("ids") Long... ids);

    @Modifying
    @Query("UPDATE User u SET u.active = false WHERE u.id IN :ids")
    void deactivateAll(@Param("ids") Iterable<Long> ids);

}

As the version field is not automatically increased for JPQL/HQL queries, this must be done manually by adjusting the custom queries accordingly.

When trying to use the Hibernate specific (non-JPA conforming) VERSIONED keyword (HQL only) to extend the query

@Modifying
@Query("UPDATE VERSIONED User u SET u.active = false WHERE u.id = ?1")
void deactivate(Long id);

the generated SQL is not valid indepentently of the database tested, resulting in an error looking something like this (schema name is TEST):

Table "USER0_" not found; SQL statement: update "TEST"."users" set user0_."version"=user0_."version"+1, "active"=0 where ("id") IN (select "id" from "HT_users") [42102-194]

So, how can I update the JPA/Hibernate @Version field in a Spring Data JPA @Modifying @Query query?

score 2 · Accepted Answer · edited May 23 '17 at 12:34

2

You can add u.version=u.version+1 to the query. The updated @Querys look like this:

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long> {

    @Modifying
    @Query("UPDATE User u SET u.version=u.version+1, u.active = false WHERE u.id = ?1")
    void deactivate(Long id);

    @Modifying
    @Query("UPDATE User u SET u.version=u.version+1, u.active = false WHERE u.id IN :ids")
    void deactivateAll(@Param("ids") Long... ids);

    @Modifying
    @Query("UPDATE User u SET u.version=u.version+1, u.active = false WHERE u.id IN :ids")
    void deactivateAll(@Param("ids") Iterable<Long> ids);

}

There may be caveats using this. E.g. this will obviously not fail with an OptimisticLockException like updates issued by the EntityManager do (as described here).

edited May 23 '17 at 12:34

Community

1
1

answered May 09 '17 at 15:09

AntiTiming

2,094
3
22
33

Try to use `@Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)` instead of incrementing version manually – Jay Smith May 09 '17 at 16:04
@JaySmith Thank you very much for the hint, but it seems to me that the Lock annotation cannot be combined with a modifying query like the ones from my example repository. At least I get a pretty self-speaking exception: "java.lang.IllegalStateException: Illegal attempt to set lock mode on a non-SELECT query" when simply adding it as is. The documentation and API docs do not help me here or I'm missing something. Do I have to take anything else into account? – AntiTiming May 09 '17 at 18:15
I created hql query with `versioned` keyword and it works ok. You get error `Table "USER0_"` not found` . Is it related to `versioned` keyword? In [hibernate docs](https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#locking-optimistic-version-number) it is said that you cannot modify `version` explicitly. – Jay Smith May 10 '17 at 03:53
@JaySmith did you use Query annotation for the HQL? What about the JPA settings, e.g. globally quoted identifiers. I will update my question and add some of my missing settings. And you're right, the hibernate docs say it is forbidden to alter the version manually. I will take a look at the Lock annotation again, maybe I'll find a hint there what to do to update the version properly. Actually that's why I asked the question as it is not obvious to me so far. Thanks again for your support! – AntiTiming May 10 '17 at 11:36
I didn't use spring data jpa. I created hql query with `session.createQuery` method. – Jay Smith May 10 '17 at 12:30
@JaySmith Ok, thank you for the update. I had no time to investigate further on the `VERSIONED` keyword. About the Lock annotation, I asked another SO question to clarify if this is possible at all or not, see here: https://stackoverflow.com/questions/43896128/ – AntiTiming May 10 '17 at 14:59

score 0 · Answer 2 · answered Dec 14 '17 at 15:00

0

As answered the similar question here - try to add VERSIONED keyword after the UPDATE keyword:

@Modifying
@Query("UPDATE VERSIONED User...)

answered Dec 14 '17 at 15:00

m52509791

449
5
13

How to update the JPA/Hibernate @Version field in a Spring Data JPA @Modifying @Query query?

2 Answers2