Hibernate ON CONFLICT DO clause

Last modified:

Are you struggling with performance issues in your Spring, Jakarta EE, or Java EE application?

What if there were a tool that could automatically detect what caused performance issues in your JPA and Hibernate data access layer?

Wouldn’t it be awesome to have such a tool to watch your application and prevent performance issues during development, long before they affect production systems?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, Micronaut, or Play Framework.

So, rather than fixing performance issues in your production system on a Saturday night, you are better off using Hypersistence Optimizer to help you prevent those issues so that you can spend your time on the things that you love!

Introduction

In this article, we are going to see how the Hibernate ON CONFLICT DO clause works, and how we can use it to execute an SQL UPSERT statement in a portable way.

Upsert

As I explained in this article, if you have this use case where you need to either INSERT or UPDATE a table record based on whether the underlying table contains the record or not, then we need to use an Upsert statement.

While Hibernate offers an upsert method in the StatelessSession, there is also an alternative for JPQL entity queries.

Hibernate ON CONFLICT DO clause

Let’s assume we have the following Book entity:

@Entity
@Table(name = "book")
public class Book {
⠀
    @Id
    private Long id;
⠀
    private String title;
⠀
    @NaturalId
    private String isbn;
}

Assuming we have an empty book table:

| id | isbn           | title                             |
| -- | -------------- | --------------------------------- |

To insert a new Book or update an existing one using a single SQL statement, we will have to execute the following JPQL statement that uses the ON CONFLICT DO clause:

entityManager.createQuery("""
    insert into Book (id, title, isbn)
    values (
        :id,
        :title,
        :isbn
    )
    on conflict(id) do
    update
    set
        title = excluded.title,
        isbn = excluded.isbn
    """)
.setParameter("id", 1L)
.setParameter("title", "High-Performance Java Persistence")
.setParameter("isbn", "978-9730228236")
.executeUpdate();

When executing this statement on PostgreSQL, Hibernate generates the following SQL statement:

INSERT INTO book as b1_0(id,title,isbn)
VALUES (
    1,
    'High-Performance Java Persistence',
    '978-9730228236'
)
ON CONFLICT(id) DO
UPDATE SET
    title = excluded.title,
    isbn = excluded.isbn

The excluded alias is assigned by PostgreSQL on a pseudo table that contains the records meant to be inserted. That’s the reason we can reference them in the UPDATE clause as well.

For more details about this syntax, check out the PostgreSQL manual or the jOOQ one.

When checking the book table, we can see that a new record was inserted:

| id | isbn           | title                             |
| -- | -------------- | --------------------------------- |
| 1  | 978-9730228236 | High-Performance Java Persistence |

If we run it on MySQL, Hibernate will execute the following SQL statement that uses the ON DUPLICATE KEY MySQL-specific clause:

INSERT INTO book(id,title,isbn)
VALUES (
    1,
    'High-Performance Java Persistence',
    '978-9730228236'
) AS excluded(id,title,isbn)
ON DUPLICATE KEY
UPDATE
    title = excluded.title,
    isbn = excluded.isbn

When running the Hibernate ON CONFLICT DO query on Oracle, Hibernate will use the MERGE statement instead:

MERGE INTO book b1_0 using (
    SELECT 
        1 id, 
        'High-Performance Java Persistence' title,
        '978-9730228236' isbn 
    FROM dual
) excluded ON (b1_0.id = excluded.id) 
WHEN MATCHED THEN 
UPDATE SET 
    b1_0.title = excluded.title,
    b1_0.isbn = excluded.isbn 
WHEN NOT MATCHED THEN 
INSERT (id,title,isbn) 
VALUES (
    excluded.id,
    excluded.title,
    excluded.isbn
)

When running the Hibernate JPQL query with the ON CONFLICT DO clause that now changes the title and the isbn number on PostgreSQL:

entityManager.createQuery("""
    insert into Book (id, title, isbn)
    values (
        :id,
        :title,
        :isbn
    )
    on conflict(id) do
    update
    set
        title = excluded.title,
        isbn = excluded.isbn
    """)
.setParameter("id", 1L)
.setParameter("title", "High-Performance Java Persistence, 2nd edition")
.setParameter("isbn", "978-9730228237")
.executeUpdate();

We can see that Hibernate will generate the following SQL query:

INSERT INTO book as b1_0(id,title,isbn)
VALUES (
    1,
    'High-Performance Java Persistence, 2nd edition',
    '978-9730228237'
)
ON CONFLICT(id) DO
UPDATE SET
    title = excluded.title,
    isbn = excluded.isbn

This time, the Hibernate ON CONFLICT DO clause will set the title and the isbn columns of our book table record to the new values.

When checking the book table, we can see that the previously inserted record was successfully updated:

| id | isbn           | title                                          |
| -- | -------------- | ---------------------------------------------- |
| 1  | 978-9730228237 | High-Performance Java Persistence, 2nd edition |

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Starting with Hibernate 6.5, you can use the ON CONFLICT DO clause and generate the Upsert statement in a portable way.

Based on the underlying database capabilities, Hibernate will choose either a database-specific statement, such as the ON CONFLICT DO one in PostgreSQL, or the SQL Standard MERGE statement.

Follow @vlad_mihalcea