Dynamic Queries with Spring Data JPA Specifications

Spring Data JPA allows us to connect with different relational databases through a common interface to create JPA (Java Persistence API) based repositories. We can easily create database queries by using method names, the @Query annotation, and named queries to retrieve data from the database.

The Spring Data repository abstraction is a powerful feature that hides the store-specific implementation details and allows us to write business logic code at a higher abstraction level. All we need to do is only extend any of the Spring Data repositories to take full advantage of this feature. A typical repository interface looks something like the below:

public interface DogRepository extends JpaRepository<Dog, Long> {

    Dog findByName(String name);
    
    List<Dog> findByColor(String color, Sort sort);

    Page<Dog> findByAgeGreaterThan(int age, Pageable pageable)
}

The first method simply finds and returns a single dog with a given name. The second method returns a list of dogs with a given color and applies the dynamic sorting to the results through the given Sort object. Finally, the last method returns a Page of dogs older than the given age.

Although the above approach looks really convenient (you don't need to write a single line of implementation code to execute queries), it has at least two drawbacks:

1. The number of query methods is fixed as we cannot dynamically define a new one on runtime. As the application grows, these query methods increase quickly, making it difficult to maintain the persistence layer.
2. Each query method defines a fixed set of criteria. We have predefined the number and type of parameters that cannot be changed dynamically on runtime.

For larger and complex applications, we need a robust and flexible query generation strategy to handle different types of use cases. That's where the Spring Data JPA specifications come in.

Spring Data JPA Specifications

Spring Data JPA specifications allow us to create dynamic database queries by using the JPA Criteria API. It defines a specification as a predicate over an entity. Here is what the Specification interface looks like:

public interface Specification<T> extends Serializable {

    @Nullable
    Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, 
                CriteriaBuilder criteriaBuilder);
    
    // ... other methods
}

As you can see above, there is only one abstract toPredicate() method that returns an instance of Predicate. In the following sections, you'll learn how to use this interface to write dynamic queries for different use cases.

Create an Application

Let us start with creating a simple Spring Boot application with the Spring Data JPA and H2 in-memory database. Our application has just one Movie entity class, as shown below:

Movie.java

package com.attacomsian.jpa.domains;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;

@Entity
public class Movie implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String title;
    private String genre;
    private double rating;
    private double watchTime;
    private int releaseYear;

    public Movie() {
    }

    public Movie(String title, String genre, double rating, double watchTime, int releaseYear) {
        this.title = title;
        this.genre = genre;
        this.rating = rating;
        this.watchTime = watchTime;
        this.releaseYear = releaseYear;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}

The next step is to create a repository interface called MovieRepository to retrieve data from the database. To use specifications, we also need to extend our repository interface from the JpaSpecificationExecutor interface. This interface provides methods to execute specifications.

Here is what our repository interface looks like:

MovieRepository.java

package com.attacomsian.jpa.repositories;

import com.attacomsian.jpa.domains.Movie;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.CrudRepository;

public interface MovieRepository extends CrudRepository<Movie, Long>,
        JpaSpecificationExecutor<Movie> {

    // TODO: add queries
}

Create Specifications

Let us start with the most fascinating part of this article, creating specifications to execute dynamic queries for searching movies in the database.

The first step is to create a simple enum class that defines different search operations:

SearchOperation.java

package com.attacomsian.jpa.repositories.specs;

public enum SearchOperation {
    GREATER_THAN,
    LESS_THAN,
    GREATER_THAN_EQUAL,
    LESS_THAN_EQUAL,
    NOT_EQUAL,
    EQUAL,
    MATCH,
    MATCH_START,
    MATCH_END,
    IN,
    NOT_IN
}

Next, create the SearchCriteria class that represents a single search criterion:

SearchCriteria.java

package com.attacomsian.jpa.repositories.specs;

public class SearchCriteria {
    private String key;
    private Object value;
    private SearchOperation operation;

    public SearchCriteria() {
    }

    public SearchCriteria(String key, Object value, SearchOperation operation) {
        this.key = key;
        this.value = value;
        this.operation = operation;
    }

    // getters and setters, toString(), ... (omitted for brevity)
}

As you can see above, the SearchCriteria class holds a basic representation of a single constraint:

