How to use order by in JPA Repository

by

We can use order by in the JPA repository by using the OrderBy keyword in the finder methods of that JPA repository of Spring Data JPA. We need to add the finder method with the combination of the orderBy keyword to filter and sort records of the database table. Add a method starting with the find keyword followed by the By keyword, then use the property name of the JPA Entity class followed by the OrderBy keyword, then use the property name of the JPA Entity class and the suffix with the Asc or Desc keyword in that method [findByPropertyNameOrderByPropertyNameAsc/Desc()] in the interface, which is extended by JpaRepository.

order_by_in_jpa_repository

Use findBy with Order By in JpaRepository

Let us consider a JPA Entity class User with some properties which makes fields in the table of the database.
User.java

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import lombok.Data;

@Data

@Entity

public class User {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String email;

  private boolean active;

}

We need to fetch records from the database based on the property name of User entity class and order of the records are ascending or descending then we are adding finder methods (findBy) with OrderBy in the JpaRepository extended interface UserRepository.
UserRepository.java

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.springjava.entity.User;

public interface UserRepository extends JpaRepository < User, Long > {

  List < User > findByActiveOrderByIdDesc(boolean active);

  List < User > findByActiveOrderByIdAsc(boolean active);

}

Implementing OrderBy with findBy method in JpaRepository

Let us make step-by-step a Spring Boot Restful web services application using Maven, Spring Web, Spring Data JPA, Lombok and H2 database for implementing OrderBy keyword in the JpaRepository. 
These are the following steps:

  1. Creating a Spring Boot Starter Project
  2. Keep the IDE ready
  3. Maven Dependency
  4. Defining the configuration
  5. Creating a JPA Entity
  6. Creating a JPA Repository
  7. Creating a Service Interface
  8. Creating a Service class
  9. Creating a Rest Controller class
  10. Run the Spring Application and Check

1. Creating a Spring Boot Starter Project

We are creating a Spring Boot Application from the web tool Spring Initializr or you can create it from the IDE(STS, VS Code etc.) that you are using. 
Add the following dependencies:

  • Spring Web
  • Spring Data JPA
  • Lombok
  • H2 Database

2. Keep the IDE ready

We are importing this created application into our Eclipse IDE or you can import it into another IDE you are using. You can refer to this article to create and set up the Spring Boot Project in Eclipse IDE.

Project Structure 

3. Maven Dependency

Here is the complete pom.xml file for the Spring Boot Application.
pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.3</version>
		<relativePath />
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

4. Defining the configuration

We are configuring the H2 database configuration in the application.properties file.
application.properties

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

5. Creating a JPA Entity

We are creating a JPA entity class User with these properties(id, name, email and active ).
User.java

package com.springjava.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import lombok.Data;

@Data

@Entity

public class User {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String name;

  private String email;

  private boolean active;

}
  • This @Data annotation is used for a constructor, setter method, getter method, etc.
  • This @Entity annotation is used to create a table through Java code in the database. 
  • This @Id annotation is used to create a primary key of the entity class.
  • This @GeneratedValue(strategy = GenerationType.IDENTITY) annotation is used to generate increment value of the specified property of entity class

6. Creating a JPA Repository

We are creating a JPA Repository to interact with the JPA Entity class and adding two methods [findByActiveOrderByIdDesc(boolean active) and findByActiveOrderByIdAsc(boolean active)].
UserRepository.java

package com.springjava.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.springjava.entity.User;

public interface UserRepository extends JpaRepository < User, Long > {

  List < User > findByActiveOrderByIdDesc(boolean active);

  List < User > findByActiveOrderByIdAsc(boolean active);

}
  • List<User> findByActiveOrderByIdDesc(boolean active): This method is added for fetching records based on the active property of the User class in descending order based on the id property of the User class by passing boolean parameter value.
  • List<User> findByActiveOrderByIdAsc(boolean active): This method is added for fetching records based on the active property of the User class in ascending order based on the id property of the User class by passing boolean parameter value.

7. Creating a Service Interface

We are creating a Service interface with some method declaration [saveAll(List userList), getByActiveByOrderByIdDesc(boolean active) and deleteColumn()]. So the implementation class of this interface overrides these declared methods.
UserService.java

package com.springjava.service;

import java.util.List;

import com.springjava.entity.User;

public interface UserService {

  void saveAll(List < User > userList);

  List < User > getByActiveByOrderByIdDesc(boolean active);

  List < User > getByActiveByOrderByIdAsc(boolean active);

}

8. Creating a Service class

We are creating a Service class UserServiceImpl and this class is implementing the UserService interface. 
UserServiceImpl.java

package com.springjava.service;

import java.util.List;

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

import org.springframework.stereotype.Service;

import com.springjava.entity.User;

import com.springjava.repository.UserRepository;

@Service

public class UserServiceImpl implements UserService {

@Autowired

private UserRepository userRepo;

@Override

public void saveAll(List<User> userList) {

    userRepo.saveAll(userList);

}

@Override

public List<User> getByActiveByOrderByIdDesc(boolean active) {

return userRepo.findByActiveOrderByIdDesc(active);

}

@Override

public List<User> getByActiveByOrderByIdAsc(boolean active) {

return userRepo.findByActiveOrderByIdAsc(active);

}

}
  • We used @Autowired annotation to inject UserRepository in this service class.
  • We used saveAll(), findByActiveOrderByIdDesc() and findByActiveOrderByIdAsc() methods of that UserRepository.
  • This @Service annotation is a stereotype annotation of the Spring Framework. This annotation marks the class as a service provider for the application.This annotation is used in class where it provides business functionalities.

