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 the FetchType.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 the APPROVED state having a PostComment child entity in the PENDING state and containing a spam message
• two Post entities in the PENDING state having a spam title

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 supplied PostModerate entity.
• Second, we set the status property to SPAM and the updateOn property to the current date.
• Then, we will create a filtering predicate for the message property, which is common to all PostModerate entities that need to be moderated.
• Only for the Post entity, we also check the title property.

Notice that we used the Post_, PostComment_ and PostModerate_ 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.

Insert details about how the information is going to be processed

DOWNLOAD NOW