Using the Specification of Spring Data JPA to build database query

The best feature of Spring Data JPA is that it can easily create query SQL by generating query by custom method name. Spring Data JPA provides a Repository programming model. The simplest way is to extend JPA Repository. We get a bunch of common CRUD methods, such as save, findAll, delete, etc. And many database single table query interfaces can be built by using these keywords:

public interface CustomerRepository extends JpaRepository<customer, long> {
  Customer findByEmailAddress(String emailAddress);
  List<customer> findByLastname(String lastname, Sort sort);
  Page<customer> findByFirstname(String firstname, Pageable pageable);
  • The SQL generated by findByEmailAddress queries the data of the Customer table according to the email ﹣ address field
  • findByLastname queries the Customer table data according to the lastname field
  • findByFirstname queries the Customer table data according to the firstname field

All the above queries don't need our handwritten SQL, and the query generator automatically helps us work. For developers, they only need to remember some keywords, such as findBy, delete, and so on. However, sometimes we need to create a more complex query, so we can't use the query builder. You can do this using the Specification described in this section.

>I prefer to write SQL to complete complex queries, but sometimes I use Specification occasionally to complete the task, which is still deep in my heart. No rejection, no blind obedience. There is no best way, only the most appropriate way!

I. use Criteria API to build complex queries

Yes, in addition to specification, we can use the Criteria API to build complex queries, but specification is not easy to use. Let's look at the demand: on the Customer's birthday, we want to send coupons to all long-term customers (over 2 years). How do we retrieve customers?

We have two predicate query conditions:

  • Birthday
  • Long term customers - customers over 2 years.

Here is how to use the JPA 2.0 Criteria API:

LocalDate today = new LocalDate();

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<customer> query = builder.createQuery(Customer.class);
Root<customer> root = query.from(Customer.class);

Predicate hasBirthday = builder.equal(root.get(Customer_.birthday), today);
Predicate isLongTermCustomer = builder.lessThan(root.get(Customer_.createdAt), today.minusYears(2); 

query.where(builder.and(hasBirthday, isLongTermCustomer));
  • The first line, LocalDate, compares the customer's birthday with today's date. em is javax.persistence.EntityManager
  • The next three lines contain the boilerplate code for querying the JPA infrastructure instance of the Customer entity.
  • Then, in the next two lines, we will build the predicate query condition
  • In the last two lines, where is used to connect two predicate query conditions, and the last is used to execute the query.

The main problem with this code is that predicate query conditions are not easy to reuse. You need to set CriteriaBuilder, CriteriaQuery, and Root first. In addition, the readability of the code is poor.

II. specification

In order to define reusable predicate conditions, we introduce the Specification interface.

public interface Specification<t> {
  Predicate toPredicate(Root<t> root, CriteriaQuery query, CriteriaBuilder cb);

When using the Specification interface in combination with Java 8's lambda expression, the code becomes very simple

public CustomerSpecifications {
   //Query criteria: birthday is today
  public static Specification<customer> customerHasBirthday() {
    return (root, query, cb) -&gt;{ 
        return cb.equal(root.get(Customer_.birthday), today);
  //Query criteria: customer creation date is two years ago
  public static Specification<customer> isLongTermCustomer() {
    return (root, query, cb) -&gt;{ 
        return cb.lessThan(root.get(Customer_.createdAt), new LocalDate.minusYears(2));

You can now do the following from the CustomerRepository:


We create reusable predicate query conditions that can be executed separately, and we can use these separate predicates together to meet our business needs. We can use and( )And or( )Connection specification.


It reads fluently, improves readability, and provides more flexibility than using the JPA Criteria API.

Looking forward to your attention

Keywords: Programming SQL Spring Database

Added by yuws on Tue, 10 Dec 2019 14:00:20 +0200