How to select top 5 records in a table using Spring Data JPA

by

Last updated on June 28th, 2024

We can select the top 5 records from the database table using Spring Data JPA through the query method with the Top keyword, @Query annotation, Pageable interface for dynamic limits and Limit interface. In this topic, we will learn how to select the top 5 records from the database table in various ways.

top_5_records

1. Using Query Method with Top Keyword

Create a method query with the Top keyword in the repository interface. We need to create a method that starts with the find keyword followed by the Top keyword use the 5 number then use the By keyword after the use property name of the JPA entity class.

public interface UserRepository extends JpaRepository<User, Long> {
List<User> findTop5ByActive(boolean active);
}

If we want to fetch the top 5 records with OrderBy, we will create a custom query method in the repository interface.

public interface UserRepository extends JpaRepository<User, Long> {
List<User> findTop5ByOrderByIdDesc();
}

2. Using @Query Annotation

Create a custom method in the repository interface with @Query to define a query using JPQL and native query.

public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u FROM User u ORDER BY u.id DESC limit 5")
List<User> findTop5Records();
}

In the above, we used JPQL to define a query for fetching the top 5 records from the database table using a limit clause. The limit clause is used to fetch a limited number of records.

3. Using Pageable interface

We can use the query method findAll(Pageable pageable) of the JpaRepository of Spring Data JPA. In this method, we must pass the Pageable type parameter to fetch the top 5 records from the database table.

public interface UserRepository extends JpaRepository<User, Long> {

}

We need to create a Service class where we can use the findAll(Pageable pageable) of the JpaRepository.

@Service

public class UserServiceImpl implements UserService {

@Autowired

private UserRepository userRepo;

@Override

public Page<User> getTop5() {

Pageable topFive = PageRequest.of(0, 5);

return userRepo.findAll(topFive);

      }

}

4. Using Limit interface

We can create a custom query method in the repository interface and accept the Limit type parameter value to fetch the top 5 records from the database table. 

public interface UserRepository extends JpaRepository<User, Long> {
List<User>findByOrderByIdDesc(Limit limit);
}

We need to create a Service class where we can use the List<User>findByOrderByIdDesc(Limit limit) of the repository interface and pass the numeric value 5 in its of() method to fetch the top 5 records.

@Service

public class UserServiceImpl implements UserService {

@Autowired

private UserRepository userRepo;

@Override

public List<User> getTop5ByLimit() {

return userRepo.findByOrderByIdDesc(Limit.of(5));

      }

}

Implement Select Top 5 Records using Spring Boot

Make a Spring Boot restful web service Application to select the top 5 records in the JpaRepository of Spring Data JPA 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 H2  Database Configuration
  5. Creating a JPA Entity class
  6. Creating a JPA Repository
  7. Creating a Service Interface
  8. Implementing Service Interface
  9. Creating a Controller class
  10. Run the Spring Boot 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 make it from the IDE(STS, VS Code etc.). 

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’s workspace or you can import it into another IDE you use. You can refer to this article to create and set up the Spring Boot Project in Eclipse IDE.

Project Structure 

top_5_records

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>3.3.0</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>17</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>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</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>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

4. Defining H2 Database 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 class

We are creating a JPA Entity User with properties(id, name, email and active) to create a table in the database.

User.java

package com.springjava.entity;

import jakarta.persistence.Entity;

import jakarta.persistence.GeneratedValue;

import jakarta.persistence.GenerationType;

import jakarta.persistence.Id;

import jakarta.persistence.Table;

import lombok.Data;

@Data

@Entity

@Table(name = "user_tbl")

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 @Table annotation is used to assign the name of the table which will be created 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 repository interface UserRepository for the User entity class. Adding some methods and use its query method to get the top 5 records from the user_tbl database table.

UserRepository.java

package com.springjava.repository;

import java.util.List;

import org.springframework.data.domain.Limit;

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

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

import com.springjava.entity.User;

public interface UserRepository extends JpaRepository < User, Long > {

  List < User > findTop5ByActive(boolean active);

  List < User > findTop5ByOrderByIdDesc();

  @Query("SELECT u FROM User u ORDER BY u.id DESC limit 5")

  List < User > findTop5Records();

  List < User > findByOrderByIdDesc(Limit limit);

}

7. Creating a Service Interface

We are creating Service Interface UserService to declare the methods for selecting top 5 records.

UserService.java

package com.springjava.service;

import java.util.List;

import org.springframework.data.domain.Page;

import com.springjava.entity.User;

public interface UserService {

  void saveAll(List < User > userList);

  List < User > getTop5ByActive(boolean active);

  List < User > getTop5ByIdDesc();

  List < User > getTop5Records();

  Page < User > getTop5();

  List < User > getTop5ByLimit();

}

