Search Specification | Criteria API in Spring Boot

➤ Java Bean with Examples
➤ How to Code a Game
➤ Array Programs in Java
➤ Java Inline Thread Creation
➤ Java Custom Exception
➤ Hibernate vs JDBC
➤ Object Relational Mapping
➤ Check Oracle DB Size
➤ Check Oracle DB Version
➤ Generation of Computers
➤ XML Pros & Cons
➤ Git Analytics & Its Uses
➤ Top Skills for Cloud Professional
➤ How to Hire Best Candidates
➤ Scrum Master Roles & Work
➤ CyberSecurity in Python
➤ Protect from Cyber-Attack
➤ Solve App Development Challenges
➤ Top Chrome Extensions for Twitch Users
➤ Mistakes That Can Ruin Your Test Metric Program

Search Specification | Criteria API in Spring Boot | Criteria API helps us to create dynamic queries. Let us start with the project setup.

Dependencies:- Spring Web, Lombok, MySQL Driver, Spring Data JPA
application.properties:-

spring.datasource.url=jdbc:mysql://localhost:3306/criteria
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jackson.serialization.FAIL_ON_EMPTY_BEANS=false

Entity Classes:-

@Getter
@Setter
@Entity(name = "address")
public class Address {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "id")
   private Long addressId;

   private String city;
}
@Getter
@Setter
@Entity(name = "subject")
public class Subject {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "id")
   private Long id;

   private String name;

   @JsonBackReference
   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "student_id", referencedColumnName = "id")
   private Student studentId;

}
@Entity
@Getter
@Setter
public class Student {

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

   @Column(unique = true)
   private String name;

   @OneToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "address_id", referencedColumnName = "id")
   private Address address;

   @JsonManagedReference
   @OneToMany(mappedBy = "studentId", 
                cascade = CascadeType.ALL, fetch = FetchType.LAZY)
   private Set<Subject> subjects;
}

Repository:-

public interface StudentRepository extends JpaRepository<Student, Long> {}
public interface AddressRepository extends JpaRepository<Address, Long> {}

Controller:-

@RestController
@RequestMapping("/filter")
public class FilterController {

   @Autowired
   private StudentRepository studentRepository;
}

After running the application, it will create the tables. Before moving ahead, let us first initialize some data. Run it only once.

@Component
public class DataLoader implements CommandLineRunner {

   @Autowired
   private StudentRepository studentRepository;

   @Autowired
   private AddressRepository addressRepository;

   @Override
   public void run(String... args) throws Exception {
      // Create and save addresses
      Address address1 = createAndSaveAddress("New York City");
      Address address2 = createAndSaveAddress("Chicago");
      Address address3 = createAndSaveAddress("Houston");

      // Create and save students with subjects
      createAndSaveStudent("Rocco", address1, "JAVA", "Spring Boot", "JUnit");
      createAndSaveStudent("Jerry", address2, "Angular", "CSS", "Javascript");
      createAndSaveStudent("William", address3, "Git", "Jenkins", "Jira");
   }

   private Address createAndSaveAddress(String city) {
      Address address = new Address();
      address.setCity(city);
      return addressRepository.save(address);
   }

   private void createAndSaveStudent(String studentName, Address address, 
               String... subjects) {
      Student student = new Student();
      student.setName(studentName);
      student.setAddress(address);
      Set<Subject> subjectSet = new HashSet<>();
      for (String subjectName : subjects) {
         Subject subject = new Subject();
         subject.setName(subjectName);
         subject.setStudentId(student);
         subjectSet.add(subject);
      }
      student.setSubjects(subjectSet);
      studentRepository.save(student); // This will cascade and save subjects as well
   }

}

Query Method Examples

For basic searching, we can write query methods. For example, to search for a Student based on a given name we can define the findByName(String name) method in the StudentReposity interface.

public interface StudentRepository extends JpaRepository<Student, Long> {

   // select * from student where name = ?
   Student findByName(String name);

   // aggregation
   // Find students by Address.city
   List<Student> findByAddressCity(String city);

   // aggregation
   // Find students by Subjects.name
   List<Student> findBySubjectsName(String subjectName);
}
@RestController
@RequestMapping("/filter")
public class FilterController {

   @Autowired
   private StudentRepository studentRepository;

   @GetMapping("/{name}")
   public Student getStudentByName(@PathVariable(name = "name") String name) {
      return studentRepository.findByName(name);
   }

   @GetMapping("/city/{CITY}")
   public List<Student> getStudentByCityName(@PathVariable(name = "CITY") String city) {
      return studentRepository.findByAddressCity(city);
   }

   @GetMapping("/subject/{SUB}")
   public List<Student> getStudentBySubjectsName(
              @PathVariable(name = "SUB") String subject) {
      return studentRepository.findBySubjectsName(subject);
   }
}

Search Specification

Now one question may arise: if the query method is already there, why do we need specification? Let us assume we want a new filter based on student name and address city. Similarly, we want to find Students based on id and name with the LIKE operation. So, for every scenario, we have to create a different method or query. Instead of that, we can implement specifications. We can use those specifications everywhere.

Our repository interface should implement JpaSpecificationExecutor<T>, where T represents entity class. For StudentRepository:-

package com.knowprogram.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.knowprogram.demo.entity.Student;

public interface StudentRepository extends JpaRepository<Student, Long>, 
     JpaSpecificationExecutor<Student> {
}

The JpaSpecificationExecutor interface is given as follows:-

public interface JpaSpecificationExecutor<T> {
    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);
    boolean exists(Specification<T> spec);
    long delete(Specification<T> spec);
    <S extends T, R> R findBy(Specification<T> spec, 
            Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction);
}

Example in Controller:-

@PostMapping("/specification")
public List<Student> getStudents() {
    Specification<Student> specification = new Specification<Student>() {

        @Override
        public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, 
             CriteriaBuilder criteriaBuilder) {
            // return criteriaBuilder.equal(root.get("name"), "Rocco");
            return criteriaBuilder.equal(root.get("id"), "2");
        }

    };

    List<Student> all = studentRepository.findAll(specification);
    return all;
}

API can be tested from the URL:- {{url}}/filter/specification where {{url}} represents http://localhost:8080.

Criteria API Basic Example

Let us create DTO:-

package com.knowprogram.demo.dto;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class SearchRequestDto {
   String column;
   String value;
}
package com.knowprogram.demo.dto;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class RequestDto {
   private SearchRequestDto searchRequestDto;
}

Following is the service class. Since we want it for all entities of our project therefore we have used <T>.

package com.knowprogram.demo.service;

import org.springframework.data.jpa.domain.Specification;

import com.knowprogram.demo.dto.SearchRequestDto;

import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;

@Service
public class FilterSpecification<T> {
   public Specification<T> getSearchSpecification(SearchRequestDto 
                                              searchRequestDto) {
      return new Specification<T>() {

         private static final long serialVersionUID = 1L;

         @Override
         public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, 
                              CriteriaBuilder criteriaBuilder) {
            return criteriaBuilder.equal(root.get(searchRequestDto.getColumn()), 
                              searchRequestDto.getValue());
         }

      };
   }

}

In Controller:-

package com.knowprogram.demo.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.knowprogram.demo.dto.RequestDto;
import com.knowprogram.demo.entity.Student;
import com.knowprogram.demo.repository.StudentRepository;
import com.knowprogram.demo.service.FilterSpecification;

@RestController
@RequestMapping("/filter")
public class FilterController {

   @Autowired
   private StudentRepository studentRepository;

   @Autowired
   private FilterSpecification<Student> studentFilterSpecification;

   @PostMapping("/specification")
   public List<Student> getStudents(@RequestBody RequestDto requestDto) {
      Specification<Student> searchSpecification = studentFilterSpecification
            .getSearchSpecification(requestDto.getSearchRequestDto());
      return studentRepository.findAll(searchSpecification);
   }
}

Now, we can test with dynamic columns & values (POST – {{url}}/filter/specification). Let us see some examples:-

Find the Student where id is 3:-

{
    "searchRequestDto": {
        "column": "id",
        "value": "3"
    }
}

Find the Student whose name is “Rocco”:-

{
    "searchRequestDto": {
        "column": "name",
        "value": "Rocco"
    }
}

Let us assume we want to combine multiple columns like filters based on ID and name. For that, we should modify RequestDto to List<SearchRequestDto> as follows:-

public class RequestDto {
   private List<SearchRequestDto> searchRequestDto;
}

In FilterSpecification:-

