HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Spring Boot 2 / Spring boot pagination and sorting example

Spring boot pagination and sorting example

Learn to request and display only chunk of data from database using pagination and sorting inputs and query parameters in spring boot and spring data applications.

Paging and sorting is mostly required when we are displaying domain data in tabular format in UI.

Pagination consist of two fields – page size and page number. Sorting is done on a single of multiple fields in the table.

1. JPA Entity

In this post, we are taking example of EmployeeEntity class. Each entity instance represent an employee record in database.

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

@Entity
@Table(name="TBL_EMPLOYEES")
public class EmployeeEntity {

    @Id
    @GeneratedValue
    private Long id;
    
    @Column(name="first_name")
    private String firstName;
    
    @Column(name="last_name")
    private String lastName;
    
    @Column(name="email", nullable=false, length=200)
    private String email;
    
    //Setters and getters

    @Override
    public String toString() {
        return "EmployeeEntity [id=" + id + ", firstName=" + firstName + 
                ", lastName=" + lastName + ", email=" + email   + "]";
    }
}

2. PagingAndSortingRepository

PagingAndSortingRepository is an extension of CrudRepository to provide additional methods to retrieve entities using the pagination and sorting abstraction. It provides two methods :

  • Page findAll(Pageable pageable) – returns a Page of entities meeting the paging restriction provided in the Pageable object.
  • Iterable findAll(Sort sort) – returns all entities sorted by the given options. No paging is applied here.
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.howtodoinjava.demo.entity.EmployeeEntity;

@Repository
public interface EmployeeRepository 
        extends PagingAndSortingRepository<EmployeeEntity, Long> {

}

3. Accepting paging and sorting parameters

Generally, paging and sorting parameters are optional and thus part of request URL as query parameters. If any API supports paging and sorting, ALWAYS provide default values to its parameters – to be used when client does not choose specify any paging or sorting preferences.

The default paging and sorting values shall be clearly documented in API docs. In UI, these default values can be highlighted with separate colors.

Page number values start with 0. So in UI, if you are displaying page number from 1, then do not forget to subtracting ‘1’ while fetching records.

In below spring mvc controller, we are accepting paging and sorting parameters using pageNo, pageSize and sortBy query parameters. Also, by default '10' employees will be fetched from database in page number '0', and employee records will be sorted based on 'id' field.

@RestController
@RequestMapping("/employees")
public class EmployeeController 
{
    @Autowired
    EmployeeService service;

    @GetMapping
    public ResponseEntity<List<EmployeeEntity>> getAllEmployees(
                        @RequestParam(defaultValue = "0") Integer pageNo, 
                        @RequestParam(defaultValue = "10") Integer pageSize,
                        @RequestParam(defaultValue = "id") String sortBy) 
    {
        List<EmployeeEntity> list = service.getAllEmployees(pageNo, pageSize, sortBy);

        return new ResponseEntity<List<EmployeeEntity>>(list, new HttpHeaders(), HttpStatus.OK); 
    }
}

To perform pagination and/or sorting, we must create org.springframework.data.domain.Pageable or org.springframework.data.domain.Sort instances are pass to the findAll() method.

@Service
public class EmployeeService 
{
    @Autowired
    EmployeeRepository repository;
    
    public List<EmployeeEntity> getAllEmployees(Integer pageNo, Integer pageSize, String sortBy)
    {
        Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by(sortBy));

        Page<EmployeeEntity> pagedResult = repository.findAll(paging);
        
        if(pagedResult.hasContent()) {
            return pagedResult.getContent();
        } else {
            return new ArrayList<EmployeeEntity>();
        }
    }
}

4. Pagination and sorting techniques

4.1. Paging WITHOUT sorting

To apply only pagination in result set, we shall create Pageable object without any Sort information.

 
Pageable paging = PageRequest.of(pageNo, pageSize);

Page<EmployeeEntity> pagedResult = repository.findAll(paging); 

4.2. Paging WITH sorting

To apply only pagination in result set, we shall create Pageable object with desired Sort column name.

 
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email"));

Page<EmployeeEntity> pagedResult = repository.findAll(paging);

By default, records are ordered in DESCENDING order. To choose ASCENDING order, use .ascending() method.

 
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email").ascending()); 

Page<EmployeeEntity> pagedResult = repository.findAll(paging); 

4.3. Sorting only

If there is no need to page, and only sorting is required, we can create Sort object for that.

 
Sort sortOrder = Sort.by("email"); 

List<EmployeeEntity> list = repository.findAll(sortOrder);

If we wish to apply sorting on multiple columns or group by sort, then that is also possible by creating Sort using simple builder pattern steps.

 
Sort emailSort = Sort.by("email"); 
Sort firstNameSort = Sort.by("first_name"); 

Sort groupBySort = emailSort.and(firstNameSort);

List<EmployeeEntity> list = repository.findAll(groupBySort);

5. Difference between Page and Slice

5.1. Page

The findAll(Pageable pageable) method by default returns a Page object. A Page object provides lots of extra useful information other than just list of employees in current page.

E.g. A Page object has the number of total pages, number of the current page and well as whether current page is first page or last page.

Finding total pages invokes an additional count() query causing an extra overhead cost. Be sure when you are using it.

5.2. Slice

Slice is very much similar to Page, except it does not provide the number of total pages in database. It helps in improving performance when we do not need to display total number pages in UI.

Generally, Slice is used in case navigation is consist of Next page and Previous page links.

