How to use Limit Query Results in JPA Repository

We can use limit query results in JPA Repository to get limit records from the database table using the query method with the First or Top keyword, @Query annotation with limit clause, Pageable, and Limit interfaces. In this topic, we will learn how to use limit query results in JPA Repository in various ways.

Use Limit Query Results in JPA Repository

These are the following methods to use limit query results in JPA Repository:

1. Using Query Method with First or Top Keyword

Create a method with the First or Top keyword in the repository interface. This method must begin with the findFirst or findTop keyword followed by some numeric(N) with the By keyword and then followed by the field name (property name) of the JPA entity class.

Example

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

• In the above findFirst3ByActive will limit the records by giving three records from the database table.

2. Using @Query Method limit clause

Create a custom method in the repository interface. The method must be annotated with @Query annotation to define native SQL native or JPQL with a limit clause.

Example

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

• The limit clause returns the number of records as per the specified number.

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 get the limit records from the database table.

Example

@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepo;

@Override
public Page<User> getLimitRecordsWithPageable() {
Pageable page = PageRequest.of(0, 3);
return userRepo.findAll(page);
   }
}

• Pageable: It Provides pagination and sorting capabilities.
• PageRequest.of(page, size): It Specifies the page number and page size (limit).

4. Using Limit interface

We need to add a custom query method with a Limit type parameter in the repository interface.

Example

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

Use this method in the Service class by injecting the repository interface then call this findByOrderByIdDesc(Limit limit).

Example

@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepo;

@Override
public List<User> getRecordByLimit(){
return userRepo.findByOrderByIdDesc(Limit.of(3));
     }
}

• We are calling the method by passing Limit.of(3) to return three records from the database.

Implement Limit Query Results in JPA Repository

Make a Spring Boot restful web service application to select the 3 records in the JpaRepository of Spring Data JPA step-by-step in the above-mentioned ways.

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 

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 generating constructor, setter method, getter method, etc automatically.

6. Creating a JPA Repository

We are creating a repository interface UserRepository for the User entity class. Adding some and using its query method to get the 3 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> findFirst3ByActive(boolean active);
@Query("SELECT u FROM User u ORDER BY u.id ASC limit 3")
List<User> findLimitRecords();
List<User> findByOrderByIdDesc(Limit limit);
}

7. Creating a Service Interface

We are creating a Service Interface UserService to declare the methods for limiting only 3 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> getFirst3ByActive(boolean active);
List<User> getLimitRecords();
Page<User>  getLimitRecordsWithPageable();
List<User> getRecordByLimit();
}

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, annotated with the @Service annotation, provides business functionality for this application. We are injecting the UserRepository in the class to use its method to limit the database table to 3 records.

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> getFirst3ByActive(boolean active) {
return userRepo.findFirst3ByActive(active);
}

@Override
public List<User> getLimitRecords(){
return userRepo.findLimitRecords();
}

@Override
public Page<User> getLimitRecordsWithPageable() {
Pageable page = PageRequest.of(0, 3);
return userRepo.findAll(page);
}

@Override
public List<User> getRecordByLimit(){
return userRepo.findByOrderByIdDesc(Limit.of(3));
  }
}

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("/limiting-with-first/{active}")
public ResponseEntity<?> limitingWithFirst(@PathVariable boolean active) {
Map<String, Object> respUser = new LinkedHashMap<String, Object>();
List<User> userList=userService.getFirst3ByActive(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("/limiting-with-jpql")
public ResponseEntity<?> limitingWithJPQL() {
Map<String, Object> respUser = new LinkedHashMap<String, Object>();
List<User> userList=userService.getLimitRecords();
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("/limiting-with-pageable")
public ResponseEntity<?> limitingWithPageable() {
Map<String, Object> respUser = new LinkedHashMap<String, Object>();
Page<User> data=userService.getLimitRecordsWithPageable();
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("/limiting-with-limit")
public ResponseEntity<?> limitingWithLimit() {
Map<String, Object> respUser = new LinkedHashMap<String, Object>();
List<User> userList=userService.getRecordByLimit();
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

Check the Table

GET: http://localhost:8080/api/user/limiting-with-first/true

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/limiting-with-jpql

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 fetch first 3 rows only

GET: http://localhost:8080/api/user/limiting-with-pageable

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/limiting-with-limit

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 limit query results in JPA Repository using Spring Data JPA with Spring Boot restful web service application.