9. Creating a Rest Controller class

We are creating a Rest Controller class UserController in which all methods are created for API endpoints for handling requests from the clients. 
UserController.Java

package com.springjava.controller;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

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

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.springjava.entity.User;

import com.springjava.service.UserService;

@RestController

@RequestMapping("/api/user")

public class UserController {

  @Autowired

  private UserService userService;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < User > users) {

    Map < String, Object > respUser = new LinkedHashMap < String, Object > ();

    userService.saveAll(users);

    respUser.put("status", 1);

    respUser.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respUser, HttpStatus.CREATED);

  }

  @GetMapping("/find-by-order-by-desc/{active}")

  public ResponseEntity < ? > getUserListDesc(@PathVariable boolean active) {

    Map < String, Object > respUser = new LinkedHashMap < String, Object > ();

    List < User > userList = userService.getByActiveByOrderByIdDesc(active);

    if (!userList.isEmpty()) {

      respUser.put("status", 1);

      respUser.put("data", userList);

      return new ResponseEntity < > (respUser, HttpStatus.OK);

    } else {

      respUser.clear();

      respUser.put("status", 0);

      respUser.put("message", "Data is not found");

      return new ResponseEntity < > (respUser, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/find-by-order-by-asc/{active}")

  public ResponseEntity < ? > getUserListAsc(@PathVariable boolean active) {

    Map < String, Object > respUser = new LinkedHashMap < String, Object > ();

    List < User > userList = userService.getByActiveByOrderByIdAsc(active);

    if (!userList.isEmpty()) {

      respUser.put("status", 1);

      respUser.put("data", userList);

      return new ResponseEntity < > (respUser, HttpStatus.OK);

    } else {

      respUser.clear();

      respUser.put("status", 0);

      respUser.put("message", "Data is not found");

      return new ResponseEntity < > (respUser, HttpStatus.NOT_FOUND);

    }

  }

}
  • This class is annotated with @RestController annotation to make this class act as Rest Controller for giving responses in JSON form.
  • We used @RequestMapping annotation to define the base URL for the application.
  • We used @PostMapping and @GetMapping annotations to handle HTTP requests from the client.
  • We used ResponseEntity to represent the entire HTTP response.
  • We used @Autowired  annotation to inject UserService in the class.
  • We used @RequestBody annotation to take a JSON array in the save() method as the List of User class parameter.
  • We are making three API endpoints[@PostMapping(“/save-all”), @GetMapping(“/find-by-order-by-desc/{active}”) and @GetMapping(“/find-by-order-by-desc/{active}”)]
  • This API endpoint [@PostMapping(“/save-all”)] is for accepting the JSON array as request body.
  • This API endpoint[@GetMapping(“/find-by-order-by-desc/{active}”)] is for getting responses based on the value passed in the url.
  • This API endpoint[@GetMapping(“/find-by-order-by-desc/{active}”)] is for getting responses based on the value passed in the url.
  • We used @PathVariable annotation to pass the value on the url to get the response.  
  • We have created two restful web services handling methods[save(), getUserListDesc() and getUserListAsc()].
  • save(): This saves the list of user records into the database.
  • getUserListDesc(): This method is created for getting records in descending order based on the primary key column from the user table.
  • getUserListAsc(): This method is created for getting records in ascending order based on the primary key column from the user table.

10. Run the Spring Boot Application and Check

Right-click this Spring Boot application on the DemoApplication.java, then click Run As, and select Java Application. 

Check H2 Database

Checking the H2 database console we need browse this URL “http://localhost:8080/h2-console”.

order_by_in_jpa_repository

Click on the Connect button.

See the table below:

order_by_in_jpa_repository

Creating a JSON Array

We created a JSON array to pass on the API as a request for the testing endpoint  http://localhost:8080/api/user/save-all.

[
  {
    "name": "Test",
    "email": "test@gmail.com",
    "active": true
  },
  {
    "name": "ABC",
    "email": "abc@gmail.com",
    "active": true
  },
  {
    "name": "XYZ",
    "email": "xyz@gmail.com",
    "active": true
  },
  {
    "name": "PQR",
    "email": "pqr@gmail.com",
    "active": true
  }
]

Testing API on the Postman Tool

Saving the user data

POST: http://localhost:8080/api/user/save-all

order_by_in_jpa_repository

Check the table:

order_by_in_jpa_repository

After this API hits Spring Data JPA (internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: insert into user (id, active, email, name) values (null, ?, ?, ?)
Hibernate: insert into user (id, active, email, name) values (null, ?, ?, ?)
Hibernate: insert into user (id, active, email, name) values (null, ?, ?, ?)
Hibernate: insert into user (id, active, email, name) values (null, ?, ?, ?)

Fetching list of user in descending order

GET: http://localhost:8080/api/user/find-by-order-by-desc/true

order_by_in_jpa_repository

After this API hits Spring Data JPA (internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: select user0_.id as id1_0_, user0_.active as active2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.active=? order by user0_.id desc

Fetching list of user in descending order

GET: http://localhost:8080/api/user/find-by-order-by-asc/true

order_by_in_jpa_repository

After this API hit Spring Data JPA (internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: select user0_.id as id1_0_, user0_.active as active2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.active=? order by user0_.id asc

Conclusion

In this topic, we learnt how to implement the OrderBy keyword with findBy methods of the JpaRepository using Spring Boot Restful web services application.

Leave a Comment