@Service
public class FilterSpecification<T> {
   public Specification<T> getSearchSpecification(List<SearchRequestDto> 
                       searchRequestDtos) {
      return (root, query, criteriaBuilder) -> {
         List<Predicate> predicates = new ArrayList<>();
         for (SearchRequestDto requestDto : searchRequestDtos) {
            Predicate equal = criteriaBuilder.equal(root.get(requestDto.getColumn()), 
                        requestDto.getValue());
            predicates.add(equal);
         }
         return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
      };
   }
}

No change in the controller. In API call (request body):-

{
    "searchRequestDto": [
        {
            "column": "id",
            "value": "2"
        },
        {
            "column": "name",
            "value": "Gaurav"
        }
    ]
}

Adding List of Criteria Using OR/AND Operator

To work with AND/OR operator dynamically, modify RequestDto as follows:-

@Getter
@Setter
public class RequestDto {
   private List<SearchRequestDto> searchRequestDto;

   private GlobalOperator globalOperator;

   public enum GlobalOperator {
      AND, OR;
   }
}

In FilterSpecification:-

@Service
public class FilterSpecification<T> {
   public Specification<T> getSearchSpecification(List<SearchRequestDto> 
            searchRequestDtos, GlobalOperator globalOperator) {
      return (root, query, criteriaBuilder) -> {
         List<Predicate> predicates = new ArrayList<>();
         for (SearchRequestDto requestDto : searchRequestDtos) {
            Predicate equal = criteriaBuilder.equal(root.get(requestDto.getColumn()), 
                 requestDto.getValue());
            predicates.add(equal);
         }

         if (globalOperator.equals(GlobalOperator.AND)) {
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
         } else {
            return criteriaBuilder.or(predicates.toArray(new Predicate[0]));
         }
      };
   }
}

In Controller:-

@PostMapping("/specification")
public List<Student> getStudents(@RequestBody RequestDto requestDto) {
   Specification<Student> searchSpecification = studentFilterSpecification
      .getSearchSpecification(requestDto.getSearchRequestDto(), 
          requestDto.getGlobalOperator());
   return studentRepository.findAll(searchSpecification);
}

API Call (request body):-

{
    "searchRequestDto": [
        {
            "column": "id",
            "value": "2"
        },
        {
            "column": "name",
            "value": "Rocco"
        }
    ],
    "globalOperator": "OR"
}

EQUAL and LIKE Operator 

@Getter
@Setter
public class SearchRequestDto {
   String column;
   String value;
   Operation operation;

   public enum Operation {
      EQUAL, LIKE;
   }
}
@Service
public class FilterSpecification<T> {
   public Specification<T> getSearchSpecification(List<SearchRequestDto> 
                 searchRequestDtos, GlobalOperator globalOperator) {

      return (root, query, criteriaBuilder) -> {
         List<Predicate> predicates = new ArrayList<>();
         for (SearchRequestDto requestDto : searchRequestDtos) {

            switch (requestDto.getOperation()) {
            case EQUAL:
               Predicate equal = criteriaBuilder.equal(root.get(requestDto.getColumn()),
                           requestDto.getValue());
               predicates.add(equal);
               break;

            case LIKE:
               Predicate like = criteriaBuilder.like(root.get(requestDto.getColumn()),
                     "%" + requestDto.getValue() + "%");
               predicates.add(like);
               break;

            default:
               throw new IllegalStateException("Unexpected Value: " + "");
            }

         }

         if (globalOperator.equals(GlobalOperator.AND)) {
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
         } else {
            return criteriaBuilder.or(predicates.toArray(new Predicate[0]));
         }

      };
   }

}

API Call (request body) example:-

{
    "searchRequestDto": [
        {
            "column": "name",
            "value": "e",
            "operation":"LIKE"
        },
        {
            "column": "name",
            "value": "i",
            "operation":"LIKE"
        }
    ],
    "globalOperator": "OR"
}

IN Operator Example

public class SearchRequestDto {
   String column;
   String value;
   Operation operation;

   public enum Operation {
      EQUAL, LIKE, IN;
   }
}

In the FilterSpecification class, in the getSearchSpecification() method, add one more case statement as follows:-

case IN:
    // assume input is string and seperated by comma:- "name1,name2,name3"
    String[] values = requestDto.getValue().split(",");
    Predicate in = root.get(requestDto.getColumn()).in(Arrays.asList(values));
    predicates.add(in);
    break;

API Call:-

{
    "searchRequestDto": [
        {
            "column": "name",
            "value": "rex,Gaurav",
            "operation": "IN"
        }
    ],
    "globalOperator": "OR"
}

Greater Than And Less Than

