REST API Pagination in Spring Boot with PostgreSQL and Spring Data JPA
Spring Boot JPA pagination and sorting tutorial, Spring Data JPA pageable response example, Custom pageable response with metadata in Spring Boot, Spring Boot REST API pagination best practices, Spring Boot pagination with Page and Slice, Spring Boot pagination using Specification and Criteria API, How to implement infinite scroll with Spring Boot pagination, Spring Boot pagination and filter with JPA Specification, Spring Boot pagination using native SQL query, Spring Boot pageable and offset-limit query with PostgreSQL
Pagination is a critical feature in REST APIs, especially when dealing with large datasets. It ensures that data is delivered to clients in manageable, bite-sized pieces, optimizing both server performance and user experience. With Spring Boot and Spring Data JPA, developers can quickly implement efficient pagination while leveraging the power of PostgreSQL for database operations.
This guide will walk you through the entire process of implementing REST API pagination in Spring Boot with PostgreSQL and Spring Data JPA, covering practical use cases, advanced customizations, and common pitfalls to avoid.
Table of Contents
- RESTful Pagination Use Case Explained
- PostgreSQL Database Setup for Testing
- Spring Boot + JPA Pagination Endpoint
- Sorting with Sort.by(…)
- Customizing Pagination Metadata
- Building Reusable Pagination DTO
- Filtering + Pagination Using @Query or Specification
- Pagination with Native SQL in JPA
- Avoiding Common Pitfalls
- Final Code Structure and Optional GitHub Link
- FAQs
RESTful Pagination Use Case Explained
The concept of RESTful pagination revolves around breaking a dataset into smaller chunks (pages) and serving one page at a time via API endpoints. Instead of retrieving all records at once, which can overwhelm both server and client, paginated responses deliver only requested portions of the data efficiently.
Example Use Cases:
- E-commerce Applications: Paginated product listings, sorted by price or popularity.
- CRM Systems: Displaying customer data with pagination and filters.
- News Feeds: Returning paginated posts, sorted by published date.
By default, pagination combines well with query parameters like page and size, allowing clients to specify the exact portion of the data they need.
PostgreSQL Database Setup for Testing
Step 1: Install and Configure PostgreSQL
Download PostgreSQL and set it up locally or use a hosted solution like AWS RDS. Once installed, create a database for your project.
Example SQL command:
CREATE DATABASE springboot_pagination;
Step 2: Add PostgreSQL Configuration in application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/springboot_pagination spring.datasource.username=<your-username> spring.datasource.password=<your-password> spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=update
Step 3: Verify Database Connectivity
Use tools like DBeaver or pgAdmin to connect to your database and ensure it’s running as expected.
Spring Boot + JPA Pagination Endpoint
Step 1: Define JPA Entity
Here’s an example Product entity representing your database table:
@Entity
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private double price;
// Getters and setters
}Step 2: Create a Repository
Extend the JpaRepository interface to gain built-in paging support:
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}Step 3: Build the REST Endpoint
Use the Pageable interface to handle pagination parameters:
@RestController
@RequestMapping("/api/products")
public class ProductController {
private final ProductRepository productRepository;
public ProductController(ProductRepository productRepository) {
this.productRepository = productRepository;
}
@GetMapping
public Page<Product> getProducts(Pageable pageable) {
return productRepository.findAll(pageable);
}
}Test the endpoint using:
GET http://localhost:8080/api/products?page=0&size=10
Sorting with Sort.by(…)
Sort your paginated data dynamically using Spring’s Sort object.
Controller Example
@GetMapping("/sorted")
public Page<Product> getSortedProducts(
@RequestParam(defaultValue = "0") int page,
@RequestParam(defaultValue = "10") int size,
@RequestParam(defaultValue = "name,asc") String[] sort
) {
Sort.Direction direction = Sort.Direction.fromString(sort[1]);
PageRequest pageRequest = PageRequest.of(page, size, Sort.by(direction, sort[0]));
return productRepository.findAll(pageRequest);
}This allows sorting by name, price, or other fields by passing parameters like ?sort=price,desc.
Customizing Pagination Metadata
To provide helpful metadata (e.g., total pages, current page), use a wrapper object like PaginatedResponse.
Custom Wrapper Example:
public class PaginatedResponse<T> {
private List<T> data;
private long totalItems;
private int totalPages;
private int currentPage;
// Constructor and Getters
}Modify your controller:
@GetMapping
public PaginatedResponse<Product> getPaginatedProducts(Pageable pageable) {
Page<Product> page = productRepository.findAll(pageable);
return new PaginatedResponse<>(
page.getContent(),
page.getTotalElements(),
page.getTotalPages(),
page.getNumber()
);
}Building Reusable Pagination DTO
Reuse and maintain consistency in API responses by creating DTO classes. For example:
public class ProductDto {
private String name;
private double price;
public ProductDto(String name, double price) {
this.name = name;
this.price = price;
}
}Map entities to DTOs:
List<ProductDto> dtos = page.getContent()
.stream()
.map(product -> new ProductDto(product.getName(), product.getPrice()))
.collect(Collectors.toList());Filtering + Pagination Using @Query or Specification
Enhance pagination by filtering with @Query or JPA Specifications.
Example Filter Query:
@Query("SELECT p FROM Product p WHERE p.name LIKE %:keyword%")
Page<Product> findByNameContaining(@Param("keyword") String keyword, Pageable pageable);Redirect filter logic through your controller using:
@GetMapping("/search")
public Page<Product> searchProducts(@RequestParam String keyword, Pageable pageable) {
return productRepository.findByNameContaining(keyword, pageable);
}Pagination with Native SQL in JPA
When performance is critical, native SQL queries can help.
Example:
@Query(value = "SELECT * FROM products WHERE price > :minPrice", nativeQuery = true)
Page<Product> findByPriceGreaterThan(@Param("minPrice") double minPrice, Pageable pageable);Native queries directly leverage PostgreSQL’s strengths for optimized performance.
Avoiding Common Pitfalls
- Zero-Based Indexing: Spring’s pageable requests start from 0, not 1. Always communicate this to frontend teams.
- Large Page Sizes: Limit maximum
sizeparameters to avoid overwhelming the database. - Indexing Recommendations: Add indexes on frequently sorted or filtered columns to improve query efficiency.
Final Code Structure and Optional GitHub Link
- JPA Entity for data modeling.
- Repository for database interaction.
- Custom DTOs to shape API responses.
- Controller exposing paginated REST endpoints.
Optional repo link for full codebase setup: GitHub Repository.
FAQs
Q1. Why use Pageable instead of manually handling offsets?
Spring’s Pageable interface simplifies pagination implementation, ensuring cleaner code and built-in support for validation and metadata.
Q2. Can I use native SQL and JPA together?
Yes, native SQL is supported via the @Query annotation when optimized queries are necessary.
Q3. What’s the difference between Slice and Page?
Slice handles pagination without total metadata (e.g., total pages), while Page provides full pagination details.
Use this detailed guide to implement powerful pagination strategies in your Spring Boot applications with PostgreSQL and JPA.
