Introduction

In this article, we are going to see what is the best way to use the Spring Data JPA Specification when combining multiple predicates with the result set ordering logic.

While you can also use query methods or the @Query annotation to define your Spring Data queries, the Spring Data JPA Specification allows you to compose dynamically various filtering criteria, which may be very convenient when you’d, otherwise, end up with lots of query method permutations.

Domain Model and Repository

Let’s assume we are using the following PostComment entity in our application:

For this entity, we create the PostCommentRepository that extends two interfaces:

• the BaseJpaRepository from the Hypersistence Utils project, which is a better alternative to the default JpaRepository from Spring Data
• the JpaSpecificationExecutor, which provides the Spring Data Specification filtering methods

@Repository
public interface PostCommentRepository
    extends BaseJpaRepository<PostComment, Long>,
            JpaSpecificationExecutor<PostComment> {

    interface Specs {

        static Specification<PostComment> byPost(Post post) {
            return (root, query, builder) ->
                builder.equal(root.get(PostComment_.post), post);
        }

        static Specification<PostComment> byStatus(PostComment.Status status) {
            return (root, query, builder) ->
                builder.equal(root.get(PostComment_.status), status);
        }

        static Specification<PostComment> byReviewLike(String reviewPattern) {
            return (root, query, builder) ->
                builder.like(root.get(PostComment_.review), reviewPattern);
        }

        static Specification<PostComment> byVotesGreaterThanEqual(int votes) {
            return (root, query, builder) ->
                builder.greaterThanOrEqualTo(root.get(PostComment_.votes), votes);
        }

        static Specification<PostComment> orderByCreatedOn(
                Specification<PostComment> spec) {
            return (root, query, builder) -> {
                query.orderBy(builder.asc(root.get(PostComment_.createdOn)));
                return spec.toPredicate(root, query, builder);
            };
        }
    }
}

Notice that we have a Specs interface in which we define several Specification definitions:

1️⃣ The byPost Specification filters the PostComment entities that match the provided Post entity reference:

static Specification<PostComment> byPost(Post post) {
    return (root, query, builder) ->
        builder.equal(root.get(PostComment_.POST), post);
}

Notice that we are using the PostComment_ JPA Metamodel to reference the entity properties we are matching.

For more details about the best way to generate and use the JPA Metamodel, check out this article.

2️⃣ The byStatus Specification filters the PostComment entities that have the provided Status:

static Specification<PostComment> byStatus(PostComment.Status status) {
    return (root, query, builder) ->
        builder.equal(root.get(PostComment_.status), status);
}

3️⃣ The byReviewLike Specification filters the PostComment entities that have the review property like the provided pattern:

static Specification<PostComment> byReviewLike(String reviewPattern) {
    return (root, query, builder) ->
        builder.like(root.get(PostComment_.review), reviewPattern);
}

4️⃣ The byVotesGreaterThanEqual Specification filters the PostComment entities that have the votes property greater than or equal to the provided vote count:

static Specification<PostComment> byVotesGreaterThanEqual(int votes) {
    return (root, query, builder) ->
        builder.greaterThanOrEqualTo(root.get(PostComment_.votes), votes);
}

5️⃣ The orderByCreatedOn Specification shows you how you can customize the Criteria API query that will be executed by Spring Data JPA. In this case, we are passing an existing Specification, and we apply the ORDER BY logic to the existing query:

static Specification<PostComment> orderByCreatedOn(
        Specification<PostComment> spec) {
    return (root, query, builder) -> {
        query.orderBy(builder.asc(root.get(PostComment_.createdOn)));
        return spec.toPredicate(root, query, builder);
    };
}

Testing Time

To find all the PostComment entities that belong to a given parent Post entity, we can use the byPost Specification:

List<PostComment> comments = postCommentRepository.findAll(
    byPost(post)
);

And Hibernate is going to execute the following SQL query:

SELECT 
    p1_0.id,
    p1_0.created_on,
    p1_0.parent_id,
    p1_0.post_id,
    p1_0.review,
    p1_0.status,
    p1_0.votes
FROM 
    post_comment p1_0
WHERE 
    p1_0.post_id = 1

Adding an ORDER BY clause to the Spring Data JPA Specification

If we want to add an ORDER BY clause to the previous SQL query, we can do it like this:

List<PostComment> comments = postCommentRepository.findAll(
    orderByCreatedOn(
        byPost(post)
    )
);

Notice that we passed the byPost Specification to the orderByCreatedOn so that when Spring Data JPA runs the query, we get the chance to add the ORDER BY logic, as illustrated by the executed SQL query:

SELECT 
    p1_0.id,
    p1_0.created_on,
    p1_0.parent_id,
    p1_0.post_id,
    p1_0.review,
    p1_0.status,
    p1_0.votes
FROM 
    post_comment p1_0
WHERE 
    p1_0.post_id = 1
ORDER BY 
    p1_0.created_on ASC

Combining multiple Spring Data JPA Specifications

The greatest benefit of the Spring Data JPA Specification is that we can combine as many Spring Data JPA Specifications as our business use case demands.

For instance, if we want to get all PostComment entities matching a given Post, with Pending status, that have a given review pattern and with a number of votes greater than or equal to the provided threshold, we can write the query as follows:

List<PostComment> comments = postCommentRepository.findAll(
    orderByCreatedOn(
        byPost(post)
            .and(byStatus(PostComment.Status.PENDING))
            .and(byReviewLike(reviewPattern))
            .and(byVotesGreaterThanEqual(minVotes))
    )
);

And Hibernate is going to generate the following SQL query:

SELECT 
    p1_0.id,
    p1_0.created_on,
    p1_0.parent_id,
    p1_0.post_id,
    p1_0.review,
    p1_0.status,
    p1_0.votes
FROM 
    post_comment p1_0
WHERE 
    p1_0.post_id = 1 AND 
    p1_0.status = 0 AND 
    p1_0.review like 'Awesome%' ESCAPE '' AND 
    p1_0.votes >= 1
ORDER BY 
    p1_0.created_on ASC

Cool, right?

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

Conclusion

The Spring Data JPA Specification is a very handy feature that allows you to compose your queries from multiple individual filtering options.

More, the query can be built dynamically at runtime based on the user input. And, as illustrated by the orderByCreatedOn example, you are not limited to defining the WHERE clause filtering criteria since you can customize the entire Criteria API query that will be executed by Spring Data JPA.

Insert details about how the information is going to be processed

DOWNLOAD NOW