JPA Bulk Update and Delete with Blaze Persistence
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, I’m going to show you how to write JPA Bulk Update and Delete queries using the amazing Blaze Persistence framework.
Blaze Persistence is a JPA framework that allows you to build Criteria queries that are much more powerful than the standard JPA Criteria API. More, it also provides support for Keyset Pagination, which is very useful when having to navigate over large result sets.
Domain Model
In this article, I’m going to use the same entity classes I’ve used for the JPA Criteria API Bulk Update and Delete queries:
The PostStatus
is a Java Enum
which controls whether a given Post
or PostComment
should be visible in our application.
Because Post
and PostComment
records have to be moderated, the initial status is PENDING
. If the system administrators decide that a given posting is valid, the status becomes APPROVED
, and the Post
and PostComment
entries become visible. Otherwise, they are marked as SPAM
.
Both Post
and PostComment
extend the PostModerate
base class, and because the base class contains persisted properties, we need to annotate it with the @MappedSuperclass
JPA annotation.
If you want to reuse properties from across several entities, you can use the @MappedSuperClass
annotation.
The PostModerate
class looks as follows:
@MappedSuperclass public abstract class PostModerate { @Enumerated(EnumType.ORDINAL) @Column(columnDefinition = "tinyint") private PostStatus status = PostStatus.PENDING; @Column(name = "updated_on") private Date updatedOn = new Date(); //Getters and setters omitted for brevity }
If you want to persist
Enum
properties, then the most compact column type is the shortest available integer column type.For more details about the advantages and disadvantages of various
Enum
-persisting strategies, check out this article.
The Post
parent entity looks like this:
@Entity(name = "Post") @Table(name = "post") public class Post extends PostModerate { @Id @GeneratedValue private Long id; private String title; private String message; //Getters and setters omitted for brevity }
And,the PostComment
child entity looks as follows:
@Entity(name = "PostComment") @Table(name = "post_comment") public class PostComment extends PostModerate { @Id @GeneratedValue private Long id; @ManyToOne(fetch = FetchType.LAZY) private Post post; private String message; //Getters and setters omitted for brevity }
By default,
@ManyToOne
and@OneToOne
associations use theFetchType.EAGER
fetching strategy, which is very bad for performance and can lead to N+1 query issues.For more details, check out this article.
Testing data
Considering we have added the following entities to our system:
entityManager.persist( new Post() .setId(1L) .setTitle("High-Performance Java Persistence") .setStatus(PostStatus.APPROVED) ); entityManager.persist( new Post() .setId(2L) .setTitle("Spam title") ); entityManager.persist( new Post() .setId(3L) .setMessage("Spam message") ); entityManager.persist( new PostComment() .setId(1L) .setPost( entityManager.getReference(Post.class, 1L) ) .setMessage("Spam comment") );
So, we have:
- one
Post
entity in theAPPROVED
state having aPostComment
child entity in thePENDING
state and containing a spammessage
- two
Post
entities in thePENDING
state having a spamtitle
Blaze Persistence Bulk Update
To build a Bulk UPDATE statement dynamically, Blaze Persistence offers the UpdateCriteriaBuilder
utility.
To see how UpdateCriteriaBuilder
works, check out the following example:
public <T extends PostModerate> int flagSpam( EntityManager entityManager, Class<T> postModerateClass) { UpdateCriteriaBuilder<T> builder = cbf .update(entityManager, postModerateClass) .set(PostModerate_.STATUS, PostStatus.SPAM) .set(PostModerate_.UPDATED_ON, new Date()); String spamToken = "%spam%"; if(Post.class.isAssignableFrom(postModerateClass)) { builder .whereOr() .where(lower(Post_.MESSAGE)) .like().value(spamToken).noEscape() .where(lower(Post_.TITLE)) .like().value(spamToken).noEscape() .endOr(); } else if(PostComment.class.isAssignableFrom(postModerateClass)) { builder .where(lower(PostComment_.MESSAGE)) .like().value(spamToken).noEscape(); } return builder.executeUpdate(); }
The flagSpam
method works as follows:
- First, we need to obtain a
UpdateCriteriaBuilder
reference so that we can create our Bulk Update dynamic statement for the suppliedPostModerate
entity. - Second, we set the
status
property toSPAM
and theupdateOn
property to the current date. - Then, we will create a filtering predicate for the
message
property, which is common to allPostModerate
entities that need to be moderated. - Only for the
Post
entity, we also check thetitle
property.
Notice that we used the
Post_
,PostComment_
andPostModerate_
JPA Metadata classes to reference the entity properties.For more details about the JPA Metamodel, check out this article.
This example shows the true power of a dynamic query builder because the SQL statement syntax varies based on the supplied arguments.
Without Blaze Persistence or Criteria API, you’d probably resort to using String
concatenation and risk SQL injection attacks.
When executing the flagSpam
method against the Post
entity class:
assertEquals(2, flagSpam(entityManager, Post.class));
Hibernate will execute the following SQL statement:
UPDATE post SET status = 2, updated_on = '2018-01-09 10:50:42.861' WHERE lower(message) LIKE '%spam%' OR lower(title) LIKE '%spam%'
And, when executing it against the PostComment
records:
assertEquals(1, flagSpam(entityManager, PostComment.class));
We get the following SQL statement executed:
UPDATE post_comment SET status = 2, updated_on = '2018-01-09 10:50:43.07' WHERE lower(message) LIKE '%spam%'
Blaze Persistence Bulk Delete
You can use Blaze Persistence to build Bulk Delete queries dynamically.
For instance, the following example shows how you can delete the old Post
and PostComment
entities having the status
of SPAM
:
public <T extends PostModerate> int deleteSpam( EntityManager entityManager, Class<T> postModerateClass) { return cbf .delete(entityManager, postModerateClass) .where(PostModerate_.STATUS).eq(PostStatus.SPAM) .where(PostModerate_.UPDATED_ON).le( Timestamp.valueOf( LocalDateTime.now().minusDays( (Post.class.isAssignableFrom(postModerateClass)) ? 7 : 3 ) ) ) .executeUpdate(); }
This time, we only vary the parameter passed to the filtering predicate. However, you can vary the entire WHERE clause, just like we did for the bulk update statement.
To test how it works, let’s make sure that our spam postings are old enough to be deleted:
entityManager.createQuery(""" update Post set updatedOn = :timestamp where status = :status """) .setParameter( "timestamp", Timestamp.valueOf(LocalDateTime.now().minusDays(7)) ) .setParameter("status", PostStatus.SPAM) .executeUpdate(); entityManager.createQuery(""" update PostComment set updatedOn = :timestamp where status = :status """) .setParameter( "timestamp", Timestamp.valueOf(LocalDateTime.now().minusDays(3)) ) .setParameter("status", PostStatus.SPAM) .executeUpdate();
Good, now we can run the deleteSpam
method:
assertEquals(2, deleteSpam(entityManager, Post.class)); assertEquals(1, deleteSpam(entityManager, PostComment.class));
and Hibernate is going to execute the following DELETE statements:
DELETE FROM post WHERE status = 2 AND updated_on <= '2021-09-07 17:19:11.709' DELETE FROM post_comment WHERE status = 2 AND updated_on <= '2021-09-11 17:19:11.720'
Awesome, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Blaze Persistence is a very good alternative to the standard JPA Criteria API.
If you are curious how you’d write the same queries using Criteria API, check out this previous article I wrote, and you’ll surely like Blaze Persistence better.
