Sorting in Spring Data JPA using Spring Boot

January 26, 2020

In this post, we will see Sorting in Spring Data JPA with Spring Boot and Oracle. Spring Data JPA provides a predefined repository to perform pagination and sorting i.e PagingAndSortingRepository interface.

Before going ahead let’s see what are we going to do. We will have one entity Student.java which have four fields(id, name, university and rollNumber). First, we will save some student records in the database using REST API and then we will fetch all record using Spring Data JPA which would be in sorted order on basis of name. We will see different ways to implement sorting using Spring Data JPA. Let’s see the sample entity.

Student.java

@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;

}

PagingAndSortingRepository interface contains two methods.

Iterable<T> findAll(Sort sort) – use for sorting.

Page<T> findAll(Pageable pageable) – use for sorting pagination.

We are going to use the first one in our example later.

We will define our repository extending PagingAndSortingRepository as below.

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

	List<Student> findByUniversityOrderByNameAsc(String university);

	List<Student> findAllByOrderByNameAsc();
	
	@Query("select s from Student s")
	List<Student> findAllAndSortByName(Sort sort);
	
	@Query("select s from Student s where s.university = ?1")
	List<Student> findByUniversityAndSortByNameLength(String university, Sort sort);
}

We are going to perform sorting on based on the name field. Let’s see some different ways and different scenario to implement Sorting in Spring Data JPA.

The first scenario – Retrieve the data on the basis of one field(i.e university) and sort on the basis of another field(name).

public List<Student> findByUniversity(String university) {
List<Student> response = studentRepository.findByUniversityOrderByNameAsc(university);
return response;
}

The second scenario – Retrieve all record(rows) and sort on the basis of some field(name).

public List<Student> findAll() {
List<Student> response = (List<Student>) studentRepository.findAllByOrderByNameAsc();
return response;
}

The third scenario – Retrieve all record(rows) and sort on the basis of some field(name). This is same to second one only different is way to write the query.

public List<Student> findAllUsinSortAsParameter() {
List<Student> response = (List<Student>) studentRepository.findAll(Sort.by(Sort.Direction.ASC, “name”));
return response;
}

The fourth scenario – Retrieve the data on the basis of one field(i.e university) and sort on basis of some other field lenght. for example we want to sort on basis of lenght of name(the student which name is short in length should come first).

public List<Student> findByUniversityAndSortByNameLength() {
List<Student> response = studentRepository.findByUniversityAndSortByNameLength(“rgtu”, JpaSort.unsafe(“LENGTH(name)”));
return response;
}

The fifth scenario – Retrieve all record(rows) and sort on the basis of some field(name). This is similar to the previous one only different is the way to write the query.

public List<Student> findAllAndSortByName() {
List<Student> response = studentRepository.findAllAndSortByName(new Sort(“name”));
return response;
}

Note – Sort class is available in org.springframework.data.domain package which provides different methods to perform even complex sorting. See Docs here.

First, we will save and fetch records using the below rest API.

http://localhost:9091/student/save – POST operation

Request Data – for save call.

[
    {
        "name": "Hiteshdo",
        "rollNumber": "0126CS01",
        "university":"rgtu"
    },
    {
        "name": "Johnhjhjhjhj",
        "rollNumber": "0126CS02",
        "university":"rgtu"
    },
    {
        "name": "Mohankkkkkkkkkkkkkk",
        "rollNumber": "0126CS03",
        "university":"rgtu"
    },
    {
        "name": "Nagesh",
        "rollNumber": "0126CS04",
        "university":"rgtu"
    },
    {
        "name": "s",
        "rollNumber": "0126CS05",
        "university":"rgtu"
    },
    {
        "name": "Ranakum",
        "rollNumber": "0126CS06",
        "university":"rgtu"
    },
    {
        "name": "Roc",
        "rollNumber": "0126CS07",
        "university":"rgtu"
    },
    {
        "name": "Simpy",
        "rollNumber": "0126CS08",
        "university":"rgtu"
    },
    {
        "name": "Tiwari",
        "rollNumber": "0126CS09",
        "university":"rgtu"
    },    {
        "name": "Appu",
        "rollNumber": "0126CS10",
        "university":"rgtu"
    },
    {
        "name": "Babloo",
        "rollNumber": "0126CS11",
        "university":"rgtu"
    },
    {
        "name": "Ga",
        "rollNumber": "0126CS12",
        "university":"rgtu"
    }
]

Then we will perform sorting using below API.

http://localhost:9091/student/rgtu – GET operation

http://localhost:9091/student/findallorderbyname-GET operation

http://localhost:9091/student/findallUsinsortasparameter- GET operation

http://localhost:9091/student/findbyuniversityandsortbynamelength-GET operation

http://localhost:9091/student/findallandsortbyname- GET operation

Sorting in Spring Data JPA with Spring Boot and Oracle Example from scratch.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next. Fill all details(GroupId – springdatajpasorting, ArtifactId – springdatajpasorting and name- criteriasortingexample) 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>springdatajpasorting</groupId>
	<artifactId>springdatajpasorting</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springdatajpasorting</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.

Directory structure –

