Writing dynamic SQL queries using Spring Data JPA Specification and Criteria API

1. OVERVIEW

A dynamic SQL query refers to building a query on the fly before executing it. Not just replacing query parameters by their name or position, but also including a variable number of columns in the WHERE clause, and even joining tables based on specific conditions.

How would you implement a RESTful endpoint like /api/films?minRentalRate=0.5&maxRentalRate=4.99 where you need to retrieve films from a relational database?

You could take advantage of Spring Data JPA support for:

@Query-annotated method.
Named queries.
Query methods. Naming a method findByRentalRateBetween() in your Film repository interface.

Let’s say later on you also need to search films based on movie categories, for instance, comedy, action, horror, etc.
You might think a couple of new repository methods would do the work. One for the new category filter and another to combine both, the rental rate range filter with the category filter. You would also need to find out which repository method to call based on the presence of the request parameters.

You soon realize this approach is error-prone and it doesn’t scale as the number of request parameters to search films increases.

This blog post covers generating dynamic SQL queries using Spring Data JPA Specification and Criteria API including joining tables to filter and search data.

Tip: Read on to learn:

How to write dynamic SQL queries using Spring Data JPA repositories and EntityManager.
How to write dynamic SQL queries using Spring Data JPA repositories and Querydsl.

To see the dynamic queries generation in action, let’s implement this endpoint:

/api/films?category=Action&category=Comedy&category=Horror&minRentalRate=0.5&maxRentalRate=4.99&releaseYear=2006

where all request parameters are optional. The request parameters semantic will be:

category IN (‘Comedy’, ‘Horror’, …) AND

rentalRate BETWEEN 0.5 AND 4.99 AND

releaseYear = ‘2006’

Tip: Read on to learn how to add pagination links to RESTful responses using Spring HATEOAS.

This is a sequence diagram to better understand the interactions between the classes included in this tutorial.

Retrieve films Sequence Diagram

Let’s discuss this flow following a bottom-up approach. First the relevant relational database tables, next the JPA entities, then the Spring Data JPA-based repositories, and so on up to the service and REST controller.

2. DATABASE TABLES

This is a partial ER diagram for the db_dvdrental relational database used in this post:

Entity Relationship Diagram - db_dvdrental DVD Rental Entity Relationship Diagram

film, film_category, and category are the relevant tables I’ll cover in this post.

Tip: Read on to learn how to document your relational database using SchemaSpy.

3. JPA ENTITIES

Let’s generate the JPA entities mapped to these tables using a Maven plugin:

Film.java:

@Entity
@Table(name = "film", schema = "public")
public class Film implements Serializable {

  @Column(name = "rental_rate", nullable = false, precision = 4)
  private BigDecimal rentalRate;

  @Column(name = "release_year")
  @Lob
  private String releaseYear;

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "film")
  private Set<FilmCategory> filmCategories = new HashSet<FilmCategory>(0);
...
}

FilmCategory.java:

@Entity
@Table(name = "film_category", schema = "public")
public class FilmCategory implements Serializable {

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "film_id", nullable = false, insertable = false, updatable = false)
  private Film film;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "category_id", nullable = false, insertable = false, updatable = false)
  private Category category;
...
}

Category.java:

@Entity
@Table(name = "category", schema = "public")
public class Category implements Serializable {

  @Column(name = "name", nullable = false, length = 25)
  private String name;

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "category")
  private Set<FilmCategory> filmCategories;
...
}

These are the JPA entities relevant to this post with the attributes that we will search on.

Notice the @OneToMany, @ManyToOne entity associations are LAZY loaded.

Important: EAGER fetching associations are simple to use but this strategy might load irrelevant data to your business use case. You’ll likely end up including many JOIN clauses in a SELECT statement impacting your application performance.

Tip: Read on if you are interested in learning how to prevent the N+1 select problem and LazyInitializationException using Spring Data JPA EntityGraph.

Film_.java:

@StaticMetamodel(Film.class)
public class Film_ {

  public static volatile SingularAttribute<Film, BigDecimal> rentalRate;
  public static volatile SingularAttribute<Film, String> releaseYear;
  public static volatile SetAttribute<Film, FilmCategory> filmCategories;
}

FilmCategory_.java:

@StaticMetamodel(FilmCategory.class)
public class FilmCategory_ {

  public static volatile SingularAttribute<FilmCategory, Category> category;
}

Category_.java:

