SpringBoot-JPA-DynamicQuery-Specification

A practical example of building dynamic search, sorting, and pagination with Spring Data JPA Specification.

The project demonstrates how to keep query conditions outside the repository by using reusable specification classes. It includes three API styles:

• Dynamic filtering that returns a list
• Dynamic filtering, sorting, and pagination through a GET endpoint
• Dynamic filtering, sorting, and pagination through a POST request body

Features

• Dynamic query conditions with JpaSpecificationExecutor
• Optional filters for employee and department data
• Entity relationship filtering with JPA Criteria API
• Pagination with configurable page and page size
• Ascending and descending sorting
• Custom mapping between API sort fields and entity properties
• GET and POST search examples
• Automatic sample-data generation with Java Faker

Dependencies

• Spring Data JPA
• Faker (generate fake data )

Sample tables

• Employee
• Department

Domain Model

The sample application contains two entities.

Department

• id
• deptNo
• deptName
• createDate
• employees

Employee

• id
• deptNo
• firstName
• midName
• lastName
• phone
• birthDate
• department

Each employee belongs to one department. The relationship is joined through DEPT_NO.

How It Works

Spring Data JPA normally provides predefined repository methods such as:

findByFirstName(...)
findByLastName(...)
findByDepartmentDeptNo(...)

This approach becomes difficult to maintain when many filters are optional or can be combined in different ways.

This project solves that problem with three main components.

1. Repository

The repository extends both JpaRepository and JpaSpecificationExecutor.

public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {

}

JpaSpecificationExecutor adds methods such as:

findAll(Specification<T> specification)
findAll(Specification<T> specification, Pageable pageable)

2. Search Specification

SearchSpecification<S, T> stores the search object and delegates predicate creation to a custom specification.

public abstract class SearchSpecification<S, T>
        implements Specification<T> {

    private final S search;

    protected SearchSpecification(S search) {
        this.search = search;
    }

    protected abstract Predicate buildPredicate(
            Root<T> root,
            CriteriaQuery<?> query,
            CriteriaBuilder builder,
            S search
    );
}

Concrete specifications decide which predicates should be added based on the provided filters.

3. Page Specification

SearchPageSpecification<S, T> extends the base specification and creates a Pageable from:

Parameter	Description	Default
page	Zero-based page number	0
size	Number of records per page	10
sort	Sort direction: asc or desc	asc
sortField	Entity field used for sorting	id

The application then executes the specification with:

employeeRepository.findAll(
    specification,
    specification.getPageable()
);

Project Structure

src/main/java/com/tirmizee
├── api
│   ├── ExampleApiController.java
│   └── data
│       ├── EmployeeDetailDTO.java
│       ├── EmployeeDetailDTO2.java
│       ├── ExampleSearch1.java
│       ├── ExampleSearch2.java
│       └── ExampleSearch3.java
├── jpa
│   ├── entities
│   │   ├── Department.java
│   │   └── Employee.java
│   ├── repositories
│   │   ├── DepartmentRepository.java
│   │   └── EmployeeRepository.java
│   └── specification
│       ├── SearchSpecification.java
│       ├── SearchPageSpecification.java
│       ├── SearchPageable.java
│       └── custom
│           ├── ExampleSpecification1.java
│           ├── ExampleSpecification2.java
│           ├── ExampleSpecification3.java
│           └── SearchCriteria.java
└── SpringBootCustomJpaSpeficicationApplication.java

Prerequisites

Install the following tools before running the project:

• JDK 8
• MySQL 8
• Maven, or use the included Maven Wrapper

Configuration properties

Modify these properties to match your configuration.

Update src/main/resources/application.properties to match your environment:

## SERVER PORT
server.port=8000

## CONNECTION 
spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

## CONNECTION POOL
spring.datasource.hikari.connection-timeout=600000
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.idle-timeout=3000000
spring.datasource.hikari.max-lifetime=3000000
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.pool-name=MYSQL-DEMO-POOL

## JPA HIBERNATE
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.type=trace

## LOGGING
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=TRACE

yaml version

server:
  port: '8000'
spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        hbm2ddl:
          auto: update
        use_sql_comments: 'true'
        format_sql: 'true'
        show_sql: 'true'
        type: trace
  datasource:
    hikari:
      connection-timeout: '600000'
      pool-name: MYSQL-DEMO-POOL
      idle-timeout: '3000000'
      maximum-pool-size: '5'
      max-lifetime: '3000000'
      auto-commit: 'true'
      minimum-idle: '2'
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    url: jdbc:mysql://localhost:3306/demo?useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
logging:
  level:
    com:
      zaxxer:
        hikari:
          HikariConfig: DEBUG
          nodeValue: TRACE

Running the Application

macOS or Linux

./mvnw spring-boot:run

Windows

mvnw.cmd spring-boot:run

The application starts on:

http://localhost:8000

At startup, the application generates sample departments and employees with Java Faker.

API Examples

1. Search Employees as a List

	GET method 
	URl : http://localhost:8000/api/employee/list?deptNo=&firstName=&lastName=&deptName

2. Search, Sort, and Paginate with GET

	GET method 
	URl : http://localhost:8000/api/employee/page?page=0&sort=desc&sortField=deptName&firstName=Yos&lastName&deptNo=D002&deptName=Ar&size=20

3. Search, Sort, and Paginate with POST

	POST method 
	URl : http://localhost:8000/api/employee/page
	Request body :
	{
	    "page" : 0,
	    "size" : "10",
	    "sort" : "asc",
	    "sortField" : "id",
	    "search" : {
		"firstName" : null,
		"lastName" : null,
		"deptNo" : null,
		"deptName" : null
	    }
	}

The POST endpoint is useful when the search model becomes too complex for query parameters.

Adding a New Dynamic Search

Step 1: Create a Search Model

@Data
public class EmployeeSearch {
    private String firstName;
    private String lastName;
    private String deptNo;
    private String deptName;
}

For pagination, extend SearchPageable:

@Data
@EqualsAndHashCode(callSuper = true)
public class EmployeePageSearch extends SearchPageable {
    private String firstName;
    private String lastName;
}

Step 2: Create a Specification

Extend SearchSpecification for a non-paginated search or SearchPageSpecification for a paginated search.

Inside the specification:

1. Create an empty list of predicates.
2. Check whether each search field contains a value.
3. Add the corresponding Criteria API predicate.
4. Join related entities when a related field must be filtered.
5. Return the combined predicate.

Conceptual example:

List<Predicate> predicates = new ArrayList<>();

if (StringUtils.isNotBlank(search.getFirstName())) {
    predicates.add(
        builder.like(
            builder.lower(root.get("firstName")),
            "%" + search.getFirstName().toLowerCase() + "%"
        )
    );
}

if (StringUtils.isNotBlank(search.getDeptName())) {
    Join<Employee, Department> department = root.join("department");

    predicates.add(
        builder.like(
            builder.lower(department.get("deptName")),
            "%" + search.getDeptName().toLowerCase() + "%"
        )
    );
}

return builder.and(predicates.toArray(new Predicate[0]));

Step 3: Execute the Specification

Without pagination:

EmployeeSpecification specification =
        new EmployeeSpecification(search);

List<Employee> employees =
        employeeRepository.findAll(specification);

With pagination:

EmployeePageSpecification specification =
        new EmployeePageSpecification(search);

Page<Employee> employees =
        employeeRepository.findAll(
            specification,
            specification.getPageable()
        );

Sorting Related Entity Fields

API field names do not always match entity property paths.

For example, a client may send:

sortField=deptName

But JPA may need to sort using a nested property such as:

department.deptName

SearchPageSpecification provides a sortProperty method that can be overridden to translate an API field into the correct entity property.

@Override
protected String sortProperty(String sortField) {
    if ("deptName".equals(sortField)) {
        return "department.deptName";
    }

    return sortField;
}

Notes

• Page numbering starts at 0.
• The default page size is 10.
• The default sort direction is ascending.
• The default sort field is id.
• Empty filters are ignored by the custom specifications.
• deptName filtering requires a join from Employee to Department.
• The sample API maps entities into DTOs before returning responses.
• This repository is intended as a learning reference and proof of concept.