hibernatejpaormupsertstateless-session

Hibernate StatelessSession.upsert() based on @NaturalId


I have such entity with surrogate PK and unique key based on 3 varchar columns.

public class InvoiceEntity {

    @Id
    @GeneratedValue
    private UUID id;

    @NaturalId
    private InvoiceUniqueKey invoiceId;
}

Using statelessSession.upsert(invoiceEntity) it does generate following sql

merge into invoice as t using (select cast(? as uuid) id ... as s on (t.id=s.id)

So the question is - How can i force hibernate use my unique key(@NaturalId) in merge clause instead of primary key @Id ?

I tried all annotations in hibernate, read docs and found nothing that may make me able to use it this way.


Solution

  • You can use the ON CONFLICT clause to get the upsert on any unique key column, like this:

    entityManager.createQuery("""
        insert into InvoiceEntity (id, invoiceId, amount)
        values (
            :id,
            :invoiceId,
            :amount
        )
        on conflict(invoiceId) do
        update
        set
            amount = excluded.amount
        """)
    .setParameter("id", id)
    .setParameter("invoiced", invoiceId)
    .setParameter("author", author)
    .executeUpdate();