Hibernate StatelessSession Upsert
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how the Hibernate StatelessSession Upsert method works.
As I explained in this article, the UPSERT operation allows you to INSERT a record if there is no such record matching the filtering criteria or to UPDATE it in case the record exists.
While many relational database systems offer the standard MERGE command to execute the UPSERT command, the syntax may differ slightly from one database to the other. Therefore, it’s great that Hibernate provides a portable way to achieve this goal.
Domain Model
Let’s assume we are using the following Book entity:
@Entity
@Table(name = "book")
public class Book {
⠀
@Id
private Long id;
⠀
private String title;
⠀
@NaturalId
private String isbn;
⠀
public Long getId() {
return id;
}
⠀
public Book setId(Long id) {
this.id = id;
return this;
}
⠀
public String getTitle() {
return title;
}
⠀
public Book setTitle(String title) {
this.title = title;
return this;
}
⠀
public String getIsbn() {
return isbn;
}
⠀
public Book setIsbn(String isbn) {
this.isbn = isbn;
return this;
}
}
Hibernate StatelessSession Upsert
Since version 6.3, the Hibernate StatelessSession utility provides an Upsert method we can use to either INSERT or UPDATE a JPA entity.
Executing the Upsert on PostgreSQL
When executing the following test case on PostreSQL:
doInStatelessHibernate(session -> {
session.upsert(
new Book()
.setId(1L)
.setTitle("High-Performance Hibernate")
.setIsbn("978-9730228236")
);
⠀
session.upsert(
new Book()
.setId(1L)
.setTitle("High-Performance Hibernate 2nd edition")
.setIsbn("978-9730228236")
);
});
Hibernate generates the following SQL statements:
Query:["
MERGE INTO
book AS t
USING (
SELECT
CAST(? as bigint) id,
CAST(? as text) title
) AS s
ON (t.id = s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET title = s.title
"],
Params:[(1, High-Performance Hibernate)]
Query:["
MERGE INTO
book AS t
USING (
SELECT
CAST(? as bigint) id,
CAST(? as text) title
) AS s
ON (t.id = s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET title = s.title
"],
Params:[(1, High-Performance Hibernate 2nd edition)]
Executing the Upsert on Oracle
When executing the same test case on Oracle, Hibernate generates the following SQL statements:
Query:["
MERGE INTO
book t
USING (
SELECT
CAST(? as number(19,0)) id,
CAST(? as varchar2(4000 char)) title
FROM
dual
) s ON (t.id=s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET t.title = s.title
"],
Params:[(1, High-Performance Hibernate)]
Query:["
MERGE INTO
book t
USING (
SELECT
CAST(? as number(19,0)) id,
CAST(? as varchar2(4000 char)) title
FROM
dual
) s ON (t.id=s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET t.title = s.title
"],
Params:[(1, High-Performance Hibernate 2nd edition)]
Notice the FROM dual part that’s specific to Oracle.
Executing the Upsert on SQL Server
When executing the test case on SQL Server, Hibernate generates the MERGE SQL statement as well:
Query:["
MERGE INTO
book AS t USING (
SELECT
CAST(? as bigint) id,
CAST(? as varchar(max)) title
) AS s ON (t.id=s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET title = s.title;
"],
Params:[(1, High-Performance Hibernate)]
Query:["
MERGE INTO
book AS t USING (
SELECT
CAST(? as bigint) id,
CAST(? as varchar(max)) title
) AS s ON (t.id=s.id)
WHEN NOT MATCHED THEN
INSERT (id, title) VALUES (s.id, s.title)
WHEN MATCHED THEN
UPDATE SET title = s.title;"],
Params:[(1, High-Performance Hibernate 2nd edition)]
Since SQL Server supports the MERGE statement, Hibernate uses just like on PostgreSQL and Oracle.
Executing the Upsert on MySQL
When executing the same test case on MySQL, Hibernate generates a pair of UPDATE and INSERT SQL statements:
Query:["
UPDATE
book
SET
title = ?
WHERE
id = ?
"],
Params:[(High-Performance Hibernate, 1)]
Query:["
INSERT INTO
book (title,id)
VALUES
(?,?)
"],
Params:[(High-Performance Hibernate, 1)]
Query:["
UPDATE
book
SET
title = ?
WHERE
id = ?
"],
Params:[(High-Performance Hibernate 2nd edition, 1)]
In the case of MySQL, since the MERGE is not supported, the upsert method will simply render an UPDATE followed by an INSERT instead of using the ON DUPLICATE KEY clause.
The reason why the second UPDATE is not followed by an INSERT is because the UPDATE call returned the updateCount value of 1, so Hibernate knows that the UPDATE went through.
I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The Hibernate StatelessSession Upsert method can use the MERGE SQL command if the database supports it, like it’s the case with PostgreSQL, Oracle, or SQL Server.
