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.

Updated Answer

Hibernate 6.5 introduced the ON CONFLICT DO clause that can help handle duplicate entries.

The syntax for an insert using the ON CONFLICT clause

"INSERT" "INTO"? targetEntity targetFields (queryExpression | valuesList) "on conflict" conflictTarget? conflictAction

You can then use the sample query below:

public InvoiceEntity mergeInvoice(InvoiceEntity invoiceData){

    Query query = statelessSession.createQuery("""
      insert into InvoiceEntity (invoiceId, otherParams)
      values (:invoiceId, :otherParams)
      on conflict(invoiceId) do update
      set 
        otherParams = excluded.otherParams
    """
    );

    query.setParameter("invoiceId", invoiceData.invoiceId);
    query.setParameter("otherParams", invoiceData.otherParams);

    return query.executeUpdate();
    
}

On conflict clause also allows you to do nothing if there is conflict.

Query query = statelessSession.createQuery("""
      insert into InvoiceEntity (invoiceId, otherParams)
      values (:invoiceId, :otherParams)
      on conflict(invoiceId) do nothing
    """);

query.setParameter("invoiceId", invoiceData.invoiceId);
query.setParameter("otherParams", invoiceData.otherParams);
query.executeUpdate();

Original Answer

The primary key @Id is used by default to determine if a row exists when using upsert or merge. Hibernate tells the DB to enforce uniqueness when you have a @NaturalId assigned. You will get an excerption error (MySQLIntegrityConstraintViolationException) when you upsert and fields marked as @NaturalId exists in the right order (per your varchar columns)

If you want to avoid getting excerption error you have to check for duplicate key (the @NaturalId) before inserting or updating.

A sample code below should give an idea of how to check for presence of record by composite key before inserting or updating. Note: the code is not tested, treat as a pseudocode.

public InvoiceEntity getInvoice(InvoiceEntity invoice){
    Query query = statelessSession.createQuery("SELECT r.id FROM InvoiceEntity r WHERE r.invoiceId=:invoiceId");
    query.setParameter("invoiceId", invoice.invoiceId);
    return query.getSingleResult();
}


public InvoiceEntity mergeInvoice(InvoiceEntity invoiceData){

    InvoiceEntity invoice = getInvoice(invoiceData)

    
    if (invoice) {
        invoice.setName(invoiceData.name); //change the values for the non unique/persistent columns here. You can also use a loop here to assign the data
        statelessSession.update(invoice);
    }
    else {
        statelessSession.save(invoiceData);
    }
}