Introduction

In this article, we are going to see how the Spring Data JPA Query By Example (QBE) feature works, when you should use it, and what limitations it has.

While Spring Data JPA already provides a wide range of options to query data:

• query methods or the @Query annotation
• Spring Data JPA Specification
• custom Repository query methods

The Spring Data Query By Example feature is meant to offer a way to decouple the data filtering logic from the query processing engine so that you can allow the data access layer clients to define the filtering criteria using a generic API that doesn’t depend on the JPA Criteria API.

Domain Model and Repository

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

The Spring Data JPA PostCommentRepository interface that encapsulates the data access methods of the PostComment entity looks like this:

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

I’m using the BaseJpaRepository from the Hypersistence Utils project as a replacement for the default Spring Data JPA JpaRepository, as the BaseJpaRepository provides proper persist and merge methods and removes the generic findAll method.

The BaseJpaRepository is defined like this:

@NoRepositoryBean
public interface BaseJpaRepository<T, ID> 
    extends Repository<T, ID>, 
            QueryByExampleExecutor<T> {
    ...
}

Since it extends the QueryByExampleExecutor interface, the BaseJpaRepository provides the Query By Example feature to all the Spring Data JPA Repositories that extend this base interface.

Fetching child entities by their parent entity reference

To find all the PostComment entities that belong to a given parent Post entity, we can use the following Example query:

PostComment postComment = new PostComment()
    .setPost(
        new Post()
            .setId(1L)
    );

List<PostComment> comments = (List<PostComment>) postCommentRepository
    .findAll(
        Example.of(
            postComment,
            ExampleMatcher
                .matching()
                .withIgnorePaths(
                    PostComment_.VOTES
                )
        )
    );

First, we create a PostComment entity that populates only the data that defines our query filtering criteria.

Since we want to filter by the Post entity, we only set the post property to a Post object that contains the id attribute.

Afterward, we create an Example object using the previous PostComment, and we instruct the JPA provider to skip the votes property because this is a primitive Java type, and it will have a non-null value that, without skipping, Hibernate will think it’s needed for filtering purposes.

When running the above Query by Example, Hibernate generates 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
JOIN 
    post p2_0 ON p2_0.id=p1_0.post_id
WHERE 
    p1_0.post_id = 1

While the query reruns the expected data, it doesn’t do it in a very efficient manner since it includes a superfluous JOIN clause to the post table. The goal of the JOIN is to create compound projections, but this is not what we need, and the actual SQL projection doesn’t even create a projection with columns from different tables.

Adding an ORDER BY clause to the Query by Example

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

List<PostComment> comments = (List<PostComment>) postCommentRepository
    .findAll(
        Example.of(
            postComment,
            ExampleMatcher.matching()
                .withIgnorePaths(PostComment_.VOTES)
        ),
        Sort.by(Sort.Order.asc(PostComment_.CREATED_ON))
    );

By passing the Sort criteria to the findAll method call, the executing SQL query will now include an ORDER BY clause:

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
JOIN 
    post p2_0 ON p2_0.id=p1_0.post_id
WHERE 
    p1_0.post_id = 1
ORDER BY 
    p1_0.created_on ASC

Combining multiple property matches using Query by Example

Since the entity we pass to the Query by Example query can have multiple properties set, all those properties will be combined to generate the query filtering criteria.

For instance, if we want to get all PostComment entities matching a given Post, with a status value of PENDING, and having a given review pattern, we can write the following Query by Example:

PostComment postComment = new PostComment()
    .setPost(new Post().setId(1L))
    .setStatus(PostComment.Status.PENDING)
    .setReview("Spam");

List<PostComment> comments = (List<PostComment>) postCommentRepository
    .findAll(
        Example.of(
            postComment,
            ExampleMatcher.matching()
                .withIgnorePaths(PostComment_.VOTES)
                .withMatcher(
                    PostComment_.REVIEW, 
                    ExampleMatcher.GenericPropertyMatcher::contains
                )
        ),
        Sort.by(Sort.Order.asc(PostComment_.CREATED_ON))
    );

The withMatcher method allows us to define how the review property is going to be matched when generating the associated filtering criterion in the SQL query WHERE clause.

When executing the above Spring Data Query by Example, Hibernate generates 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
JOIN 
    post p2_0 ON p2_0.id=p1_0.post_id
WHERE 
    p1_0.post_id = 1 AND 
    p1_0.review like '%Spam%' ESCAPE '\' AND 
    p1_0.status = 0
ORDER BY 
    p1_0.created_on ASC

Query by Example and findBy

The QueryByExampleExecutor interface provides a findBy method that allows you to control how much data you are going to fetch when executing an Example query.

For instance, to execute a Top-N query using the Query by Example feature, we can use the findBy method like this:

String reviewPattern = "Awesome";
int pageSize = 10;

PostComment postComment = new PostComment()
    .setPost(new Post().setId(1L))
    .setStatus(PostComment.Status.PENDING)
    .setReview(reviewPattern);

Page<PostComment> comments = postCommentRepository
    .findBy(
        Example.of(
            postComment,
            ExampleMatcher.matching()
                .withIgnorePaths(PostComment_.VOTES)
                .withMatcher(
                    PostComment_.REVIEW, 
                    ExampleMatcher.GenericPropertyMatcher::contains
                )
        ),
        q -> q
            .sortBy(
                Sort
                    .by(PostComment_.CREATED_ON)
                    .ascending()
            )
            .page(Pageable.ofSize(pageSize))
    );

When executing the above Query by Example, Hibernate generates 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
JOIN 
    post p2_0 ON p2_0.id=p1_0.post_id
WHERE 
    p1_0.post_id = 1 AND 
    p1_0.status = 0 AND 
    p1_0.review like '%Awesome%' ESCAPE '\'
ORDER BY 
    p1_0.created_on ASC
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS ONLY

Notice the OFFSET and FETCH FIRST SQL clauses, which provide pagination to our SQL result set.

Cool, 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

The Spring Data JPA Query by Example feature provides an alternative to the other more-common Spring Data JPA querying methods.

By decoupling the filtering criteria from the query processing logic, we can encapsulate the filtering criteria on the client since we don’t require any data access layer API dependency.

However, the current Spring Data JPA Query by Example implementation is rather limited. Not only that it can generate extra JOIN clauses, but, at the moment, it only provides String-based filtering options.

So, until these limitations are addressed, I’d rather stick to Specifications for entity queries and jOOQ for native SQL queries.

Insert details about how the information is going to be processed

DOWNLOAD NOW