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.

Create a Spring starter project with the following dependencies:- Spring Web, Lombok, MySQL Driver, and Spring Data JPA. See the complete code on GitHub.

In 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
@Table(name = "address")
public class Address {

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

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

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

   private String name;

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

}
@Entity
@Getter
@Setter
@Table(name = "student")
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

In the previous example, we had hardcoded the column name and search value. But we can it dynamic and then we can pass column names and values to search based on any column. For that let us create DTO:-

package com.knowprogram.demo.dto;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class SearchRequestDto {
    private String column;
    private 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"
    }
}

List of Criteria using AND

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 overload the existing getSearchSpecification() method and let us use the lambda expression to create new Specification.

@Service
public class FilterSpecification<T> {

   // getSearchSpecification(SearchRequestDto searchRequestDtos) method

   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": "Jerry"
        }
    ]
}

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

Now let us add functionality for the EQUAl and LIKE operator. For each column, we can map the operation.

@Getter
@Setter
public class SearchRequestDto {
    private String column;
    private String value;

    private 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 IllegalArgumentException("Unexpected Value for Operation: " 
                        + requestDto.getOperation());
            }

         }

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

Another example:-

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

Note that the LIKE operator is only applicable for the String type of value but not for the integer, double. But the current code allows specifying LIKE for the integer also, and it leads to runtime error. To resolve this issue we can add a type check to ensure that the LIKE operator is only applied to String values.

case LIKE:
    if (root.get(requestDto.getColumn()).getJavaType() == String.class) {
        Predicate like = criteriaBuilder.like(root.get(requestDto.getColumn()), 
                 "%" + requestDto.getValue() + "%");
        predicates.add(like);
    } else {
        throw new IllegalArgumentException("LIKE operator is only applicable to String values");
    }
    break;

IN Operator Example

Add IN operation in the Operation enum of SearchRequestDto class:-

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 separated 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": "Jerry,William",
            "operation": "IN"
        }
    ],
    "globalOperator": "OR"
}

Greater Than And Less Than

Let us add support for greater than and less than operations in the Operation enum of SearchRequestDto class:-

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

Let us add support for between operations in the Operation enum of SearchRequestDto class:-

public enum Operation {
    EQUAL, LIKE, IN, GREATER_THAN, LESS_THAN, BETWEEN;
}
case BETWEEN:
   // assume input is "10, 20"
   String[] boundryValues = requestDto.getValue().split(",");
   // both boundryValues will be inclusive
   Predicate between = criteriaBuilder.between(
        root.get(requestDto.getColumn()), boundryValues[0], boundryValues[1]);
   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; // for joining the tables

   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": "Chicago",
            "joinTable":"address",
            "operation": "JOIN"
        }
    ],
    "globalOperator": "OR"
}

Pagination Example With Criteria API

To add pagination support we don’t need to modify anything in the StudentFilterSpecification class. Instead while calling the findAll() method on StudentRepository Interface we have to pass the Pageable object. See more about Pageable in Spring Data JPA.

@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 = "DESC") String sortOrder) {

   Sort sort = sortOrder.equalsIgnoreCase("DESC") ? 
         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=ASC

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
}

Reference:- https://www.youtube.com/playlist?list=PLoyb0HJlmv_lvsJv02JKe7hz45oB3qlgX

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 *