SpringBoot-JPA-DynamicQuery-Specification

This is an easy-to-use example of searching, sorting and paging with Spring Data JPA specification.

Sample tables

Employee
Department

Configuration properties

change properties to your configuration

## 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

How to implement

Step 1. Create Repository interface then extends JpaRepository and JpaSpecificationExecutor.

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

Step 2. Create Specification class then extends SearchSpecification and override toPredicate method for implements custom search logic.

	public class ExampleSpecification1 extends SearchSpecification<ExampleSearch1, Employee> {

		private static final long serialVersionUID = 1L;

		public ExampleSpecification1(ExampleSearch1 search) {
			super(search);
		}

		@Override
		public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder, ExampleSearch1 searchBody) {

			.....
		}

	}

if you want to use Paging ,extends SearchPageSpecification instead. SearchPageSpecification provides a getPageable function for create a Pagable.

	public class ExampleSpecification2 extends SearchPageSpecification<ExampleSearch2, Employee> {

		private static final long serialVersionUID = 1L;

		public ExampleSpecification2(ExampleSearch2 serachPage) {
			super(serachPage);
		}

		@Override
		public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder,
				ExampleSearch2 searchBody) {

			....
		}

	}

Step 3. Apply it in Service or Controller class.

	@GetMapping(path = "/employee/page")
	public Page<EmployeeDetailDTO> exampleTwo(ExampleSearch2 searchPage){

		ExampleSpecification2 specificationTwo = new ExampleSpecification2(searchPage);

		Page<Employee> page = employeeRepository.findAll(specificationTwo, specificationTwo.getPageable());

		// transform Employee to EmployeeDetailDTO
		Function<Employee, EmployeeDetailDTO> converter = source -> {
			EmployeeDetailDTO target = new EmployeeDetailDTO();
			target.setId(source.getId());
			target.setFirstName(source.getFirstName());
			target.setLastName(source.getLastName());
			target.setDepartmentNo(source.getDeptNo());
			target.setDepartmentName(source.getDepartment().getDeptName());
			return target;
		};

		return page.map(converter);
	}

Demo

Example searching list of Employee.

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

Example searching, sorting and paging for GET method.

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

Example searching, sorting and paging for POST method.

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

Dependencies