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.
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();