Spring Data JPA "ON DUPLICATE KEY UPDATE amount = account.amount someValue"-CodePudding

Let's say I have next entity

@Entity
@Table(name = "account")
public class Account {

@Id
private long id;

private long amount;

}

I want to add some value to existing one and update it in DB or if this account is not existing yet I want to create new one.

Basic approach with Spring Data JPA is:

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

In this case two request to DB are done, but with native query I can do it just by sending one request like:

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

Is that possible to do the same thing by the means of Spring Data JPA/JPQL and reduce amount of requests ?

CodePudding user response：

You can do that like this:

@Modifying
@Query("update account m set m.amount =m.amount   ?2 where m.id=?1")
void updateAcco(Long id, Long someValue);

CodePudding user response：

Definitly not sure that will work but you can try tow things :

@Repository
public interface AccountRepo extends JpaRepository<Account, Long> {

  @Query(native = "INSERT INTO account (id, amount) VALUES (:id, :someValue) ON DUPLICATE KEY UPDATE amount = account.amount   :someValue")
  Long addAmount(@Param("id") Long id, @Param("someValue") Long amount);

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

}