Introduction

JPA Criteria API bulk update delete is a great feature that allows you to build bulk update and delete queries using the JPA 2.1 Criteria API support via CriteriaUpdate and CriteriaDelete.

Because one of our community members asked me on the Hibernate forum about this topic, I decided it is a good opportunity to write about this lesser-known JPA Criteria API feature.

JPA Criteria API Bulk Update and Delete #java https://t.co/OYHHYgklPQ pic.twitter.com/3PCeoEsArz

— Java (@java) January 11, 2018

Domain Model

Assuming we have the following entities in our system:

The PostStatus is a Java Enum which states if a given Post or PostComment should be visible in our application. Because all Post and PostComment entries are being moderated, the initial status is PENDING. If the system decides that a given posting is valid, the status becomes APPROVED and the posting becomes visible. Otherwise, the posting is marked as SPAM.

If you want to reuse properties from across several entities, you can use the @MappedSuperClass annotation.

That’s the reason why both Post and PostComment extend the PostModerate class which 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 entity looks as follows:

@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 entity looks like this:

@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:

Post _post = doInJPA(entityManager -> {
    Post post = new Post();
    post.setTitle("High-Performance Java Persistence");
    post.setStatus(PostStatus.APPROVED);
    entityManager.persist(post);

    return post;
});

doInJPA(entityManager -> {
    Post spamPost1 = new Post();
    spamPost1.setTitle("Spam title");
    entityManager.persist(spamPost1);

    Post spamPost2 = new Post();
    spamPost2.setMessage("Spam message");
    entityManager.persist(spamPost2);

    PostComment spamComment = new PostComment();
    spamComment.setPost(_post);
    spamComment.setMessage("Spam comment");
    entityManager.persist(spamComment);
});

Therefore, we have:

• one Post entity that is APPROVED, but has several PostComment child entities that have a PENDING status
• another Post entity that has a PENDING status

CriteriaUpdate

To build queries dynamically, you should always use a professional query builder like JPA Criteria API or jOOQ.

You should never resort to using String concatenation to build queries dynamically because this bad practice is prone to SQL injection attacks.

For more details, check out this article.

To build SQL UPDATE statements dynamically, JPA offers the CriteriaUpdate class.

To see how CriteriaUpdate works, check out the following example which we will use to flag spam postings:

public <T extends PostModerate> int flagSpam(
        EntityManager entityManager, 
        Class<T> postModerateClass) {
        
    CriteriaBuilder builder = entityManager
        .getCriteriaBuilder();
        
    CriteriaUpdate<T> update = builder
        .createCriteriaUpdate(postModerateClass);

    Root<T> root = update.from(postModerateClass);

    Expression<Boolean> filterPredicate = builder
    .like(
        builder.lower(root.get("message")), 
        "%spam%"
    );

    if(Post.class.isAssignableFrom(postModerateClass)) {
        filterPredicate = builder.or(
            filterPredicate, builder
            .like(
                builder.lower(root.get("title")), 
                "%spam%"
            )
        );
    }

    update
    .set(root.get("status"), PostStatus.SPAM)
    .set(root.get("updatedOn"), new Date())
    .where(filterPredicate);

    return entityManager
    .createQuery(update)
    .executeUpdate();
}

The flagSpam method works as follows:

• First, we need to obtain a CriteriaBuilder so that we can create our Criteria API dynamic statements.
• Second, we will create the CriteriaUpdate statement against the supplied postModerateClass which can be any class extending PostModerate.
• Then, we will create a filtering predicate for the message property which is common to all postings that need to be moderated.
• Only for the Post entity we also check the title property.

This example shows the true power of a dynamic query builder because the statement can be built so that it varies based on the supplied arguments. Without Criteria API, you’d probably resort to using String concatenation and risk SQL injection attacks.

Now, we can test out flagSpam method as follows:

assertEquals(2, flagSpam(entityManager, Post.class));
assertEquals(1, flagSpam(entityManager, PostComment.class));

And Hibernate will execute the following SQL statements:

UPDATE 
    post 
SET 
    status = 2, 
    updated_on = '2021-01-09 10:50:42.861'
WHERE 
    lower(message) LIKE '%spam%' OR 
    lower(title) LIKE '%spam%'
    
UPDATE 
    post_comment 
SET 
    status = 2, 
    updated_on = '2021-01-09 10:50:43.07' 
WHERE 
    lower(message) LIKE '%spam%'

Notice how the UPDATE statement varies based on the underlying entity type. That’s why CriteriaUpdate is worth using for dynamic bulk update statements.

CriteriaDelete

Not only that JPA offers a CriteriaUpdate, but it also comes with a CriteriaDelete utility for building dynamic bulk delete statements.

To see how CriteriaDelete works, check out the following example which we will use to delete old spam postings:

public <T extends PostModerate> int deleteSpam(
        EntityManager entityManager, 
        Class<T> postModerateClass) {
        
    CriteriaBuilder builder = entityManager
        .getCriteriaBuilder();
        
    CriteriaDelete<T> delete = builder
        .createCriteriaDelete(postModerateClass);

    Root<T> root = delete.from(postModerateClass);

    int daysValidityThreshold = 
        (Post.class.isAssignableFrom(postModerateClass)) ? 
            7 : 
            3;

    delete
    .where(
        builder.and(
            builder.equal(
                root.get("status"), 
                PostStatus.SPAM
            ),
            builder.lessThanOrEqualTo(
                root.get("updatedOn"), 
                Timestamp.valueOf(
                    LocalDateTime
                    .now()
                    .minusDays(daysValidityThreshold)
                )
            )
        )
    );

    return entityManager
    .createQuery(delete)
    .executeUpdate();
}

This time, we only vary the parameter passed to the filtering predicate. However, you can vary the entire WHERE clause when using the CriteriaDelete utility.

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-01-02 10:50:43.109'
    
DELETE FROM 
    post_comment 
WHERE 
    status = 2 AND 
    updated_on <= '2021-01-06 10:50:43.115'

That’s it! You can easily create dynamic bulk update and delete statements using the Criteria API.

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

Conclusion

While the CriteriaQuery has been available since JPA 2.0, CriteriaUpdate and CriteriaDelete made it into the JPA specification since JPA 2.1.

For this reason, they are not very well known or acknowledged. This article proves that they are very useful and you should definitely employ them.

Insert details about how the information is going to be processed

DOWNLOAD NOW