Implement Update-Or-Insert in Spring Data JPA

1. Introduction

In application development, the need to perform an update-or-insert operation, also known as “upsert”, is quite common. This operation involves putting a new record into a database table if it doesn’t exist or updating an existing record if it does.

In this tutorial, we’ll learn different approaches to performing update-or-insert operations using Spring Data JPA.

2. Setup

For demo purposes, we’ll use a CreditCard entity:

@Entity
@Table(name="credit_card")
public class CreditCard {
    @Id
    @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "credit_card_id_seq")
    @SequenceGenerator(name = "credit_card_id_seq", sequenceName = "credit_card_id_seq", allocationSize = 1)
    private Long id;
    private String cardNumber;
    private String expiryDate;

    private Long customerId;

   // getters and setters
}

3. Implementation

We’ll implement update-or-insert using three different approaches.

3.1. Using Repository Method

In this approach, we’ll write a transactional default method in the repository using the save(entity) method, which is inherited from the CrudRepository interface. The save(entity) method inserts the record if it’s new, or it updates the existing entity based on id:

public interface CreditCardRepository extends JpaRepository<CreditCard,Long> {
    @Transactional
    default CreditCard updateOrInsert(CreditCard entity) {
        return save(entity);
    }
}

We’re passing creditCard to the updateOrInsertUsingReposiotry() method inside the CreditCardLogic class, which either inserts or updates the entity based on entity id :

@Service
public class CreditCardLogic {
    @Autowired
    private CreditCardRepository creditCardRepository;
   
    public void updateOrInsertUsingRepository(CreditCard creditCard) {
        creditCardRepository.updateOrInsert(creditCard);
    }
}

One important note for this approach is that whether the entity is to be updated or not is decided by id. If we need to find existing records based on another column, such as cardNumber instead of id, then this approach won’t work. In that case, we can use the approaches that we discussed in later sections.

We can write unit tests to verify our logic. First, let’s save some test data into the credit_card table:

private CreditCard createAndReturnCreditCards() {
    CreditCard card = new CreditCard();
    card.setCardNumber("3494323432112222");
    card.setExpiryDate("2024-06-21");
    card.setCustomerId(10L);
    return creditCardRepository.save(card);
}

We’ll use the above-saved CreditCard for the update. Let’s build a CreditCard object for insert:

private CreditCard buildCreditCard() {
    CreditCard card = new CreditCard();
    card.setCardNumber("9994323432112222");
    card.setExpiryDate("2024-06-21");
    card.setCustomerId(10L);

    return card;
}

We’re ready to write our unit test:

@Test
void givenCreditCards_whenUpdateOrInsertUsingRepositoryExecuted_thenUpserted() {
    // insert test
    CreditCard newCreditCard = buildCreditCard();
    CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNull(existingCardByCardNumber);

    creditCardLogic.updateOrInsertUsingRepository(newCreditCard);

    existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNotNull(existingCardByCardNumber);

    // update test
    CreditCard cardForUpdate = existingCard;
    String beforeExpiryDate = cardForUpdate.getExpiryDate();
    cardForUpdate.setExpiryDate("2029-08-29");
    existingCardByCardNumber = creditCardRepository.findByCardNumber(cardForUpdate.getCardNumber());
    assertNotNull(existingCardByCardNumber);

    creditCardLogic.updateOrInsertUsingRepository(cardForUpdate);

    assertNotEquals("2029-08-29", beforeExpiryDate);
    CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get();
    assertEquals("2029-08-29", updatedCard.getExpiryDate());
}

In the above test, we’re asserting both insert and update operations for the updateOrInsertUsingRepository() method.

3.2. Using Custom Logic

In this approach, we write custom logic inside the CreditCardLogic class, which first checks if the given row already exists in the table, and based on the output, it decides to insert or update the record :

public void updateOrInsertUsingCustomLogic(CreditCard creditCard) {
    CreditCard existingCard = creditCardRepository.findByCardNumber(creditCard.getCardNumber());
    if (existingCard != null) {
        existingCard.setExpiryDate(creditCard.getExpiryDate());
        creditCardRepository.save(creditCard);
    } else {
        creditCardRepository.save(creditCard);
    }
}