• key — Represents the entity field name, i.e. title, genre, etc.
• value — Represents the parameter value, i.e. Troy, Action, etc.
• operation — Indicates the search operation: equality, match, comparison, etc.

Finally, let us create a MovieSpecification class that extends the Specification interface and provides an implementation for the toPredicate() method:

MovieSpecification.java

package com.attacomsian.jpa.repositories.specs;

import com.attacomsian.jpa.domains.Movie;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

public class MovieSpecification implements Specification<Movie> {

    private List<SearchCriteria> list;

    public MovieSpecification() {
        this.list = new ArrayList<>();
    }

    public void add(SearchCriteria criteria) {
        list.add(criteria);
    }

    @Override
    public Predicate toPredicate(Root<Movie> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        //create a new predicate list
        List<Predicate> predicates = new ArrayList<>();

        //add criteria to predicate
        for (SearchCriteria criteria : list) {
            if (criteria.getOperation().equals(SearchOperation.GREATER_THAN)) {
                predicates.add(builder.greaterThan(
                        root.get(criteria.getKey()), criteria.getValue().toString()));
            } else if (criteria.getOperation().equals(SearchOperation.LESS_THAN)) {
                predicates.add(builder.lessThan(
                        root.get(criteria.getKey()), criteria.getValue().toString()));
            } else if (criteria.getOperation().equals(SearchOperation.GREATER_THAN_EQUAL)) {
                predicates.add(builder.greaterThanOrEqualTo(
                        root.get(criteria.getKey()), criteria.getValue().toString()));
            } else if (criteria.getOperation().equals(SearchOperation.LESS_THAN_EQUAL)) {
                predicates.add(builder.lessThanOrEqualTo(
                        root.get(criteria.getKey()), criteria.getValue().toString()));
            } else if (criteria.getOperation().equals(SearchOperation.NOT_EQUAL)) {
                predicates.add(builder.notEqual(
                        root.get(criteria.getKey()), criteria.getValue()));
            } else if (criteria.getOperation().equals(SearchOperation.EQUAL)) {
                predicates.add(builder.equal(
                        root.get(criteria.getKey()), criteria.getValue()));
            } else if (criteria.getOperation().equals(SearchOperation.MATCH)) {
                predicates.add(builder.like(
                        builder.lower(root.get(criteria.getKey())),
                        "%" + criteria.getValue().toString().toLowerCase() + "%"));
            } else if (criteria.getOperation().equals(SearchOperation.MATCH_END)) {
                predicates.add(builder.like(
                        builder.lower(root.get(criteria.getKey())),
                        criteria.getValue().toString().toLowerCase() + "%"));
            } else if (criteria.getOperation().equals(SearchOperation.MATCH_START)) {
                predicates.add(builder.like(
                        builder.lower(root.get(criteria.getKey())),
                        "%" + criteria.getValue().toString().toLowerCase()));
            } else if (criteria.getOperation().equals(SearchOperation.IN)) {
                predicates.add(builder.in(root.get(criteria.getKey())).value(criteria.getValue()));
            } else if (criteria.getOperation().equals(SearchOperation.NOT_IN)) {
                predicates.add(builder.not(root.get(criteria.getKey())).in(criteria.getValue()));
            }
        }

        return builder.and(predicates.toArray(new Predicate[0]));
    }
}

The MovieSpecification class allows you to combine multiple specifications for filtering the movies using numerous constraints. Using this class, you can generate different kinds of database queries dynamically.

Test the Application

Now is the time to create the main application class to test specifications by writing dynamic search queries:

Application.java

package com.attacomsian.jpa;

