Custom pagination response in spring boot jpa with postgresql, Spring Data JPA pagination with custom query, Spring Boot pagination with custom query, Spring Boot pagination, sorting and filtering, Spring JPA pagination with native query example, Pageable Spring Boot, Pagination in Spring Boot REST API with JPA

Building a robust CRUD (Create, Read, Update, Delete) API is a fundamental skill for any backend developer. But what if you could take it a step further by implementing features like pagination and sorting? Not only do these additions enhance performance, but they also create a more user-friendly experience by efficiently managing large datasets in your applications.

This guide will walk you through creating a paginated and sortable CRUD API using Spring Boot, Spring Data JPA, and PostgreSQL. By the end, you’ll have a full-featured API ready to integrate with frontend frameworks like React or Angular, complete with Swagger UI documentation and best practices for testing.

CRUD App Overview: What You’ll Build
Setup PostgreSQL + Spring Data Project
Implement Pagination in GET /items Endpoint
Add Sorting Capability to List API
Create/Update/Delete Logic (CUD)
Integrate with Swagger UI
Custom Response Format with Pagination Info
Frontend Integration Hints (React/Angular)
Testing with cURL/Postman
Final Project Structure + GitHub Link

CRUD App Overview: What You’ll Build

You’ll create a RESTful API that manages Item entities in a PostgreSQL database. This API will include features such as:

Create, Read, Update, Delete (CRUD): Modify and retrieve item data.
Pagination: Fetch paginated lists of items.
Sorting: Sort items dynamically by various properties (e.g., name, price).
Custom Responses: Include metadata (e.g., total pages, current page).
Documentation with Swagger: Generate and test API documentation.

At the end of this tutorial, the application will be able to handle large datasets efficiently while maintaining clean and concise code.

Setup PostgreSQL + Spring Data Project

Step 1. Initialize the Project

Use Spring Initializr to bootstrap your Spring Boot application. Select the following dependencies:

Spring Web
Spring Data JPA
PostgreSQL Driver
Spring Boot DevTools (optional for hot reloading)

Step 2. Configure PostgreSQL Connection

Set up the connection credentials in the application.properties file:

spring.datasource.url=jdbc:postgresql://localhost:5432/crud_api
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Ensure PostgreSQL is running locally and the crud_api database has been created.

Step 3. Entity Class

Define the Item entity for storing data:

@Entity
public class Item {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private double price;

    // Getters and setters
}

Step 4. Repository Layer

Create a repository interface to interact with the database:

@Repository
public interface ItemRepository extends JpaRepository<Item, Long> {
}

Implement Pagination in GET /items Endpoint

Pagination allows users to retrieve data in smaller chunks, enhancing performance and user experience.

Controller Code:

@RestController
@RequestMapping("/api/items")
public class ItemController {

    private final ItemRepository itemRepository;

    public ItemController(ItemRepository itemRepository) {
        this.itemRepository = itemRepository;
    }

    @GetMapping
    public Page<Item> getAllItems(Pageable pageable) {
        return itemRepository.findAll(pageable);
    }
}

API Request Example:

GET /api/items?page=0&size=5

This request fetches the first 5 items.

Why Use Pageable?

The Pageable interface automatically extracts pagination parameters (page, size) from the request and simplifies database operations by turning them into offset-based queries.

Add Sorting Capability to List API

Extend the previous example by adding sorting.

Controller Update:

@GetMapping
public Page<Item> getAllItems(Pageable pageable) {
    return itemRepository.findAll(pageable);
}

Sorting is integrated automatically by adding ?sort=name,asc or ?sort=price,desc to your API request:

GET /api/items?page=0&size=5&sort=name,asc

For multi-field sorting:

GET /api/items?sort=name,asc&sort=price,desc

Create/Update/Delete Logic (CUD)

Implement the other CRUD operations to make the API complete.

Create

@PostMapping
public Item createItem(@RequestBody Item item) {
    return itemRepository.save(item);
}

Update

@PutMapping("/{id}")
public Item updateItem(@PathVariable Long id, @RequestBody Item itemDetails) {
    Item item = itemRepository.findById(id).orElseThrow(() -> new RuntimeException("Item not found"));
    item.setName(itemDetails.getName());
    item.setPrice(itemDetails.getPrice());
    return itemRepository.save(item);
}

Delete

@DeleteMapping("/{id}")
public ResponseEntity<?> deleteItem(@PathVariable Long id) {
    itemRepository.deleteById(id);
    return ResponseEntity.ok().build();
}

Integrate with Swagger UI

Add Swagger dependencies to your project:

implementation 'org.springdoc:springdoc-openapi-ui'

Access your API’s Swagger UI at /swagger-ui.html. Swagger automatically generates a user-friendly interface for testing endpoints, including query parameters for pagination and sorting.

Custom Response Format with Pagination Info

Spring’s Page object includes useful metadata such as total pages and elements. Wrap the response in a custom format for better usability.

Custom Wrapper:

public class PaginatedResponse<T> {
    private List<T> data;
    private int currentPage;
    private int totalPages;
    private long totalItems;

    // Constructor, getters, and setters
}

Use this wrapper in your controller:

@GetMapping
public PaginatedResponse<Item> getAllItems(Pageable pageable) {
    Page<Item> page = itemRepository.findAll(pageable);
    return new PaginatedResponse<>(page.getContent(), page.getNumber(), page.getTotalPages(), page.getTotalElements());
}

Frontend Integration Hints (React/Angular)

Example Workflow:

HTTP Requests: Use libraries like Axios (React) or HttpClient (Angular) to fetch paginated data.
Persist Pagination State: Maintain page, size, and sort in state variables to allow smooth page navigation.
Real-Time Sorting: Trigger new API calls based on user interactions like selecting sortable table headers.

Testing with cURL/Postman

Using cURL:

curl -X GET "http://localhost:8080/api/items?page=0&size=5&sort=name,asc"

Using Postman:

Set the request type to GET.
Enter http://localhost:8080/api/items and add query parameters (page, size, sort) as key-value pairs.
Test additional CRUD endpoints (POST, PUT, DELETE) with sample JSON payloads.

Final Project Structure + GitHub Link

Project Structure:

src/
|-- main/
    |-- java/
        |-- com.example.crud/
            |-- controller/
            |-- repository/
            |-- entity/
            |-- dto/

GitHub Repository: CRUD API with Spring Boot

FAQs

Q1. How does Pageable differ from manual limit-offset SQL queries?

Pageable integrates pagination, sorting, and metadata with minimal configuration.

Q2. Can I paginate on custom database queries?

Yes, use the @Query annotation with Pageable for custom queries.

This guide equips you to build a complete, paginated, and sortable CRUD API for managing large datasets efficiently. Now, it’s your turn to get started!

Table of Contents