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 application.properties
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.
springjpa-postgresql-configdependencystarter
Click Finish, then our project will be created successfully.

Open pom.xml and check Dependencies:

		
			org.springframework.boot
			spring-boot-starter-data-jpa
		

		
			org.springframework.boot
			spring-boot-starter-web
		

		
			org.postgresql
			postgresql
			runtime
		

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

2. Configure Spring JPA

Open application.properties, add these lines of configuration code:

spring.datasource.url=jdbc:postgresql://localhost/testdb
spring.datasource.username=postgres
spring.datasource.password=123
spring.jpa.generate-ddl=true
3. Create DataModel Class

Under package model, create class Customer.

Content of Customer.java:

package com.javasampleapproach.pagingjpa.model;

import java.io.Serializable;

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 = "customer")
public class Customer implements Serializable {

	private static final long serialVersionUID = -3009157732242241606L;
	@Id
	@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;
	}

	@Override
	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 CustomerRepository.java

package com.javasampleapproach.pagingjpa.repo;

import org.springframework.data.repository.PagingAndSortingRepository;

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.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

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

@Service
public class CustomerService {

	private final static int PAGESIZE = 3;
	
	@Autowired
	CustomerRepository repository;
	
	public void save(Customer customer) {
		repository.save(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 org.springframework.data.domain.Slice: 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;

@RestController
public class WebController {

	@Autowired
	private CustomerService customerService;

	@RequestMapping("/save")
	public String process() {
		customerService.save(new Customer("Jack", "Smith"));
		customerService.save(new Customer("Adam", "Johnson"));
		customerService.save(new Customer("Kim", "Smith"));
		customerService.save(new Customer("David", "Williams"));
		customerService.save(new Customer("Peter", "Davis"));
		return "Done";
	}

	@RequestMapping("/findall")
	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(
    id BIGINT PRIMARY KEY NOT NULL,
    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
http://localhost:8080/save
The browser returns Done and if checking database testdb with table customer, we can see some data rows has been added:

Request 2
http://localhost:8080/findall

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
http://localhost:8080/customers

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
http://localhost:8080/customers?p=1

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

Request 5
http://localhost:8080/customers?p=2

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

SpringJPAPostgreSQLPaging

Leave a Reply

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