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?

AntiTiming AntiTiming · Accepted Answer · 2017-05-09T15:09:35

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

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

2 Answers