public class SearchRequestDto {
   String column;
   String value;
   Operation operation;

   public enum Operation {
      EQUAL, LIKE, IN, GREATER_THAN, LESS_THAN;
   }
}

In the FilterSpecification class, in the getSearchSpecification() method, add two more case statements as follows:-

case GREATER_THAN:
    Predicate greaterThan = criteriaBuilder.greaterThan(root.get(requestDto.getColumn()),
                     requestDto.getValue());
    predicates.add(greaterThan);
    break;

case LESS_THAN:
    Predicate lessThan = criteriaBuilder.lessThan(root.get(requestDto.getColumn()),
                     requestDto.getValue());
    predicates.add(lessThan);
    break;

API Example (request body):-

{
    "searchRequestDto": [
        {
            "column": "id",
            "value": "2",
            "operation": "GREATER_THAN"
        }
    ],
    "globalOperator": "OR"
}

BETWEEN Operation

public class SearchRequestDto {
   String column;
   String value;
   Operation operation;

   public enum Operation {
      EQUAL, LIKE, IN, GREATER_THAN, LESS_THAN, BETWEEN;
   }
}
case BETWEEN:
   // assume input is "10, 20"
   String[] boundryValues = requestDto.getValue().split(",");
   Predicate between = criteriaBuilder.between(root.get(requestDto.getColumn()),
           boundryValues[0],boundryValues[1]);
   // both boundryValues will be inclusive
   predicates.add(between);
   break;

API Call Example:-

{
    "searchRequestDto": [
        {
            "column": "id",
            "value": "2,3",
            "operation": "BETWEEN"
        }
    ],
    "globalOperator": "OR"
}

Join Tables Predicates

Assuming we want to combine Student and Address tables then we can do that as follows. Modify the SearchRequestDto:-

public class SearchRequestDto {
   String column;
   String value;
   Operation operation;
   String joinTable; // added

   public enum Operation {
      EQUAL, LIKE, IN, GREATER_THAN, LESS_THAN, BETWEEN, JOIN;
   }
}

It can be constructed like this:- criteriaBuilder.equal(root.join("joinTableName").get("attribute from join table"), "value");

case JOIN:
   Predicate join = criteriaBuilder.equal(
      root.join(requestDto.getJoinTable()).get(requestDto.getColumn()), 
      requestDto.getValue()
   );
   predicates.add(join);
   break;

Example:-

{
    "searchRequestDto": [
        {
            "column": "city",
            "value": "UK",
            "joinTable":"address",
            "operation": "JOIN"
        }
    ],
    "globalOperator": "OR"
}

Pagination Example With Criteria API

@PostMapping("/specification")
public Page<Student> getStudents(@RequestBody RequestDto requestDto,
      @RequestParam(required = false, defaultValue = "10") Integer pageSize,
      @RequestParam(required = false, defaultValue = "0") Integer pageNo,
      @RequestParam(required = false, defaultValue = "id") String sortBy,
      @RequestParam(required = false, defaultValue = "descending") String sortOrder) {

   Sort sort = sortOrder.equalsIgnoreCase("descending") ? 
         Sort.by(sortBy).descending() : Sort.by(sortBy).ascending();
   Pageable pageable = PageRequest.of(pageNo, pageSize, sort);

   Specification<Student> searchSpecification = studentFilterSpecification
      .getSearchSpecification(requestDto.getSearchRequestDto(), 
         requestDto.getGlobalOperator());
   return studentRepository.findAll(searchSpecification, pageable);
}

Sample API Call:- {{url}}/filter/specification?pageNo=0&pageSize=2&sortBy=name&sortOrder=ascending

Request Body:-

{
    "searchRequestDto": [
        {
            "column": "id",
            "value": "1,3",
            "operation": "BETWEEN"
        }
    ],
    "globalOperator": "OR"
}

Sample response:-

{
    "totalPages": 2,
    "totalElements": 3,
    "size": 2,
    "content": [
        {
            "id": 2,
            "name": "Jerry",
            "address": {},
            "subjects": []
        },
        {
            "id": 1,
            "name": "Rocco",
            "address": {},
            "subjects": []
        }
    ],
    "number": 0,
    "sort": {
        "empty": false,
        "sorted": true,
        "unsorted": false
    },
    "first": true,
    "last": false,
    "pageable": {
        "pageNumber": 0,
        "pageSize": 2,
        "sort": {
            "empty": false,
            "sorted": true,
            "unsorted": false
        },
        "offset": 0,
        "paged": true,
        "unpaged": false
    },
    "numberOfElements": 2,
    "empty": false
}

