Sorting And Pagination in Spring Data JPA

April 12, 2020

In this post, we will see Sorting And Pagination in Spring Data JPA example for sorting using Spring Boot and Oracle. In this tutorial, we are going to cover the below points.

Sorting using PagingAndSortingRepository findAll(Sort sort) method.
Pagination using the PagingAndSortingRepository findAll(Pageable pageable).
Sorting And Paginationexample together.
The example of Sorting And Pagination in Spring Data JPA from scratch.
Brief about Sort class, Page interface, Slice interface and Pageable interface.

PagingAndSortingRepository interface contains below methods which are used for sorting and pagination.

Iterable findAll(Sort sort) – Used for Sorting.

Page findAll(Pageable pageable) – Used for pagination.

Consider we have an entity called Student.java as below.

package com.javatute.entity;
 
@Entity
public class Student {
 
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;
 
	@Column(name = "name")
	private String name;
 
	@Column(name = "roll_number")
	private String rollNumber;
 
	@Column(name = "university")
	String university;	
 
}

And we have some records in the database as below.

Spring Data JPA And Or Example

Example of sorting using PagingAndSortingRepository findAll(Sort sort) method.

Example 1 – sorting using a single field.

	@Transactional
	public List<Student> getAllStudentsAsSorted() {
		List<String> fieldsWantToSort = new ArrayList<String>();
		fieldsWantToSort.add("name");// we can multiple field for sorting
		Sort sort =  new Sort(Direction.ASC,fieldsWantToSort);
		List<Student> studentResponse = (List<Student>) studentRepository.findAll(sort);
		return studentResponse;
	}

Example 2 – sorting using multiple fields.

	@Transactional
	public List<Student> getAllStudentsAsSorted() {
		List<String> fieldsWantToSort = new ArrayList<String>();
		fieldsWantToSort.add("name");
		fieldsWantToSort.add("rollNumber");
		Sort sort =  new Sort(Direction.ASC,fieldsWantToSort);
		List<Student> studentResponse = (List<Student>) studentRepository.findAll(sort);
		return studentResponse;
	}

Example 3 – sorting using a single field another way.

	@Transactional
	public List<Student> getAllStudentsAsSorted() {
		List<Student> studentResponse = (List<Student>) studentRepository.findAll(Sort.by("name").ascending());
		return studentResponse;
	}

Example of Pagination using the PagingAndSortingRepository findAll(Pageable pageable) method.

	@Transactional
	public List<Student> getAllStudentsAsPageble(int pageIndex, int sizeOfPage) {

		Pageable pageable = PageRequest.of(pageIndex, sizeOfPage, Direction.ASC);
		Page<Student> page = studentRepository.findAll(pageable);
		if (page != null) {
			List<Student> studentResponse = (List<Student>) page.getContent();
			return studentResponse;
		}
		return null;
	}

Example of Sorting And Pagination together using PagingAndSortingRepository findAll(Pageable pageable) method.

	@Transactional
	public List<Student> getAllStudentsAsPageble(int pageIndex, int sizeOfPage, String sortParameter) {

		Pageable pageable = PageRequest.of(pageIndex, sizeOfPage, Direction.ASC, sortParameter);
		Page<Student> page = studentRepository.findAll(pageable);
		if (page != null) {
			List<Student> studentResponse = (List<Student>) page.getContent();
			return studentResponse;
		}
		return null;
	}

Note – PageRequest.of(0, 2) means – first page which will contain two records.
PageRequest.of(1, 2) means – Second Page contains two records
PageRequest.of(2, 2) means – third Page contains five records

How PageRequest of() method has been internally defined.

public static PageRequest of(int page, int size, Direction direction, String… properties) {
return of(page, size, Sort.by(direction, properties));
}

We will have below REST API to test Sorting And Pagination in Spring Data examples.

http://localhost:9091/student/saveall

http://localhost:9091/student/findallsorted/name/rollNumber

http://localhost:9091/student/findallpageble/0/3

http://localhost:9091/student/findallsortedandpageble/0/4/name

Sorting And Pagination in Spring Data JPA Example using Spring Boot and oracle.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next. Fill all details(GroupId – PagingAndSortingRepositoryfindall, ArtifactId – PagingAndSortingRepositoryfindall and name – PagingAndSortingRepositoryfindall) and click on finish. Keep packaging as the jar.

