Sorting in Spring Data JPA using Spring Boot

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.

Sorting in Spring Data JPA using Spring Boot

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

 

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.

 

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.

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

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

 

StudentController.java

StudentRepository.java – interface

StudentService.java – interface

 

StudentServiceImpl.java

SpringMain.java

 

JpaConfig.java

application.properties

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

 

Save response –

 

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 –

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

 

 

http://localhost:9091/student/findallUsinsortasparameter- 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

 

 

 

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

Sorting in Spring Data JPA with Spring Boot and Oracle

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

 

 

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

Sorting in Spring Data JPA with Spring Boot and Oracle

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.

You may like.

 

See docs here.

 

 

Top