Sorting in Spring Data JPA using Spring Boot

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 = "/save", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveBook(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveStudent(studentList);
		return studentResponse;
	}

	@RequestMapping(value = "/{university}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByUniversitySortByName(@PathVariable String university) {
		List<Student> studentList = (List<Student>) studentService.findByUniversitySortByName(university);
		return studentList;
	}

	@RequestMapping(value = "/findallorderbyname", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getAllStudents() {
		List<Student> studentList = (List<Student>) studentService.findAllOrderByName();
		return studentList;
	}

	@RequestMapping(value = "/findallUsinsortasparameter", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findAllUsinSortAsParameter() {
		List<Student> studentList = (List<Student>) studentService.findAllUsinSortAsParameter();
		return studentList;
	}
	
	@RequestMapping(value = "/findbyuniversityandsortbynamelength", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByUniversityAndSortByNameLength() {
		List<Student> studentList = (List<Student>) studentService.findByUniversityAndSortByNameLength();
		return studentList;
	}
	
	@RequestMapping(value = "/findallandsortbyname", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findAllAndSortByName() {
		List<Student> studentList = (List<Student>) studentService.findAllAndSortByName();
		return studentList;
	}

}

StudentRepository.java – interface

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

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

	List<Student> findByUniversityOrderByNameAsc(String university);

	List<Student> findAllByOrderByNameAsc();
	
	@Query("select s from Student s")
	List<Student> findAllAndSortByName(Sort sort);
	
	@Query("select s from Student s where s.university = ?1")
	List<Student> findByUniversityAndSortByNameLength(String university, Sort sort);
}

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> saveStudent(List<Student> studentList);

	public List<Student> findByUniversitySortByName(String university);

	public List<Student> findAllOrderByName();

	
	public List<Student> findAllUsinSortAsParameter();
	public List<Student> findByUniversityAndSortByNameLength();
	public List<Student> findAllAndSortByName();
	
}

StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.JpaSort;
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;

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

	@Autowired
	private StudentRepository studentRepository;

	@PersistenceContext
	EntityManager entityManager;

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

	@Transactional
	public List<Student> findByUniversitySortByName(String university) {
		List<Student> response = studentRepository.findByUniversityOrderByNameAsc(university);
		return response;
	}

	@Transactional
	public List<Student> findAllOrderByName() {
		List<Student> response = (List<Student>) studentRepository.findAllByOrderByNameAsc();
		return response;
	}

	@Transactional
	public List<Student> findAllUsinSortAsParameter() {
		List<Student> response = (List<Student>) studentRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));
		return response;
	}
	
	@Transactional
	public List<Student> findByUniversityAndSortByNameLength() {
		List<Student> response = studentRepository.findByUniversityAndSortByNameLength("rgtu", JpaSort.unsafe("LENGTH(name)")); 
		return response;
	}
	
	@Transactional
	public List<Student> findAllAndSortByName() {
		List<Student> response = studentRepository.findAllAndSortByName(new Sort("name")); 
		return response;
	}
	

}

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);
	}

}

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 {

}

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.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

Let’s run the SpringMain class(run as java application).

Save response –

[
    {
        "id": 1,
        "name": "Hiteshdo",
        "rollNumber": "0126CS01",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": "Johnhjhjhjhj",
        "rollNumber": "0126CS02",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "Mohankkkkkkkkkkkkkk",
        "rollNumber": "0126CS03",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": "Nagesh",
        "rollNumber": "0126CS04",
        "university": "rgtu"
    },
    {
        "id": 5,
        "name": "s",
        "rollNumber": "0126CS05",
        "university": "rgtu"
    },
    {
        "id": 6,
        "name": "Ranakum",
        "rollNumber": "0126CS06",
        "university": "rgtu"
    },
    {
        "id": 7,
        "name": "Roc",
        "rollNumber": "0126CS07",
        "university": "rgtu"
    },
    {
        "id": 8,
        "name": "Simpy",
        "rollNumber": "0126CS08",
        "university": "rgtu"
    },
    {
        "id": 9,
        "name": "Tiwari",
        "rollNumber": "0126CS09",
        "university": "rgtu"
    },
    {
        "id": 10,
        "name": "Appu",
        "rollNumber": "0126CS10",
        "university": "rgtu"
    },
    {
        "id": 11,
        "name": "Babloo",
        "rollNumber": "0126CS11",
        "university": "rgtu"
    },
    {
        "id": 12,
        "name": "Ga",
        "rollNumber": "0126CS12",
        "university": "rgtu"
    }
]

Let’s perform get operation one by one.

http://localhost:9091/student/rgtu – GET operation

Generated Query –

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_ where student0_.university=? order by student0_.name asc

http://localhost:9091/student/findallorderbyname-GET operation

Sorting in Spring Data JPA and Spring Boot

Generated Query –

http://localhost:9091/student/findallUsinsortasparameter- GET operation

Generate Query –

http://localhost:9091/student/findbyuniversityandsortbynamelength-GET operation

Sorting in Spring Data JPA with Spring Boot and Oracle

Generate Query –

http://localhost:9091/student/findallandsortbyname- GET operation

Generate Query –
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

That’s all about Sorting in Spring Data JPA with Spring Boot and Oracle.

Sorting in Spring Data JPA with Spring Boot and Oracle Example from scratch.

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