Modify pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>PagingAndSortingRepositoryfindall</groupId>
	<artifactId>PagingAndSortingRepositoryfindall</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>PagingAndSortingRepositoryfindall</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>

			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<fork>true</fork>
					<executable>C:\Program Files\Java\jdk1.8.0_131\bin\javac.exe</executable>
				</configuration>
			</plugin>


		</plugins>
	</build>
</project>

Note – In pom.xml we have defined javac.exe path in configuration tag. You need to change accordingly i.e where you have installed JDK.

If you see any error for oracle dependency then follow these steps.

Entity class for Sorting And Pagination in Spring Data JPA.

Student.java

package com.javatute.entity;

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

@Entity
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

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

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

}

StudentController.java

package com.javatute.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.javatute.entity.Student;
import com.javatute.service.StudentService;

@RestController
@RequestMapping(value = "/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/saveall", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveAllStudents(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveAllStudent(studentList);
		return studentResponse;
	}

	@RequestMapping(value = "/findallsorted/{firstSortBy}/{secondSortBy}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudentsAsSorted(@PathVariable String firstSortBy, @PathVariable String secondSortBy) {
		List<Student> studentResponse = (List<Student>) studentService.getAllStudentsAsSorted(firstSortBy,
				secondSortBy);
		return studentResponse;
	}

	@RequestMapping(value = "/findallpageble/{pageIndex}/{sizeOfPage}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudentsAsPageble(@PathVariable Integer pageIndex, @PathVariable Integer sizeOfPage) {
		List<Student> studentList = (List<Student>) studentService.getAllStudentsAsPageble(pageIndex, sizeOfPage);
		return studentList;
	}

	@RequestMapping(value = "/findallsortedandpageble/{pageIndex}/{sizeOfPage}/{firstSortBy}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudentsSortedAsWellPageble(@PathVariable Integer pageIndex,
			@PathVariable Integer sizeOfPage, @PathVariable String firstSortBy) {
		List<Student> studentList = (List<Student>) studentService.getAllStudentsSortedAsWellPageble(pageIndex,
				sizeOfPage, firstSortBy);
		return studentList;
	}

}

Note – See more details about @Controller and RestController here.

StudentRepository.java – interface

package com.javatute.repository;

import java.io.Serializable;

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

@Repository
public interface StudentRepository extends PagingAndSortingRepository<Student, Serializable> {

}

StudentService.java – interface

package com.javatute.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.javatute.entity.Student;

@Component
public interface StudentService {
	public List<Student> saveAllStudent(List<Student> studentList);

	public List<Student> getAllStudentsAsSorted(String firstSortBy, String secondSortBy);
	public List<Student> getAllStudentsAsPageble(int pageIndex, int sizeOfPage);
	public List<Student> getAllStudentsSortedAsWellPageble(int pageIndex, int sizeOfPage, String sortByParameter);

}

Note – See here more about @Component, @Controller, @Service and @Repository annotations here.

StudentServiceImpl.java

package com.javatute.impl;

import java.util.ArrayList;
import java.util.List;

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

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.service.StudentService;

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;

@Service("studentServiceImpl")
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public List<Student> saveAllStudent(List<Student> studentList) {
		List<Student> response = (List<Student>) studentRepository.saveAll(studentList);
		return response;
	}

	@Transactional
	public List<Student> getAllStudentsAsSorted(String firstSortBy, String secondSortBy) {
		List<String> fieldsWantToSort = new ArrayList<String>();
		fieldsWantToSort.add(firstSortBy);
		fieldsWantToSort.add(secondSortBy);
		Sort sort = new Sort(Direction.ASC, fieldsWantToSort);
		// another way to sort
		// Sort anotherWayToSort = Sort.by(firstSortBy).ascending().and(Sort.by(secondSortBy).descending());
		List<Student> studentResponse = (List<Student>) studentRepository.findAll(sort);
		return studentResponse;
	}

	@Transactional
	public List<Student> getAllStudentsAsPageble(int pageIndex, int sizeOfPage) {

		Pageable pageable = PageRequest.of(pageIndex, sizeOfPage);
		Page<Student> page = studentRepository.findAll(pageable);
		if (page != null) {
			List<Student> studentResponse = (List<Student>) page.getContent();
			return studentResponse;
		}
		return null;
	}
	
	@Transactional
	public List<Student> getAllStudentsSortedAsWellPageble(int pageIndex, int sizeOfPage, String sortByParameter) {

		Pageable pageable = PageRequest.of(pageIndex, sizeOfPage, Direction.ASC,sortByParameter);
		Page<Student> page = studentRepository.findAll(pageable);
		if (page != null) {
			List<Student> studentResponse = (List<Student>) page.getContent();
			//this studentResponse will be sorted as well pageable
			return studentResponse;
		}
		return null;
	}


}

