ON CONFLICT Clause for Hibernate Insert Queries

1. Overview

In this tutorial, we’ll learn about the ON CONFLICT clause for insert queries introduced in Hibernate 6.5.

We use an ON CONFLICT clause to handle table constraint violations while inserting the data using HQL or criteria queries. The ON CONFLICT clause can also be used to handle upsert queries.

To learn more about insert queries in Hibernate, check out our tutorial on how to perform an INSERT statement on JPA objects.

2. ON CONFLICT Clause

The syntax for an insert using the ON CONFLICT clause is:

"INSERT" "INTO"? targetEntity targetFields (queryExpression | valuesList) conflictClause?

The conflictClause is written as:

"on conflict" conflictTarget? conflictAction

And the conflictAction is either DO NOTHING or DO UPDATE.

Now, let’s go through an example. Let’s consider the entity class Student having studentId and name as attributes:

@Entity
public class Student {
    @Id
    private long studentId;
    private String name;
}

The studentId attribute is a unique key for the Student entity. We can either insert the @Id value in the INSERT VALUES query or use the @GeneratedValue annotation.

Reacting to conflicts can be based on either the name or the list of attributes that form the unique constraint. Using the unique constraint name as a conflict target requires either native database support or that the statement is a single-row insert.

Possible conflict actions are to ignore the conflict or update conflicting objects/rows:

int updated = session.createMutationQuery("""
  insert into Student (studentId, name)
  values (1, 'John')
  on conflict(studentId) do update
  set name = excluded.name
  """).executeUpdate();

Here, if a conflict occurs by inserting a row with the same studentId as an existing row, then the existing row is updated. The special alias excluded is available in the update set clause of the ON CONFLICT clause and refers to the values that failed insertion due to a unique constraint conflict.

Hibernate translates the query with ON CONFLICT clause to a merge query:

MERGE INTO Student s1_0
USING (VALUES (1, 'John')) excluded(studentId, NAME)
ON ( s1_0.studentId = excluded.studentId)
WHEN matched THEN
  UPDATE SET NAME = excluded.NAME
WHEN NOT matched THEN
  INSERT (studentId,
          NAME)
  VALUES (excluded.studentId,
          excluded.NAME) 

The ON CONFLICT clause is translated to an upsert query using the excluded feature. The query excludes the original record with alias p1_0 and inserts the new record. If the studentId (conflicting attribute) matches, then we update the attributes other than the studentId. If it doesn’t match, then we perform an insert operation.

We use DO NOTHING to ignore the conflicts, ensuring nothing happens in case of a conflict and averting potential errors:

int updated = session.createMutationQuery("""
  insert into Student (studentId, name)
  values (1, 'John')
  on conflict(studentId) do nothing
  """).executeUpdate();

Here, if the table already contains a row with studentId 1, Hibernate ignores the query and averts an exception.

3. Examples

3.1. DO UPDATE

We’ve added some test cases to get a better understanding of the ON CONFLICT clause. We insert non-conflicting data with conflict action do update:

long rowCountBefore = getRowCount();
int updated = session.createMutationQuery("""
  insert into Student (studentId, name) values (2, 'Sean')
  on conflict(studentId) do update
  set name = excluded.name
  """).executeUpdate();
long rowCountAfter = getRowCount();
assertEquals(updated, 1);
assertEquals(rowCountBefore, rowCountAfter);

The inserted data is non-conflicting and inserted in the database. Hibernate ignores the ON CONFLICT clause when there is no conflict, and the query execution returns update value 1. The row count changes after the execution of the statement, indicating that the query inserts a row in the table.

Now, let’s see a test case where we insert conflicting data with conflict action do update:

long rowCountBefore = getRowCount();
int updated = session.createMutationQuery("""
  insert into Student (studentId, name) values (1, 'Sean')
  on conflict(studentId) do update
  set name = excluded.name
  """).executeUpdate();
long rowCountAfter = getRowCount();
assertEquals(updated, 1);
assertEquals(rowCountBefore, rowCountAfter);

In this test case, the query inserts a record with studentId 1. The table already has a row with studentId 1. If the query is executed, it normally throws a ConstraintViolationException as studentId is a unique constraint. We handle this exception using the ON CONFLICT clause. Instead of getting an exception, the specified conflict action, do update updates specified data.

The line set name = excluded.name updates the name field. The keyword excluded comes with conflict action do nothing. We can update all fields except the conflicting field using the excluded keyword.

3.2. DO NOTHING

Now let’s see what happens when we insert non-conflicting data with the ON CONFLICT clause and conflict action set to do nothing:

long rowCountBefore = getRowCount();
int updated = session.createMutationQuery("""
  insert into Student (studentId, name) values (2, 'Sean')
  on conflict do nothing 
  """).executeUpdate();
long rowCountAfter = getRowCount();
assertEquals(updated, 1);
assertNotEquals(rowCountBefore, rowCountAfter);

We observe that there’s no conflict while inserting the data record into the database. The query returns 1 as an updated value. The query execution increases the number of rows in the table by 1.

Let’s see a case where we insert conflicting data and use the ON CONFLICT clause with the do nothing action:

long rowCountBefore = getRowCount();
int updated = session.createMutationQuery("""
  insert into Student (studentId, name) values (1, 'Sean')
  on conflict do nothing 
  """).executeUpdate();
long rowCountAfter = getRowCount();
assertEquals(updated, 0);
assertEquals(rowCountBefore, rowCountAfter);

Here, the record inserted has studentId 1, and it causes conflict when the query is executed. Since we’ve used do nothing action, it performs no action in case of a conflict. Query execution returns an update value of 0 without updating any record. Also, the number of rows before and after query execution remains the same.

4. Conclusion

In this article, we’ve learned about the ON CONFLICT clause introduced in Hibernate 6.5. We also went through examples to get a better understanding of the ON CONFLICT clause.