@StaticMetamodel(Category.class)
public class Category_ {

  public static volatile SingularAttribute<Category, String> name;
}

The JPA static metamodel helps you to create type-safe queries using JPA Criteria API or Spring Data JPA @EntityGraph. They are named after their counterpart JPA entity with the _ symbol as a suffix. You will use them when implementing your Spring Data JPA Specifications.

Note: Stay tuned and sign up to the newsletter. I’ll cover how to generate and use the JPA Metamodel Classes in another blog post.

4. SPRING DATA JPA REPOSITORIES

FilmDao.java:

@Repository
public interface FilmDao extends JpaRepository<Film, Integer>, JpaSpecificationExecutor<Film> {

  @EntityGraph(
    type = EntityGraphType.FETCH,
    attributePaths = {
      "language", 
      "filmActors", "filmActors.actor"
    }
  )
  List<Film> findAll(@Nullable Specification<Film> spec);
...
}

This is how your Spring Data JPA repository with support for JPA Specification would look like.

Extending from JpaRepository allows your application repositories to implement CRUD, QueryByExample, pagination, and sorting operations.

Extending from JpaSpecificationExecutor provides support for Specification based on the JPA Criteria API.

You can execute any of these methods:

Optional<T> findOne(Specification<T> spec)

List<T> findAll(Specification<T> spec)

Page<T> findAll(Specification<T> spec, Pageable pageable)

List<T> findAll(Specification<T> spec, Sort sort)

long count(Specification<T> spec)

without the need to implement anything. Simple, isn’t?

The reason why FilmDao repository defined findAll(Specification<T> spec) was to include the @EntityGraph annotation to prevent N+1 SELECT queries.

Let’s now see how the service layer uses this repository.

5. SERVICE CLASS

DefaultDvdRentalService.java:

@Service
@Transactional(readOnly = true)
public class DefaultDvdRentalService implements DvdRentalService {

  @Override
  public List<Film> retrieveFilms(FilmSearchCriteria searchCriteria) {
    Specification<Film> filmSpecifications = FilmSpecifications.createFilmSpecifications(searchCriteria);
    return this.filmDao.findAll(filmSpecifications);
  }
...
}

retrieveFilms() builds the Film Specifications used by the FilmDao repository.

FilmSearchCriteria.java:

public class FilmSearchCriteria {

  private Optional<BigDecimal> minRentalRate;
  private Optional<BigDecimal> maxRentalRate;
  private Optional<Long> releaseYear;
  private Set<String> categories;

  // Getters, Setters
}

FilmSearchCriteria is a wrapper class to hold the request parameters passed in the request endpoint.

6. SPRING DATA JPA SPECIFICATIONS

The Specification interface looks like:

public interface Specification<T> extends Serializable {
  Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
...
}

Let’s implement some using Java 8 Lambda expressions.

FilmSpecifications.java:

public final class FilmSpecifications {

  public static Specification<Film> createFilmSpecifications(FilmSearchCriteria searchCriteria) {
    return rentalRateBetween(searchCriteria.getMinRentalRate(), searchCriteria.getMaxRentalRate())
      .and(releaseYearEqualTo(searchCriteria.getReleaseYear()))
      .and(categoryIn(searchCriteria.getCategories()));
  }

  public static Specification<Film> rentalRateBetween(Optional<BigDecimal> minRate, Optional<BigDecimal> maxRate) {
    return (root, query, builder) -> {
      return minRate.map(min -> {
        return maxRate.map(max -> builder.between(root.get(Film_.rentalRate), min, max)
          ).orElse(null);
      }).orElse(null);
    };
  }

  public static Specification<Film> releaseYearEqualTo(Optional<Long> releaseYear) {
    return (root, query, builder) -> {
      return releaseYear.map(relYear -> builder.equal(root.get(Film_.releaseYear), String.valueOf(relYear))
        ).orElse(null);
    };
  }

  public static Specification<Film> categoryIn(Set<String> categories) {
    if (CollectionUtils.isEmpty(categories)) {
      return null;
    }
    return (root, query, builder) -> {
      Join<Film, FilmCategory> filmCategoryJoin = root.join(Film_.filmCategories);
      Join<FilmCategory, Category> categoryJoin = filmCategoryJoin.join(FilmCategory_.category);
      return categoryJoin.get(Category_.name).in(categories);
    };
  }
}