SpringMain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.*")
@EntityScan("com.javatute.entity")
public class SpringMain {
	public static void main(String[] args) {

		SpringApplication.run(SpringMain.class, args);
	}

}

Note – See more details about @ComponentScan here.

JpaConfig.java

package com.javatute.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

Note – See more details about @Configuration annotations here.

application.properties

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle2
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql=true 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

#show sql values
logging.level.org.hibernate.type.descriptor.sql=trace

#hibernate.show_sql = true
#spring.jpa.hibernate.logging.level.sql =FINE
#show sql statement
#logging.level.org.hibernate.SQL=debug

Let’s run the SpringMain class(run as java application) and do some testing for Sorting And Pagination in Spring Data JPA.

Perform saveall operation first using below REST API.

http://localhost:9091/student/saveall

[
    {
        "name": "john",
        "rollNumber": "120",
        "university":"rgtu"
    },
    {
        "name": "mark",
        "rollNumber": "121",
        "university":"rgtu"
    },
    {
        "name": "aric",
        "rollNumber": "123",
        "university":"rgtu"
    },
    {
        "name": "hira",
        "rollNumber": "124",
        "university":"rgtu"
    }
]

Response Data.

[
    {
        "id": 1,
        "name": "john",
        "rollNumber": "120",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": "mark",
        "rollNumber": "121",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "aric",
        "rollNumber": "123",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": "hira",
        "rollNumber": "124",
        "university": "rgtu"
    }
]

http://localhost:9091/student/findallsorted/name/rollNumber

Generated Query in the above case.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ order by student0_.name asc, student0_.roll_number asc

http://localhost:9091/student/findallpageble/0/3

Generated Query in the above case.

Hibernate: select * from ( select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ ) where rownum <= ?

http://localhost:9091/student/findallsortedandpageble/0/4/name

Generated Query in the above case.

Brief about Sort class, Page interface, Slice interface and Pageable interface.

Sort class.

Let’s see some points about Sort class.

The Sort class is available in org.springframework.data.domain package contains following constructor and methods.

Important constructors.

public Sort(Direction direction, String... properties) {

}

public Sort(Direction direction, List<String> properties) {

}

Some more constructors have been defined in Sort but they are Deprecated.

Important methods of Sort class.

public static Sort by(String… properties).

public static Sort by(List<Order> orders).

public static Sort by(Order… orders).

public static Sort by(Direction direction, String… properties).

public static Sort unsorted().

public Sort descending().

public Sort ascending().

public boolean isSorted().

public boolean isUnsorted().

public Sort and(Sort sort).

public Order getOrderFor(String property).

public Iterator<Order> iterator().

Note – Order is the inner class defined in Sort class.

Page interface.

The Page interface extends Slice interface contains following methods.

public static Page<T> empty().

static <T> Page<T> empty(Pageable pageable).

int getTotalPages().

long getTotalElements().

Page<map(Function<? super T, ? extends U> converter).

Slice interface.

The Slice interface extends Streamable interface, contains following methods.

int getNumber().

int getSize().

int getNumberOfElements();.

List<T> getContent().

boolean hasContent().

Sort getSort().

boolean isFirst().

boolean isLast().

boolean hasNext().

boolean hasPrevious().

Pageable getPageable().

Pageable nextPageable().

Pageable previousPageable().

Pageable interface.

static Pageable unpaged().

default boolean isPaged().

default boolean isUnpaged().

int getPageNumber().

int getPageSize().

long getOffset().

Sort getSort().

default Sort getSortOr(Sort sort).

Pageable next().

Pageable previousOrFirst().

Pageable first().

boolean hasPrevious().

That’s all about Sorting And Pagination in Spring Data JPA example using Spring Boot and Oracle.

Sorting And Pagination in Spring Data JPA Example using Spring Boot and oracle.

RELATED ARTICLESMORE FROM AUTHOR

Benefits of implementing Serializable interface while defining entity in JPA

How to check two entities are equal in JPA

How to create an entity using Spring Data JPA

RELATED ARTICLES MORE FROM AUTHOR