8. Implementing Service Interface

We are creating the Service class UserServiceImpl to implement the UserService interface and provide the implementation of its methods. This class is annotated with @Service annotation provides business functionality for this application. Injecting the UserRepository in the class to use its method for fetching the top 5 records from the database table.

UserServiceImpl.java

package com.springjava.service;

import java.util.List;

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

import org.springframework.data.domain.Limit;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.PageRequest;

import org.springframework.data.domain.Pageable;

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 > getTop5ByActive(boolean active) {

    return userRepo.findTop5ByActive(active);

  }

  @Override

  public List < User > getTop5ByIdDesc() {

    return userRepo.findTop5ByOrderByIdDesc();

  }

  @Override

  public List < User > getTop5Records() {

    return userRepo.findTop5Records();

  }

  @Override

  public Page < User > getTop5() {

    Pageable topFive = PageRequest.of(0, 5);

    return userRepo.findAll(topFive);

  }

  @Override

  public List < User > getTop5ByLimit() {

    return userRepo.findByOrderByIdDesc(Limit.of(5));

  }

}

9. Creating a Controller class

We are creating a Controller class UserController to create API endpoint methods that handle requests from the client.

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.data.domain.Page;

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("/get-top5/{active}")

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

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

    List < User > userList = userService.getTop5ByActive(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("/get-top5-order-by-id")

  public ResponseEntity < ? > getTop5ByOrderByIdDesc() {

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

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

    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("/get-top5-with-query")

  public ResponseEntity < ? > getTop5WithQuery() {

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

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

    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("/get-top5-with-pageable")

  public ResponseEntity < ? > getTop5WithPageable() {

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

    Page < User > data = userService.getTop5();

    List < User > userList = data.getContent();

    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("/get-top5-with-limit")

  public ResponseEntity < ? > getTop5WithLimit() {

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

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

    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 RestController for giving responses in JSON form.
➔ This @RequestMapping annotation to define the base URL for the application.
➔ These @PostMapping and @GetMapping annotations to handle HTTP requests from the client.
➔ This ResponseEntity is used to represent the entire HTTP response.
➔ This @Autowired  annotation is used to inject UserService in the class.
➔ This @RequestBody annotation is used to take a JSON array in the save() method as the List of User class parameter.

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.

This application is running successfully then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: 
create table user_tbl (id bigint generated by default as identity, active boolean not null, email varchar(255), name varchar(255), primary key (id))

H2 Database Console

If we want to check the H2 database console then we need to browse this url on the browser.  

JSON Array

We are creating a sample JSON Array to test the API http://localhost:8080/api/user/save-all.

[
  {
    "name": "Test",
    "email": "test@gmail.com",
    "active": true
  },
  {
    "name": "ABC",
    "email": "abc@gmail.com",
    "active": true
  },
  {
    "name": "DEF",
    "email": "def@gmail.com",
    "active": true
  },
  {
    "name": "XYZ",
    "email": "xyz@gmail.com",
    "active": true
  },
  {
    "name": "PQR",
    "email": "pqr@gmail.com",
    "active": true
  },
  {
    "name": "JKL",
    "email": "jkl@gmail.com",
    "active": true
  },
  {
    "name": "MNO",
    "email": "mno@gmail.com",
    "active": false
  }
]

Test the APIs on the Postman Tool

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

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)
Hibernate: insert into user_tbl (active,email,name,id) values (?,?,?,default)

Check the Table

top_5_records

GET: http://localhost:8080/api/user/get-top5/true

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select u1_0.id,u1_0.active,u1_0.email,u1_0.name from user_tbl u1_0 where u1_0.active=? fetch first ? rows only

GET: http://localhost:8080/api/user/get-top5-order-by-id

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select u1_0.id,u1_0.active,u1_0.email,u1_0.name from user_tbl u1_0 order by u1_0.id desc fetch first ? rows only

GET: http://localhost:8080/api/user/get-top5-with-query

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select u1_0.id,u1_0.active,u1_0.email,u1_0.name from user_tbl u1_0 order by u1_0.id desc fetch first 5 rows only

GET: http://localhost:8080/api/user/get-top5-with-pageable

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select u1_0.id,u1_0.active,u1_0.email,u1_0.name from user_tbl u1_0 offset ? rows fetch first ? rows only
Hibernate: select count(u1_0.id) from user_tbl u1_0

GET: http://localhost:8080/api/user/get-top5-with-limit

top_5_records

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select u1_0.id,u1_0.active,u1_0.email,u1_0.name from user_tbl u1_0 order by u1_0.id desc offset ? rows fetch first ? rows only

Conclusion

In this topic, we learnt various ways to select the top 5 records using Spring Data JPA with Spring Boot restful web service application.

Leave a Comment