createFilmSpecifications() combines the other three Specifications using AND semantic as defined earlier. There is no need to check for null when combining Specifications.

rentalRateBetween() adds a BETWEEN condition to the WHERE clause if the method parameters are present.
releaseYearEqualTo() adds an EQUAL condition to the WHERE clause if the method parameter is present.
categoryIn() adds two tables JOIN clauses and an IN condition to the WHERE clause.

Notice how these Specifications use JPA static metamodel classes Film_, FilmCategory_, and Category_ to implement type-safe queries.

If there comes a need to add another request parameter to filter movies, all you have to do is wrap the request parameter value in the FilmSearchCriteria instance, and add a new JPA Specification to FilmSpecifications.

7. CONTROLLER CLASS

FilmController.java:

@RestController
@RequestMapping(value = "/api/films", produces = MediaType.APPLICATION_JSON_VALUE)
public class FilmController {

  @GetMapping(path = "")
  public ResponseEntity<List<FilmResource>> retrieveFilms(

    @RequestParam(required = false)
    Optional<BigDecimal> minRentalRate,

    @RequestParam(required = false)
    Optional<BigDecimal> maxRentalRate,

    @RequestParam(required = false)
    Optional<Long> releaseYear,

    @RequestParam(name="category", required = false)
    Set<String> categories) {

    FilmSearchCriteria searchCriteria = FilmSearchCriteria.builder()
      .minRentalRate(minRentalRate)
      .maxRentalRate(maxRentalRate)
      .releaseYear(releaseYear)
      .categories(categories)
      .build();

    List<Film> films = this.dvdRentalService.retrieveFilms(searchCriteria);
    List<FilmResource> result = FilmResourceMapper.INSTANCE.map(films);
    return new ResponseEntity<>(result, HttpStatus.OK);
  }
...
}

The controller does a few things:

Wraps the request parameters in an instance of FilmSearchCriteria. This code is easier to maintain than changing the retrieveFilms() method signature in the interface and implementation classes, and fixing the corresponding unit tests every time your team needs to add a new parameter to search on.
Executes the business logic via retrieveFilms() method.
Maps the internal model to the external interface.

Warning:
Don’t expose your internal domain as your external interface. It might lead to API compatibility issues.
The application’s internal domain might evolve at a different pace than the contracted API responses.
Map or transform the internal domain and send the result as the API response.

8. DYNAMIC QUERIES IN ACTION

Let’s send a couple of requests to the /api/films endpoint.

curl http://localhost:8080/api/films?minRentalRate=0.5&maxRentalRate=4.99

Hibernate: 
  select
    film0_.film_id as film_id1_8_0_,
    language1_.language_id as language1_13_1_,
    filmactors2_.actor_id as actor_id1_9_2_,
    filmactors2_.film_id as film_id2_9_2_,
    actor3_.actor_id as actor_id1_0_3_,
    film0_.description as descript2_8_0_,
    film0_.fulltext as fulltext3_8_0_,
    film0_.language_id as languag13_8_0_,
    film0_.last_update as last_upd4_8_0_,
    film0_.length as length5_8_0_,
    film0_.rating as rating6_8_0_,
    film0_.release_year as release_7_8_0_,
    film0_.rental_duration as rental_d8_8_0_,
    film0_.rental_rate as rental_r9_8_0_,
    film0_.replacement_cost as replace10_8_0_,
    film0_.special_features as special11_8_0_,
    film0_.title as title12_8_0_,
    language1_.last_update as last_upd2_13_1_,
    language1_.name as name3_13_1_,
    filmactors2_.last_update as last_upd3_9_2_,
    filmactors2_.film_id as film_id2_9_0__,
    filmactors2_.actor_id as actor_id1_9_0__,
    actor3_.first_name as first_na2_0_3_,
    actor3_.last_name as last_nam3_0_3_,
    actor3_.last_update as last_upd4_0_3_ 
  from
    public.film film0_ 
  left outer join
    public.language language1_ 
      on film0_.language_id=language1_.language_id 
  left outer join
    public.film_actor filmactors2_ 
      on film0_.film_id=filmactors2_.film_id 
  left outer join
    public.actor actor3_ 
      on filmactors2_.actor_id=actor3_.actor_id 
  where
    film0_.rental_rate between 0.5 and 4.99

This request included the minRentalRate and maxRentalRate parameters only.

Notice it generated only one predicate to the WHERE clause:

film0_.rental_rate between 0.5 and 4.99

Let’s now send a second request with all the parameters.

curl http://localhost:8080/api/films/?category=Action&category=Comedy&category=Horror&minRentalRate=0.99&maxRentalRate=4.99&releaseYear=2005

Hibernate: 
  select
    film0_.film_id as film_id1_8_0_,
    language3_.language_id as language1_13_1_,
    filmactors4_.actor_id as actor_id1_9_2_,
    filmactors4_.film_id as film_id2_9_2_,
    actor5_.actor_id as actor_id1_0_3_,
    film0_.description as descript2_8_0_,
    film0_.fulltext as fulltext3_8_0_,
    film0_.language_id as languag13_8_0_,
    film0_.last_update as last_upd4_8_0_,
    film0_.length as length5_8_0_,
    film0_.rating as rating6_8_0_,
    film0_.release_year as release_7_8_0_,
    film0_.rental_duration as rental_d8_8_0_,
    film0_.rental_rate as rental_r9_8_0_,
    film0_.replacement_cost as replace10_8_0_,
    film0_.special_features as special11_8_0_,
    film0_.title as title12_8_0_,
    language3_.last_update as last_upd2_13_1_,
    language3_.name as name3_13_1_,
    filmactors4_.last_update as last_upd3_9_2_,
    filmactors4_.film_id as film_id2_9_0__,
    filmactors4_.actor_id as actor_id1_9_0__,
    actor5_.first_name as first_na2_0_3_,
    actor5_.last_name as last_nam3_0_3_,
    actor5_.last_update as last_upd4_0_3_ 
  from
    public.film film0_ 
  inner join
    public.film_category filmcatego1_ 
      on film0_.film_id=filmcatego1_.film_id 
  inner join
    public.category category2_ 
      on filmcatego1_.category_id=category2_.category_id 
  left outer join
    public.language language3_ 
      on film0_.language_id=language3_.language_id 
  left outer join
    public.film_actor filmactors4_ 
      on film0_.film_id=filmactors4_.film_id 
  left outer join
    public.actor actor5_ 
      on filmactors4_.actor_id=actor5_.actor_id 
  where
    (
      film0_.rental_rate between 0.99 and 4.99
    ) 
    and film0_.release_year=? 
    and (
      category2_.name in (
        ? , ? , ?
      )
    )
2020-11-17 23:37:27.420 TRACE 14177 --- [nio-8080-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [CLOB] - [2005]
2020-11-17 23:37:27.421 TRACE 14177 --- [nio-8080-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [Action]
2020-11-17 23:37:27.421 TRACE 14177 --- [nio-8080-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [Comedy]
2020-11-17 23:37:27.421 TRACE 14177 --- [nio-8080-exec-5] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [Horror]

It now generates three predicates to the WHERE clause:

  where
    (
      film0_.rental_rate between 0.99 and 4.99
    ) 
    and film0_.release_year=? 
    and (
      category2_.name in (
        ? , ? , ?
      )
    )

And it also generates two more JOIN clauses so that it can search based on movie category:

  inner join
    public.film_category filmcatego1_ 
      on film0_.film_id=filmcatego1_.film_id 
  inner join
    public.category category2_ 
      on filmcatego1_.category_id=category2_.category_id

Different calls to findAll(Specification<T> spec) method generate and execute different SQL statements depending on which and how many Spring Data JPA Specifications were instantiated.

Another thing I would like to point out is how Hibernate parametrizes String-like film0_.release_year and category2_.name. This a good approach to prevent SQL injection attacks. film0_.rental_rate used literal values because it’s numeric.

Tip: Read on to learn how Spring Data JPA Specification, Criteria, or JPQL queries might affect your Spring Boot applications performance.

9. CONCLUSION

This tutorial covered each component in your Spring Boot application to write flexible, and maintainable code when implementing dynamic queries using Spring Data JPA Specification.

Combining Spring Data JPA Specification and Criteria API allow you to write type-safe dynamic queries to retrieve data from a relational database.

One drawback is that implementing JPA Criteria API queries is not as trivial as when using SQL.

Tip: Read on to learn:

How to write dynamic SQL queries using Spring Data JPA repositories and EntityManager.
How to write dynamic SQL queries using Spring Data JPA repositories and Querydsl.

Thanks for reading and as always, feedback is very much appreciated. If you found this post helpful and would like to receive updates when content like this gets published, sign up to the newsletter.