Update queries with JPA

Configuration

In order to use custom update queries with JPA, it’s not the same as the read queries as we need to add more configuration, especially the annotation @Modifying with the appropriate properties if we want to refresh the persistence context, e.g. to perform a status update against an id:

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("""
        UPDATE DevicePairingEntity dp
        SET dp.status = 'UNPAIRED'
        WHERE dp.deviceId = :deviceId
        """)
void unpair(@Param("deviceId") String deviceId);

Analysis

1st iteration

@Query("""
        UPDATE DevicePairingEntity dp
        SET dp.status = 'UNPAIRED'
        WHERE dp.deviceId = :deviceId
        """)
void unpair(@Param("deviceId") String deviceId);

When using the @Query with default configuration (nativeQuery = false), the integration test still fails with the same error:

Caused by: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.bioserenity.pairing.entity.DevicePairingEntity dp
SET dp.status = 'UNPAIRED'
WHERE dp.deviceId = :deviceId
]

2nd iteration

By adding the @Modifying, I can mitigate the issue, but the update is not performed.

@Modifying
@Query("""
        UPDATE DevicePairingEntity dp
        SET dp.status = 'UNPAIRED'
        WHERE dp.deviceId = :deviceId
        """)
void unpair(@Param("deviceId") String deviceId);

Even by configuring in nativeQuery = true, the integration test fails, i.e. the status of the device pairing is not updated, whereas when testing in e2e, the status is updated.

3rd iteration

It seems we need to add an additional configuration on @Modifying(clearAutomatically = true) so the integration test can passed.

@Modifying(clearAutomatically = true)
@Query("""
        UPDATE DevicePairingEntity dp
        SET dp.status = 'UNPAIRED'
        WHERE dp.deviceId = :deviceId
        """)
void unpair(@Param("deviceId") String deviceId);

See https://stackoverflow.com/a/20056058/3612053 for more information.

4th iteration

However, it may not be enough as it will drops all non-flushed changes still pending in the EntityManager. So we may also need to add the flushAutomatically = true property.

See https://stackoverflow.com/questions/32258857/spring-boot-data-jpa-modifying-update-query-refresh-persistence-context for more information.

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("""
        UPDATE DevicePairingEntity dp
        SET dp.status = 'UNPAIRED'
        WHERE dp.deviceId = :deviceId
        """)
void unpair(@Param("deviceId") String deviceId);