import com.attacomsian.jpa.domains.Movie;
import com.attacomsian.jpa.repositories.MovieRepository;
import com.attacomsian.jpa.repositories.specs.MovieSpecification;
import com.attacomsian.jpa.repositories.specs.SearchCriteria;
import com.attacomsian.jpa.repositories.specs.SearchOperation;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import java.util.Arrays;
import java.util.List;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    public CommandLineRunner specificationsDemo(MovieRepository movieRepository) {
        return args -> {

            // create new movies
            movieRepository.saveAll(Arrays.asList(
                    new Movie("Troy", "Drama", 7.2, 196, 2004),
                    new Movie("The Godfather", "Crime", 9.2, 178, 1972),
                    new Movie("Invictus", "Sport", 7.3, 135, 2009),
                    new Movie("Black Panther", "Action", 7.3, 135, 2018),
                    new Movie("Joker", "Drama", 8.9, 122, 2018),
                    new Movie("Iron Man", "Action", 8.9, 126, 2008)
            ));

            // search movies by `genre`
            MovieSpecification msGenre = new MovieSpecification();
            msGenre.add(new SearchCriteria("genre", "Action", SearchOperation.EQUAL));
            List<Movie> msGenreList = movieRepository.findAll(msGenre);
            msGenreList.forEach(System.out::println);

            // search movies by `title` and `rating` > 7
            MovieSpecification msTitleRating = new MovieSpecification();
            msTitleRating.add(new SearchCriteria("title", "black", SearchOperation.MATCH));
            msTitleRating.add(new SearchCriteria("rating", 7, SearchOperation.GREATER_THAN));
            List<Movie> msTitleRatingList = movieRepository.findAll(msTitleRating);
            msTitleRatingList.forEach(System.out::println);

            // search movies by release year < 2010 and rating > 8
            MovieSpecification msYearRating = new MovieSpecification();
            msYearRating.add(new SearchCriteria("releaseYear", 2010, SearchOperation.LESS_THAN));
            msYearRating.add(new SearchCriteria("rating", 8, SearchOperation.GREATER_THAN));
            List<Movie> msYearRatingList = movieRepository.findAll(msYearRating);
            msYearRatingList.forEach(System.out::println);

            // search movies by watch time >= 150 and sort by `title`
            MovieSpecification msWatchTime = new MovieSpecification();
            msWatchTime.add(new SearchCriteria("watchTime", 150, SearchOperation.GREATER_THAN_EQUAL));
            List<Movie> msWatchTimeList = movieRepository.findAll(msWatchTime, Sort.by("title"));
            msWatchTimeList.forEach(System.out::println);

            // search movies by `title` <> 'white' and paginate results
            MovieSpecification msTitle = new MovieSpecification();
            msTitle.add(new SearchCriteria("title", "white", SearchOperation.NOT_EQUAL));

            Pageable pageable = PageRequest.of(0, 3, Sort.by("releaseYear").descending());
            Page<Movie> msTitleList = movieRepository.findAll(msTitle, pageable);

            msTitleList.forEach(System.out::println);
        };
    }
}

The above example demonstrates how to use specifications to dynamically generate different database queries. You can also apply sorting and pagination to query results in the same way as you apply to other query generation strategies.

You can even combine multiple Specification objects to create a new one on the fly. To do so, Spring Data JPA's Specification interface provides and() and or() methods to concatenate different specifications. There is also a where() method that makes the expression more readable.

Here is an example that combines the above msTitle and msYearRating specifications to create a new one:

// combine using `AND` operator
List<Movie> movies = movieRepository.findAll(Specification.where(msTitle).and(msYearRating));

// combine using `OR` operator
List<Movie> movies = movieRepository.findAll(Specification.where(msTitle).or(msYearRating));

Source Code: Download the complete source code from GitHub available under MIT license.

Conclusion

That's all. In this article, you learned how to use Spring Data specifications with JPA Criteria API to dynamically generate database queries.

Specifications provide a robust and flexible approach to creating database queries to handle complex use cases. This tutorial covers almost all basic operations that can be used to implement a powerful search feature.

Further Reading

To learn more about Spring Data JPA, check out the following articles:

• Getting Started with Spring Data JPA
• Spring Data JPA with H2 DataBase and Spring Boot
• Accessing Data with Spring Data JPA and MySQL
• Derived Query Methods in Spring Data JPA
• Spring Data JPA Custom Queries using @Query Annotation
• How to Use Spring Data JPA Named Queries
• Sorting Query Results in Spring Data JPA
• Pagination with Spring Data JPA
• Spring Data JPA One To One Relationship Mapping Example
• Spring Data JPA One To Many Relationship Mapping Example
• Spring Data JPA Many To Many Relationship Mapping Example
• Spring Data JPA Composite Primary Key Mapping Example
• Introduction to Spring Data JPA Repositories
• Spring Data JPA Auditing using Spring Boot and MySQL

✌️ Like this article? Follow me on Twitter and LinkedIn. You can also subscribe to RSS Feed.