Enhanced Spring Specification — Multi Search/Filter and Sort with different operators and pagination

5 min readAug 28, 2020

Introduction

Development framework are enhancing the way of writing code day by day to overcome from the boilerplate code and reduce the code for baking and then using the functionality. The idea is to create the solution for the common problem so the developer can focus only on the business logic instead of focusing on the common functionalities of application.

One of the common feature needed in development is searching/filtering and sorting of records from the tables dynamically from the grid (showing on web browsers). For this, there are many user interface technologies are developed smart component to handle this, the data is loaded into the component provide features to sort and filter e.g. jqwidget data grid, ag-grid or prime angluar components.

Now, problem comes with large set of dataset especially in the application where large set of data available on which column based search and filter or sort required. In this case, the user interface component providing features will require to load all the tables data then only sort or filter (multi-column filter) can be applied. Consider a scenario where few millions of records are there then? It requires a lot of time to load data to browser so that grid component can do filter or sort.

To overcome from this problem, normally it is suggested to use the back end filter and sort means, the filter or sort criteria is sent to back end and it will be added into query running on database and once database does the sorting filtering the result is provided to browser.

This is very common solution and mostly used into application in the case where large dataset is available. This solution requires to add the sorting and filter criteria anemically as user fills into the grid filter option e.g.

Suppose in grid there are four fields: EmpCode, Name, Department, Address

then filter criteria like Name starts with ‘S’ and Department is Logistic. This criteria need to be added dynamically in query.

Technical stack

The solution here involves below technologies:

Java 8 or later as programming language
Spring/Spring boot framework
Spring data with JPA (with any SQL based ORM adapters e.g. Hibernate)

Audience

This article is intended for,

Java and Spring developer
Solution/Technical Architect

The solution touches the development technologies and terminologies which need to be prepared like:

database, spring, spring data, spring boot, entity, spring jpa, hibernate, service endpoints

Problem

In application where records are shown into grid and data should be searched/filtered from the grid with multiple operator (like ‘starts with’, ‘ends with’, ‘equals’, ‘not equals’, in case of number type; ‘less than’, ‘greater than’, ‘less than equal to’, ‘greater than equal to’ etc.) on large dataset (with millions of records) and these functionality need to be performed at back end because of large amount of data, an easy and less code to generate dynamic query without much effort in Spring data environment.

Solution

The Spring data framework already provides a solution for single column filtering with Specification only need to enhance it.

Create a constant to store operators, primitive data types and patterns e.g. https://github.com/siddhivinayak-sk/csm/blob/master/src/main/java/com/bank/csm/constants/SpecificationConstants.java

This will add the operator and other needed constant which will further be used to take the operators with same name e.g.

Operator for boolean:

<EQ>

query example: `isAdult:boolean<EQ>true

This will add criteria: where isAdult = true

Operators for string and numbers:

EQ: Equals

NE: Not Equals

IN: To indicate in predicate

Example:

`country<EQ>USA

The above will add criteria as: where country = ‘USA’

`country<NE>Australia

The above will add criteria as: where country <> ‘Australia’

Operators for string:

CT: Contains

SW: Start with

EW: Ends with

Example:

`country<SW>A

The above will add criteria as: where country like ‘A%’

`country<EW>a

The above will add criteria as: where country like ‘%a’

Operators for numbers:

GT: Greater than

LT: Less than

EW: Ends with

GE: Greater than equals to

LE: Less than equals to

Example:

`age<GE>20

The above will add criteria as: where age > 20

`age<LE>60

The above will add criteria as: where age ≤ 60

Create a Specification utility by referencing below class (we will discuss the class logic later): https://github.com/siddhivinayak-sk/csm/blob/master/src/main/java/com/bank/csm/utils/SpecificationUtils.java

This class has utility method and logic which creates Specification object with criteria based upon passed query from the front end. The method will be used into service to generate runtime Specification object based upon query and that will be further used with repository method.

Code Snippet 1: SpecificationUtils to create Specification object dynamically

2. Create controller method like https://github.com/siddhivinayak-sk/csm/blob/e340dc1dd96c6f8b3715a7a9619e42ea37198c1d/src/main/java/com/bank/csm/controllers/CustomerController.java#L54

Code Snippet 2: Controller Code for exposing end point

It get the query string from the request and pass into the service method as parameter along with sorting and pagination details. If sort is not provided it will be ignored.

3. Create service method like https://github.com/siddhivinayak-sk/csm/blob/e340dc1dd96c6f8b3715a7a9619e42ea37198c1d/src/main/java/com/bank/csm/services/impl/CustomerServiceImpl.java#L104

The below code create the specification dynamically based upon the criteria form the request and pass into the repository method:

Run the application
Hit the service end point by adding query parameters like http://endpont-resource-url?query=`name<SW>John`country<EQ>USA&pageNumber=0&pageSize=10&sort=name,desc

The above query parameter will fetch records where name starts with John and country is USA, sorted by name into descending order with page size 10 and page 0

Conclusion

The solution proposed here is enhancement of Spring Data Specification, where specifications are created dynamically based upon filter criteria passed by user.

The implementation does not require any additional code for specific code or adhoc query generation. It automatically get the query, operator and value based upon that creates Criteria object and append query while running query with Hibernate. So it is quite easy and less code required at back end for filter records on multiple criteria.

It also support sort at same time and pagination if required.