As per the above logic, if the cardNumber already exists in the database, then we update that existing entity based on the passed CreditCard object. Otherwise, we insert the passed CreditCard as a new entity in the updateOrInsertUsingCustomLogic() method.

We can write unit tests to verify our custom logic:

@Test
void givenCreditCards_whenUpdateOrInsertUsingCustomLogicExecuted_thenUpserted() {
    // insert test
    CreditCard newCreditCard = buildCreditCard();
    CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNull(existingCardByCardNumber);

    creditCardLogic.updateOrInsertUsingCustomLogic(newCreditCard);

    existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNotNull(existingCardByCardNumber);

    // update test
    CreditCard cardForUpdate = existingCard;
    String beforeExpiryDate = cardForUpdate.getExpiryDate();
    cardForUpdate.setExpiryDate("2029-08-29");

    creditCardLogic.updateOrInsertUsingCustomLogic(cardForUpdate);

    assertNotEquals("2029-08-29", beforeExpiryDate);
    CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get();
    assertEquals("2029-08-29", updatedCard.getExpiryDate());
}

3.3. Using Database Built-In Feature

Many databases provide a built-in feature to handle conflict on insert. For example, PostgreSQL provides “ON CONFLICT DO UPDATE” and MySQL provides “ON DUPLICATE KEY”. Using this feature, we can write a follow-up update statement when there is a duplicate key while inserting a record into the database.

An example query would be like this:

String updateOrInsert = """
    INSERT INTO credit_card (card_number, expiry_date, customer_id)
    VALUES( :card_number, :expiry_date, :customer_id )
    ON CONFLICT ( card_number )
    DO UPDATE SET
    card_number = :card_number,
    expiry_date = :expiry_date,
    customer_id = :customer_id
    """;

For testing, we’re using the H2 database, which doesn’t provide an “ON CONFLICT” feature, but instead, we can use a merge query provided by the H2 database. Let’s add the merge  logic inside the CreditCardLogic class:

@Transactional
public void updateOrInsertUsingBuiltInFeature(CreditCard creditCard) {
    Long id = creditCard.getId();
    if (creditCard.getId() == null) {
        BigInteger nextVal = (BigInteger) em.createNativeQuery("SELECT nextval('credit_card_id_seq')").getSingleResult();
        id = nextVal.longValue();
    }

   String upsertQuery = """
       MERGE INTO credit_card (id, card_number, expiry_date, customer_id)
       KEY(card_number)
       VALUES (?, ?, ?, ?)
       """;

    Query query = em.createNativeQuery(upsertQuery);
    query.setParameter(1, id);
    query.setParameter(2, creditCard.getCardNumber());
    query.setParameter(3, creditCard.getExpiryDate());
    query.setParameter(4, creditCard.getCustomerId());

    query.executeUpdate();
}

In the above logic, we execute a merge query using the native query provided by entityManager.

Now, let’s write the unit test to verify the results:

@Test
void givenCreditCards_whenUpdateOrInsertUsingBuiltInFeatureExecuted_thenUpserted() {
    // insert test
    CreditCard newCreditCard = buildCreditCard();
    CreditCard existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNull(existingCardByCardNumber);

    creditCardLogic.updateOrInsertUsingBuiltInFeature(newCreditCard);

    existingCardByCardNumber = creditCardRepository.findByCardNumber(newCreditCard.getCardNumber());
    assertNotNull(existingCardByCardNumber);

    // update test
    CreditCard cardForUpdate = existingCard;
    String beforeExpiryDate = cardForUpdate.getExpiryDate();
    cardForUpdate.setExpiryDate("2029-08-29");

    creditCardLogic.updateOrInsertUsingBuiltInFeature(cardForUpdate);

    assertNotEquals("2029-08-29", beforeExpiryDate);
    CreditCard updatedCard = creditCardRepository.findById(cardForUpdate.getId()).get();
    assertEquals("2029-08-29", updatedCard.getExpiryDate());
}

4. Conclusion

In this article, we discussed different approaches to performing update or insert operations in Spring Data JPA. We implemented these approaches along with verification using unit tests. While each database does provide some out-of-the-box features for handling upsert, implementing custom logic in Spring Data JPA on top of upsert based on the id column isn’t that complex.

As always, the example code is available over on GitHub.