To use Slice, we have implement our own custom methods.

 
public interface EmployeeRepository extends CrudRepository<EmployeeEntity, Long> 
{
    public Slice<EmployeeEntity> findByFirstName(String firstName, Pageable pageable);
}

Remember that is we use PagingAndSortingRepository, default return type is Page.

 
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email").descending()); 

Slice<EmployeeEntity> slicedResult = repository.findByFirstName("alex", paging); 

List<EmployeeEntity> employeeList = slicedResult.getContent();

6. Spring boot paging and sorting demo

In this demo, default page number is 0, page size is 10 and default sort column is ‘id’.

Now invoke these URLs one by one and observe the outputs.

  • http://localhost:8080/employees?pageSize=5
  • http://localhost:8080/employees?pageSize=5&pageNo=1
  • http://localhost:8080/employees?pageSize=5&pageNo=2
  • http://localhost:8080/employees?pageSize=5&pageNo=1&sortBy=email
  • http://localhost:8080/employees?pageSize=5&pageNo=1&sortBy=firstName

Drop me your questions in comments related to how to paginate and sort using Spring Data JPA.

Happy Learning !!

Sourcecode download

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.
TwitterFacebookLinkedInRedditPocket

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Shahul Basha

    July 20, 2020

    what is the best way if i want to do pagination but i cant use hibernate. I can only use jdbctemplate. Right now I am manually appending Limit and Offset params to query to fetch the results.

  2. Nirmal Mukundan

    July 6, 2020

    Hi

    Can the same example be used to do pagination with MongoDB too?

  3. Swapnil Tiwari

    April 28, 2020

    Is there any way to paginate an aggregated list being fetched as a part of the entity object I am fetching from SQL DB?? For example:
    Class A contains an aggregated list of objects of Class B
    I wish to fetch an Object of Class A (using “fetchById()”) but the aggregated list of Objects of Class B need to be paginated.

  4. Bessam

    April 1, 2020

    the sorting isn’t working in this example

    • Lokesh Gupta

      April 2, 2020

      Can you plz explain?

  5. Manjiri Tatke

    March 18, 2020

    Example worked but not working with thymleaf

  6. ANNK

    January 14, 2020

    Is it possible to use pagingandsorting repository with spring data jdbc

  7. AKHIL

    September 9, 2019

    i want the pagination for the mysql procedure response . how can i proceed with that kind of responses in spring boot

  8. Sarika

    July 17, 2019

    In above example it will show 10 employees based on ID. but what if I have 10000 Employees and I want to fetch in chunk of 100 employees. above will give first 100 what about next set of data ?? How to get remaining ??

    • Lokesh Gupta

      July 18, 2019

      http://localhost:8080/employees?pageSize=100&pageNo=1 //1 to 100
      http://localhost:8080/employees?pageSize=100&pageNo=2 //101 to 200
      http://localhost:8080/employees?pageSize=100&pageNo=3 //201 to 300

      and so on. Am I missing anything?

  9. Neha

    July 16, 2019

    Is there any way if we can override default Paging configurations?

    A questions here: https://stackoverflow.com/questions/27032433/set-default-page-size-for-jpa-pageable-object

  10. Savani

    July 15, 2019

    Hi,
    Is there any way if we can get the data in paginated formated and where status is Active ?

    • Lokesh Gupta

      July 16, 2019

      Absolutely. Why you see it a challenge?

  11. Ramakrishna

    June 9, 2019

    Your example looks very nice because you are implementing PagingAndSortingRepository which is better and new way doing pagination with Spring & Spring Boot.

Comments are closed on this article!

Search Tutorials

Spring Boot 2 Tutorial

  • Spring Boot – Introduction
  • Spring Boot – Starter parent
  • Spring Boot – Starter templates
  • Spring Boot – Multi-module project
  • Spring Boot – Annotations
  • Spring Boot – Auto configuration
  • Spring Boot – AOP
  • Spring Boot – Logging
  • Spring Boot – DevTools
  • Spring Boot – WAR Packaging
  • Spring Boot – REST API
  • Spring Boot – CRUD
  • Spring Boot – OAuth2
  • Spring Boot – Testing
  • Spring Boot – RestTemplate
  • Spring Boot – Thymeleaf
  • Spring Boot – Hibernate
  • Spring Boot – DataSource
  • Spring Boot – Error Handling
  • Spring Boot – Caching
  • Spring Boot – Retry
  • Spring Boot – BasicAuth
  • Spring Boot – H2 Database
  • Spring Boot – Ehcache 3.x
  • Spring Boot – Gson
  • Spring Boot – RMI
  • Spring Boot – Send Email
  • Spring Boot – Interview Questions

Spring Boot Tutorial

  • Spring Boot – CommandLineRunner
  • Spring Boot – Configure Jetty
  • Spring Boot – Tomcat Default Port
  • Spring Boot – Context Root
  • Spring Boot – SSL [https]
  • Spring Boot – Get all loaded beans
  • Spring Boot – PropertyEditor
  • Spring Boot – @EnableScheduling
  • Spring Boot – Jersey
  • Spring Boot – SOAP Webservice
  • Spring Boot – SOAP Client
  • Spring Boot – JMSTemplate
  • Spring Boot – REST APIs
  • Spring Boot – JSP View
  • Spring Boot – Actuator endpoints
  • Spring Boot – Role Based Security
  • Spring Boot – RSS / ATOM Feed
  • Spring Boot – Ehcache 2.x

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Sealed Classes and Interfaces