Conditional Pagination and Sorting using RESTful Web Services, MySQL, and Spring Boot

In this tutorial, we will learn how to implement pagination and sorting using Spring Boot. We will be using database data from here as sample data on which we will perform conditional sorting and pagination. We will be storing data in MySQL Database.

Let's get started!

Step 1: Create a Spring Boot Starter Project

Right-click on STS Package Explorer -> new -> Spring Starter Project

Click Next. We will be using the below dependencies:

Web: to handle RESTful calls and use Spring MVC Architecture

JPA: to handle entity beans

Devtools: so that we don’t have to rebuild project after code changes.

Step 2: Create Project Structure 

1. Create Entity Class:

package com.app.pagination.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "json_data")
public class PagingEntity {
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Column(name = "id")
private Integer id;

@Column(name = "userId")
private Integer userId;

@Column(name = "title")
private String title;

@Column(name = "body")
private String body;
  // getter - setters

2. Create Controller:

package com.app.pagination.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.app.pagination.entity.PagingEntity;
import com.app.pagination.enums.Direction;
import com.app.pagination.enums.OrderBy;
import com.app.pagination.exception.PaginationSortingException;
import com.app.pagination.exception.PagingSortingErrorResponse;
import com.app.pagination.service.PaginationService;

@RestController
@RequestMapping(value = "/pagination")

public class PaginationController {

@Autowired
private PaginationService paginationService;

@RequestMapping(value = "/conditionalPagination", params = { "orderBy", "direction", "page",
"size" }, method = RequestMethod.GET)
@ResponseBody
public Page<PagingEntity> findJsonDataByPageAndSize(@RequestParam("orderBy") String orderBy,
@RequestParam("direction") String direction, @RequestParam("page") int page,
@RequestParam("size") int size) {

if (!(direction.equals(Direction.ASCENDING.getDirectionCode())
|| direction.equals(Direction.DESCENDING.getDirectionCode()))) {
throw new PaginationSortingException("Invalid sort direction");
}

if (!(orderBy.equals(OrderBy.ID.getOrderByCode()) || orderBy.equals(OrderBy.USERID.getOrderByCode()))) {
throw new PaginationSortingException("Invalid orderBy condition");
}
Page<PagingEntity> list = paginationService.findJsonDataByCondition(orderBy, direction, page, size);
return list;
}

@ExceptionHandler(PaginationSortingException.class)
public ResponseEntity<PagingSortingErrorResponse> exceptionHandler(Exception ex) {
PagingSortingErrorResponse pagingSortingErrorResponse = new PagingSortingErrorResponse();
pagingSortingErrorResponse.setErrorCode(HttpStatus.PRECONDITION_FAILED.value());
pagingSortingErrorResponse.setMessage(ex.getMessage());
return new ResponseEntity<PagingSortingErrorResponse>(pagingSortingErrorResponse, HttpStatus.OK);
}

}

2.1 Enum Class for Direction:

package com.app.pagination.enums;

public enum Direction {
ASCENDING("ASC"), DESCENDING("DESC");
private final String directionCode;
private Direction(String direction) {
this.directionCode = direction;
}
public String getDirectionCode() {
return this.directionCode;
}
}

2.2 Enum Class for OrderBy:

package com.app.pagination.enums;

public enum OrderBy {
ID("id"), USERID("userId");
private String OrderByCode;
private OrderBy(String orderBy) {
this.OrderByCode = orderBy;
}
public String getOrderByCode() {
return this.OrderByCode;
}

}

3. Sevice Layer:

package com.app.pagination.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.stereotype.Service;
import com.app.pagination.dao.PaginationDao;
import com.app.pagination.entity.PagingEntity;;

@Service
public class PaginationService {

@Autowired
private PaginationDao paginationDao;

public Page<PagingEntity> findJsonDataByCondition(String orderBy, String direction, int page, int size) {
Sort sort = null;
if (direction.equals("ASC")) {
sort = new Sort(new Sort.Order(Direction.ASC, orderBy));
}
if (direction.equals("DESC")) {
sort = new Sort(new Sort.Order(Direction.DESC, orderBy));
}
Pageable pageable = new PageRequest(page, size, sort);
Page<PagingEntity> data = paginationDao.findAll(pageable);
return data;
     }
}

4. Dao Layer: 

package com.app.pagination.dao;

import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.PagingAndSortingRepository;
import com.app.pagination.entity.PagingEntity;

public interface PaginationDao extends PagingAndSortingRepository<PagingEntity, Integer> {

}

5. Exception Handling:

Here we have used @ExceptionHandler as we have only one controller. In scenarios with multiple controllers, use @ControllerAdvice so as to handle the exception at the application level.

5.1. PaginationSortingException:

Our custom Exception Class:

package com.app.pagination.exception;
public class PaginationSortingException extends RuntimeException {
    private static final long serialVersionUID = -123L;
private String errorMessage;
    @Override
public String getMessage() {
        return errorMessage;
}
    public PaginationSortingException() {
super();
}
    public PaginationSortingException(String errorMessage) {
super(errorMessage);
this.errorMessage = errorMessage;
}
}

5.2 PagingSortingErrorResponse.java:

To handle error codes and exception Message.

package com.app.pagination.exception;
public class PagingSortingErrorResponse {
private int errorCode;
private String message;
// Setter Getters
}

Finally, we will have a project structure as shown below. Also, add MySQL connector as per database.

Step 3: Database Setup

You can have your own set of data based on entity class but for simplicity, I have placed an insert script at below GitHub location:

https://github.com/ShashankBodkhe/DB-Queries/blob/master/InsertQuery.sql

Use this query and run on your MySQL WorkBench to insert sample data in the MySQL Database:

Step 4:  Start Boot Project and Test Endpoints

4.1

Right click on project -> run as -> Spring Boot App. The server will start at port 8080 default. 

2018-08-22 22:07:58.010 INFO 14184 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-08-22 22:07:58.016 INFO 14184 --- [ main] c.a.p.PaginationSortingDemoApplication : Started PaginationSortingDemoApplication in 10.472 seconds (JVM running for 12.664)

4.2

Now we can choose orderBy based on id and userId and direction of sorting as Ascending (ASC) or Descending(DESC) and provide page and size as well.

Below are some conditional queries:

1)http://localhost:8080/pagination/conditionalPagination?orderBy=id&direction=ASC&page=0&size=10 

{"content":[{"id":1,"userId":null,"title":"sunt aut facere repellat provident occaecati excepturi optio ...
  ...[{"direction":"ASC","property":"id","ignoreCase":false,"nullHandling":"NATIVE","ascending":true}],"numberOfElements":10,"first":true}

2)http://localhost:8080/pagination/conditionalPagination?orderBy=userId&direction=DESC&page=4&size=5

{"content":[{"id":21,"userId":null,"title":"asperiores ea ipsam voluptatibus modi minima quia sint","body":"repellat aliquid praesentium dolorem quo....
[{"direction":"DESC","property":"userId","ignoreCase":false,"nullHandling":"NATIVE","ascending":false}],"numberOfElements":5,"first":false}

(You can have many more conditions in the query as per your requirement)

If you enter incorrect orderBy or direction Exception handling will give proper error messages, like:

1) http://localhost:8080/pagination/conditionalPagination?orderBy=id&direction=DESCCC&page=4&size=10

{"errorCode":412,"message":"Invalid sort direction"}

2) http://localhost:8080/pagination/conditionalPagination?orderBy=iddddd&direction=ASC&page=4&size=10

{"errorCode":412,"message":"Invalid orderBy condition"}

Hope this helps! Happy coding!