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

package com.javasampleapproach.pagingjpa.model;


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

@Table(name = "customer")
public class Customer implements Serializable {

	private static final long serialVersionUID = -3009157732242241606L;
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;

	@Column(name = "firstname")
	private String firstName;

	@Column(name = "lastname")
	private String lastName;

	protected Customer() {

	public Customer(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;

	public String toString() {
		return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);
4. Create Spring JPA Repository Interface

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

package com.javasampleapproach.pagingjpa.repo;


import com.javasampleapproach.pagingjpa.model.Customer;

public interface CustomerRepository extends PagingAndSortingRepository {


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.

package com.javasampleapproach.pagingjpa.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.javasampleapproach.pagingjpa.model.Customer;
import com.javasampleapproach.pagingjpa.repo.CustomerRepository;

public class CustomerService {

	private final static int PAGESIZE = 3;
	CustomerRepository repository;
	public void save(Customer customer) {;
	public Iterable findAllCustomers() {
		return repository.findAll();
	public List getPage(int pageNumber) {
		PageRequest request = new PageRequest(pageNumber - 1, PAGESIZE, Sort.Direction.ASC, "id");
		return repository.findAll(request).getContent();

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

Page findAll(Pageable pageable);

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:

package com.javasampleapproach.pagingjpa.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
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.RestController;

import com.javasampleapproach.pagingjpa.model.Customer;
import com.javasampleapproach.pagingjpa.service.CustomerService;

public class WebController {

	private CustomerService customerService;

	public String process() { Customer("Jack", "Smith")); Customer("Adam", "Johnson")); Customer("Kim", "Smith")); Customer("David", "Williams")); Customer("Peter", "Davis"));
		return "Done";

	public String findAll() {
		String result = "";

		for (Customer customer : customerService.findAllCustomers()) {
			result += customer.toString() + "br/";

		return result + "";

	@RequestMapping(value = "/customers", method = RequestMethod.GET)
	public String viewCustomers(@RequestParam(name = "p", defaultValue = "1") int pageNumber) {
		String result = "";

		List customers = customerService.getPage(pageNumber);

		for (Customer customer : customers) {
			result += customer.toString() + "br/";

		return result + "";


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:

CREATE TABLE customer(
    firstname VARCHAR(100),
    lastname VARCHAR(100)
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

Customer[id=1, firstName='Jack', lastName='Smith']
Customer[id=2, firstName='Adam', lastName='Johnson']
Customer[id=3, firstName='Kim', lastName='Smith']
Customer[id=4, firstName='David', lastName='Williams']
Customer[id=5, firstName='Peter', lastName='Davis']

Request 3

Customer[id=1, firstName='Jack', lastName='Smith']
Customer[id=2, firstName='Adam', lastName='Johnson']
Customer[id=3, firstName='Kim', lastName='Smith']

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

Request 4

Customer[id=1, firstName='Jack', lastName='Smith']
Customer[id=2, firstName='Adam', lastName='Johnson']
Customer[id=3, firstName='Kim', lastName='Smith']

Request 5

Customer[id=4, firstName='David', lastName='Williams']
Customer[id=5, firstName='Peter', lastName='Davis']
IV. Source Code


Leave a Reply

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