UPSERT type operations

[trajano]

It my be out of scope of JPA which is why I opted for discussion rather than issue.

A few but I don't think all databases would have some sort of upsert operation. I was wondering if there's a way of incorporating something into the standard though?

Right now I would do something with this logic:

1. ensure no transaction is active
2. try
   1. in-transaction
   2. var recordToUpdateOrInsert = find existing record using em.createQuery
   3. if not present
      1. recordToUpdateOrInsert = new Entity()
      2. update recordToUpdateOrInsert
      3. em.persist(recordToUpdateOrInsert)
   4. else
      1. update recordToUpdateOrInsert
      2. em.merge(recordToUpdateOrInsert)
   5. em.flush() // it will be flushed after the transaction scope finishes, but this makes it easier to focus on
3. catch PersistenceException (which handles both OptimisticLock and unique constraint errors)
   1. in-transaction
   2. var recordToUpdate = find existing record using em.createQuery
   3. if not found throw new IllegalStateException("expected existing value");
   4. update recordToUpdate
   5. em.merge(recordToUpdate)
   6. em.flush() // it will be flushed after the transaction scope finishes, but this makes it easier to focus on

Which is kind of verbose, though I made a function that I use with Spring Data JPA with the following signature

/**
 * @param queryToGetRecord function to optionally get the existing entity
 * @param supplierForNewRecord function to provide a new entity that is to be managed
 * @param mapper function that takes in either the existing or new entity and applies changes to it.  The function may modify the originally entity so long as it returns the modified original entity.
 * @param repository the JPA repository where saveAndFlush operations will be called for the entity.
 * @param <T> entity type
 */
<T> void upsert(
  @NotNull final Supplier<Optional<T>> queryToGetRecord,
  @NotNull final Supplier<T> supplierForNewRecord,
  @NotNull final UnaryOperator<T> mapper,
  @NotNull final JpaRepository<T, ?> repository);

[gavinking]

When we do StatelessEntityManager, we could give it an upsert() method just like Hibernate's StatelessSession.upsert().

[trajano]

I wonder how someone would use the upsert construct using Spring Data JPA

[gavinking]

I mean, in our implementation of Jakarta Data, the @Save operation maps to upsert().