Spring Data JPA “ON DUPLICATE KEY UPDATE” with IdENTITY Columns

Introduction

Spring Data JPA provides an efficient way to interact with databases using its query methods and repositories. However, there are scenarios where you need to update a record in the database based on certain conditions, such as inserting a new record if it doesn’t exist or updating an existing one if it does. In this article, we will explore how to achieve this using Spring Data JPA’s “ON DUPLICATE KEY UPDATE” feature with identity columns.

The Problem

Consider an Account entity with an id and an amount. You want to add some value to the existing amount or create a new account if it doesn’t exist. You can do this using a basic approach with Spring Data JPA:

var account = dataJpaRepository.findById(id);
if (account == null) {
  account = new Account(id, someValue);
} else {
  account.set(amount + someValue);
}
dataJpaRepository.save(account);

This approach makes two database requests: one to retrieve the existing account and another to update or create it.

The Native Query Solution

To reduce the number of requests, you can use a native query with an “ON DUPLICATE KEY UPDATE” clause:

INSERT INTO account (id, amount) VALUES (:id, someValue)
ON DUPLICATE KEY UPDATE 
amount = account.amount + someValue

This is exactly what we want to achieve using Spring Data JPA and JPQL.

Attempting with JPQL

Unfortunately, it’s not possible to use @Query annotation with native queries like the one above. The native query syntax does not translate well to JPQL.

However, there are two alternative approaches you can try:

1. Using a custom query method

@Repository
public interface AccountRepo extends JpaRepository<Account, Long> {
  
  // First try
  @Query(value = "INSERT INTO account (id, amount) VALUES (:id, :someValue) ON DUPLICATE KEY UPDATE amount = account.amount + :someValue", nativeQuery = true)
  Long addAmount(@Param("id") Long id, @Param("someValue") Long amount);

  // Second try
  default Account addAmount(Long id, Long amount) {
    findById(id).map(a -> {a.amount += amount; return a;}).ifPresentOrElse(a -> save(a), () -> {return save(new Account(amount));})
  }

}

2. Using a custom query method and manually fetching the existing account

@Repository
public interface AccountRepo extends JpaRepository<Account, Long> {
  
  // First try
  @Query(value = "INSERT INTO account (id, amount) VALUES (:id, :someValue) ON DUPLICATE KEY UPDATE amount = account.amount + :someValue", nativeQuery = true)
  Long addAmount(@Param("id") Long id, @Param("someValue") Long amount);

  // Second try
  default Account addAmount(Long id, Long amount) {
    var existingAccount = findById(id).orElseThrow();
    existingAccount.set(amount);
    return save(existingAccount);
  }

}

Both of these approaches will make two database requests: one to execute the native query and another to retrieve or update the account.

Conclusion

While it’s not possible to use @Query annotation with native queries like the “ON DUPLICATE KEY UPDATE” clause, there are alternative approaches you can try using custom query methods. However, keep in mind that these approaches will still make two database requests, which might not be ideal for performance-critical applications.

To achieve the best performance and maintain a clean database schema, consider using an identity column to track changes made by Spring Data JPA. This way, you can use @Modifying annotation with @Query to execute updates in a single database request.

@Repository
public interface AccountRepo extends JpaRepository<Account, Long> {
  
  @Modifying
  @Query(value = "INSERT INTO account (id, amount) VALUES (:id, :someValue) ON DUPLICATE KEY UPDATE amount = account.amount + :someValue", nativeQuery = true)
  void addAmount(@Param("id") Long id, @Param("someValue") Long amount);
  
}

In this example, the @Modifying annotation indicates that the query is a modifying operation, and the native query syntax is used to execute the update in a single database request.

By using an identity column and custom query methods with @Modifying, you can achieve efficient updates while maintaining a clean database schema.

Last modified on 2023-08-05