Summary

Complete Code for DTO classes:-

@Getter
@Setter
public class RequestDto {
   private List<SearchRequestDto> searchRequestDto;

   private GlobalOperator globalOperator;

   public enum GlobalOperator {
      AND, OR;
   }
}
@Getter
@Setter
public class SearchRequestDto {
   String column;
   String value;
   Operation operation;
   String joinTable;

   public enum Operation {
      EQUAL, LIKE, IN, GREATER_THAN, LESS_THAN, BETWEEN, JOIN;
   }
}
public interface StudentRepository extends JpaRepository<Student, Long>, 
     JpaSpecificationExecutor<Student> {
}

Complete Code of FilterSpecification class:-

package com.knowprogram.demo.service;

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

import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import com.knowprogram.demo.dto.RequestDto.GlobalOperator;
import com.knowprogram.demo.dto.SearchRequestDto;

import jakarta.persistence.criteria.Predicate;

@Service
public class FilterSpecification<T> {
   public Specification<T> getSearchSpecification(List<SearchRequestDto> searchRequestDtos,
         GlobalOperator globalOperator) {
      return (root, query, criteriaBuilder) -> {
         List<Predicate> predicates = new ArrayList<>();
         for (SearchRequestDto requestDto : searchRequestDtos) {
            switch (requestDto.getOperation()) {
            case EQUAL:
               Predicate equal = criteriaBuilder.equal(root.get(requestDto.getColumn()), requestDto.getValue());
               predicates.add(equal);
               break;
            case LIKE:
               Predicate like = criteriaBuilder.like(root.get(requestDto.getColumn()),
                     "%" + requestDto.getValue() + "%");
               predicates.add(like);
               break;
            case IN:
               // assume input is string and seperated by comma:- "name1,name2,name3"
               String[] values = requestDto.getValue().split(",");
               Predicate in = root.get(requestDto.getColumn()).in(Arrays.asList(values));
               predicates.add(in);
               break;
            case GREATER_THAN:
               Predicate greaterThan = criteriaBuilder.greaterThan(root.get(requestDto.getColumn()),
                     requestDto.getValue());
               predicates.add(greaterThan);
               break;
            case LESS_THAN:
               Predicate lessThan = criteriaBuilder.lessThan(root.get(requestDto.getColumn()),
                     requestDto.getValue());
               predicates.add(lessThan);
               break;
            case BETWEEN:
               // assume input is "10, 20"
               String[] boundryValues = requestDto.getValue().split(",");
               Predicate between = criteriaBuilder.between(root.get(requestDto.getColumn()), boundryValues[0],
                     boundryValues[1]);
               // both boundryValues will be inclusive
               predicates.add(between);
               break;
            case JOIN:
               Predicate join = criteriaBuilder.equal(
                     root.join(requestDto.getJoinTable()).get(requestDto.getColumn()), requestDto.getValue());
               predicates.add(join);
               break;
            default:
               throw new IllegalStateException("Unexpected Value: " + "");
            }
         }

         if (globalOperator.equals(GlobalOperator.AND)) {
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
         } else {
            return criteriaBuilder.or(predicates.toArray(new Predicate[0]));
         }

      };
   }

}

Controller class:-

@RestController
@RequestMapping("/filter")
public class FilterController {

   @Autowired
   private StudentRepository studentRepository;

   @Autowired
   private FilterSpecification<Student> studentFilterSpecification;

   @PostMapping("/specification")
   public Page<Student> getStudents(@RequestBody RequestDto requestDto,
         @RequestParam(required = false, defaultValue = "10") Integer pageSize,
         @RequestParam(required = false, defaultValue = "0") Integer pageNo,
         @RequestParam(required = false, defaultValue = "id") String sortBy,
         @RequestParam(required = false, defaultValue = "descending") String sortOrder) {
      Sort sort = sortOrder.equalsIgnoreCase("descending") ? Sort.by(sortBy).descending()
            : Sort.by(sortBy).ascending();
      Pageable pageable = PageRequest.of(pageNo, pageSize, sort);

      Specification<Student> searchSpecification = studentFilterSpecification
            .getSearchSpecification(requestDto.getSearchRequestDto(), requestDto.getGlobalOperator());
      return studentRepository.findAll(searchSpecification, pageable);
   }
}

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *