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

Orlando L Otero | Nov 21, 2020

| api, criteria api, hibernate, java, jpa, restful, spring boot, spring data jpa, sql

| 9 min read

| 0 Comments

This post has been featured on https://www.baeldung.com/java-weekly-361.

1. INTRODUCTION

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.

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’

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:

DVD Rental Entity Relationship Diagram

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

3. JPA MAPPINGS

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.

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.

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;
}

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

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.

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.

Stay tuned, I’ll cover the impact of having literal values and IN predicates in your Spring Boot applications when using JPQL statements or Criteria API queries in another blog post.

9. CONCLUSION

This tutorial covers 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.

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.

10. SOURCE CODE

Accompanying source code for this blog post can be found at:

• https://bitbucket.org/asimio/spring-data-jpa-specification-dynamic-queries

11. REFERENCES

• https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods