CODEWITHSUNDEEP
hibernatejpaormupsertstateless-session
Roman Oborin
Roman Oborin

Reputation: 33

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.

Upvotes: 1

Views: 340

Answers (2)

andychukse
andychukse

Reputation: 592

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);
    }
}

Upvotes: 1

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154070

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

Upvotes: 3

Related Questions