Spring Data Query By Example

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:

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:

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:

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:

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:

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:

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

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:

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:

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:

When executing the above Query by Example, Hibernate generates the following SQL query:

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

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 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.