JpaRepository findAll Order By

Order by clause is used to select from the database in two orders(ascending and descending) based on the column name. We can use findAll order by in JpaRepository by adding methods starting with findAllBy followed by OrderBy with property_name of JPA Entity class Asc or Desc keyword suffix[findAllByOrderByIdAsc() and findAllByOderByIdDesc()]. In this topic, we will learn how to use findAll in order by in JpaRepository using the Spring Boot application with Maven, Spring Web, Spring Data JPA, Lombok and H2 database.

Let’s create a Spring Boot restful web services application to implement findAll() query method with order by keyword in the JpaRepository step-by-step
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.) 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 of findAll Order By

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 to a constructor, setter method, getter method, etc.
• This @Entity annotation is used to create a table through Java code in the database. 

6. Creating a JPA Repository

We are creating a JPA Repository to interact with the JPA Entity class.
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 > findAllByOrderByIdDesc();

  List < User > findAllByOrderByIdAsc();

}

• findAllByOrderByIdDesc(): Adding this method to fetch the list of users in descending order based id property of the User entity class. 
• findAllByOrderByIdAsc(): Adding this method to fetch the list of users in ascending order based id property of the User entity class. 

7. Creating a Service Interface

We are creating a Service interface with some method declaration[saveAll(List userList), getAllOrderByIdDesc() and getAllOrderByIdAsc()]. 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 > getAllOrderByIdDesc();

 List < User > getAllOrderByIdAsc();

}

8. Creating a Service class

We are creating a Service class UserServiceImpl and this class is implementing the UserService interface. This class is annotated with @Service annotation to act service. 
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 > getAllOrderByIdDesc() {

    return userRepo.findAllByOrderByIdDesc();

  }

  @Override

  public List < User > getAllOrderByIdAsc() {

    return userRepo.findAllByOrderByIdAsc();

  }

}

• We used @Autowired annotation to inject UserRepository in this service class.
• We used saveAll() findAllByOrderByIdDesc() and findAllByOrderByIdAsc() query methods of that JPA Repository.

9. Creating a Rest Controller class

We are creating a RestController 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.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-all-order-by-desc")

  public ResponseEntity < ? > getUserListDesc() {

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

    List < User > userList = userService.getAllOrderByIdDesc();

    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-all-order-by-asc")

  public ResponseEntity < ? > getUserListAsc() {

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

    List < User > userList = userService.getAllOrderByIdAsc();

    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 response 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 JSON object in the save() method as the List of User class parameter.
• We have created two restful web services handling methods[save(), getUserListDesc() and getUserListAs].
• save(): This saves the list of user records into the database.
• getUserListDesc(): This method is used to get the list of users in descending order based on the id from the database.
• getUserListAsc(): This method is used to get the list of users in ascending order based on the id from the database.

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

Check the H2 database console and browse this URL “http://localhost:8080/h2-console”.

See the below table here:

Testing API on the Postman

Saving the user data
POST: http://localhost:8080/api/user/save-all

Check the table:

After this API hit 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, ?, ?, ?)

Getting the list of users in descending order based on the id
GET: http://localhost:8080/api/user/find-all-order-by-desc

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_ 
order by 
user0_.id desc

Getting the list of users in ascending order based on the id
GET: http://localhost:8080/api/user/find-all-order-by-asc

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_ 
order by 
user0_.id asc

Conclusion

In this topic, we learnt how to use the findAll() query method with Order By in JpaRespository using Spring Boot Restful web services application.