How to create paging and sorting results with Spring JPA and PostgreSQL | Spring Boot

Spring Data with Spring JPA supports the ways to write interface for repositories and custom finder methods. Pagination is so important in case we have many data records to show.

In this tutorial, we’re gonna look at the way to make paging and sorting results with Spring JPA and PostgreSQL using Spring Boot.

Related articles:
How to use Spring JPA MySQL | Spring Boot
How to use Spring JPA with PostgreSQL | Spring Boot
@DataJPATest with Spring Boot

I. Technology

– Java 1.8
– Maven 3.3.9
– Spring Tool Suite – Version 3.8.1.RELEASE
– Spring Boot: 1.5.1.RELEASE

II. Overview
1. Project Structure

– Class Customer corresponds to entity and table customer, it should be implemented Serializable.
CustomerRepository is an interface extends PagingAndSortingRepository, will be autowired in CustomerService for implementing repository methods.
CustomerService provides customer service functions for WebController.
WebController is a REST Controller which has request mapping methods for RESTful requests such as: save, findall, customers?page=…
– Configuration for Spring Datasource and Spring JPA properties in
Dependencies for Spring Boot and PostgreSQL in pom.xml

2. Step to do

We will follow these steps to make things done:
– Create Spring Boot project & add Dependencies
– Configure Spring JPA
– Create DataModel Class
– Create Spring JPA Repository Interface
– Create Service Class
– Create Web Controller
– Create PostGreSQL table
– Run Spring Boot Application & Enjoy Result

III. Practice
1. Create Spring Boot project & add Dependencies

Open Spring Tool Suite, on Menu, choose File -> New -> Spring Starter Project, then fill each fields.
Click Next, in SQL: choose JPA and PostgreSQL, in Web: choose Web.
Click Finish, then our project will be created successfully.

Open pom.xml and check Dependencies:

These dependencies were auto-generated by the configuration we have done before.

2. Configure Spring JPA

Open, add these lines of configuration code:

3. Create DataModel Class

Under package model, create class Customer.

Content of

4. Create Spring JPA Repository Interface

This interface helps us do all Paging and Sorting functions for class Customer.
Under package repo, create

We only need to extend PagingAndSortingRepository Class, the implementation will be done automatically by Spring Framework.

5. Create Service Class

Under package service, create CustomerService class, remember to annotate it with @Service annotation. This helps Spring to auto-create a Bean for us to autowire in Controller layer.

-By using PageRequest, we can specify:
+ the number of items in a page by PAGESIZE;
+ how the sorting works by Sort.Direction.ASC
+ which field is used for sorting by the string parameter "id" after that. It means we wanna sort by id column.

– We have called the method of PagingAndSortingRepository:

It returns a Page, then getContent() returns a List. Beside that, Page inherites methods from getNumber, getNumberOfElements, getSize, , hasContent, hasNext, hasPrevious, isFirst, isLast, nextPageable, previousPageable… So we can use them to get more database details.

6. Create Web Controller

Under package controller, create WebController class:

We indicate that the pageNumber will be 1 if not specifying its value. That means, a HTTP GET request: /customers is the same as /customers?p=1

7. Create PostGreSQL table

Open pdAdmin III, use SQL Editor and make a query to create customer table:

8. Run Spring Boot Application & Enjoy Result

– Config maven build:
clean install
– Run project with mode Spring Boot App
– Check results:

Request 1
The browser returns Done and if checking database testdb with table customer, we can see some data rows has been added:

Request 2

Request 3

The result of this request should be like the result below:

Request 4

Request 5

IV. Source Code


Related Posts

Got Something To Say:

Your email address will not be published. Required fields are marked *