Spring Boot Pagination with Native Queries and Projections

Pagination is a vital feature in modern APIs, especially for applications that handle large datasets. While Spring Data JPA offers convenient tools for handling pagination, certain use cases demand advanced performance and flexibility that can only be achieved with native SQL queries and projections. This blog dives deep into how you can optimize your Spring Boot applications by pairing native queries with projections to craft efficient, scalable pagination solutions.

Table of Contents

1. Why Use Native Queries?
2. Creating Native Queries with Pagination (countQuery + value)
3. Using @Query with Pagination
4. Returning Projections or DTOs Instead of Entities
5. Example: Only Fetch Selected Columns

Why Use Native Queries?

Spring Data JPA abstracts much of the complexity of interacting with databases, but there are scenarios where native SQL queries offer distinct advantages, such as:

1. Fine-Tuned Query Performance

Native queries allow you to optimize database performance by explicitly defining how data should be fetched. For example, advanced joins or complex filtering queries can be written to minimize query execution time and resource consumption.

2. Leveraging Database-Specific Features

Different databases (e.g., MySQL, PostgreSQL) offer unique features that aren’t efficiently handled by JPA. Custom SQL queries leverage these features, such as window functions and common table expressions (CTEs), to improve performance.

3. Improved Control Over Pagination Logic

Native queries provide more control over the query structure for paginated results, especially when you need to avoid unnecessary operations like heavy joins, which are typically generated by JPA.

4. Integration with Custom or Legacy Databases

Many projects involve legacy databases where naming conventions and schemas don’t align with JPA’s default assumptions. Writing custom SQL queries simplifies integration drastically.

To further understand native queries and their use cases, refer to the Spring Data JPA Native Queries Documentation.

Creating Native Queries with Pagination (countQuery + value)

Pagination with native queries in Spring Boot requires two key components:

1. Main Query (value): Fetches the limited dataset for the requested page.
2. Count Query (countQuery): Determines the total number of rows matching the query conditions for all pages.

Spring Data JPA simplifies the implementation by allowing both queries to be defined within the @Query annotation.

Defining a Query for Paginated Results

Example Entity

Consider a Product entity with the following fields:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private Double price;
    private String category;
    private LocalDate createdDate;

    // Getters and Setters
}

Repository with Native Query

Here, we fetch paginated results for a specific product category sorted by price:

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query(
      value = "SELECT * FROM product WHERE category = :category ORDER BY price DESC LIMIT :limit OFFSET :offset", 
      countQuery = "SELECT COUNT(*) FROM product WHERE category = :category", 
      nativeQuery = true)
    Page<Product> findByCategoryWithPagination(@Param("category") String category, Pageable pageable);
}

• value retrieves the page of data, applying limits and offsets for pagination.
• countQuery computes the total number of results for all pages, crucial for implementing Spring Data’s pagination features effectively.

Service Layer Example

To make this functionality reusable:

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public Page<Product> getProductsByCategory(String category, int page, int size) {
        Pageable pageable = PageRequest.of(page, size);
        return productRepository.findByCategoryWithPagination(category, pageable);
    }
}

Key Advantages

1. Pages are fetched efficiently using SQL’s LIMIT and OFFSET.
2. Total row count information enables seamless integration with frontend pagination controls.

To learn more, refer to the Spring Data Pagination Documentation.

Using @Query with Pagination

For cases where native SQL isn’t required, Spring Boot’s @Query annotation simplifies custom JPQL queries with pagination. JPQL provides database independence and works well when the entity model aligns with query needs.

JPQL Example

Below is an example of fetching paginated products using JPQL:

@Query("SELECT p FROM Product p WHERE p.category = :category ORDER BY p.price DESC")
Page<Product> findProductsByCategory(@Param("category") String category, Pageable pageable);

This approach automatically handles count queries, further reducing boilerplate.

Best Practices

• Use JPQL when queries can operate directly on JPA entities.
• Opt for native queries when raw database performance is a priority.

Returning Projections or DTOs Instead of Entities

When API clients only need a subset of fields, returning full entity objects leads to unnecessary data transfer. Projections and DTOs help optimize responses by fetching only the required fields directly from the database.

Understanding Projections

Projections allow you to specify selected fields for retrieval. Spring Data JPA natively supports interface-based projections.

Define a Projection Interface

public interface ProductSummary {
    String getName();
    Double getPrice();
}

Repository with Projection Query

@Query(value = "SELECT name, price FROM product WHERE category = :category ORDER BY price DESC", nativeQuery = true)
Page<ProductSummary> findProductSummaries(@Param("category") String category, Pageable pageable);

This fetches and returns only name and price fields.

To learn more about projections, visit the Spring Data Projections Documentation.

Using DTOs for Transformations

DTOs are ideal for scenarios where you need to process or format the data before returning it.

Define a DTO Class

public class ProductDTO {
    private String name;
    private Double price;

    public ProductDTO(String name, Double price) {
        this.name = name;
        this.price = price;
    }
    // Getters and Setters
}

Query Mapping to DTO

@Query(value = "SELECT new com.example.ProductDTO(p.name, p.price) FROM Product p WHERE p.category = :category ORDER BY p.price DESC")
Page<ProductDTO> findProductDTOsByCategory(@Param("category") String category, Pageable pageable);

This allows the use of lightweight objects optimized for responses.

Example: Only Fetch Selected Columns

Sometimes, you only need specific columns from a large table. This can drastically reduce resource usage.

Native Query for Specific Columns

@Query(value = "SELECT id, name FROM product WHERE category = :category ORDER BY created_date DESC", nativeQuery = true)
Page<Map<String, Object>> findProductIdsAndNames(@Param("category") String category, Pageable pageable);

Optimized Response Format

{
  "content": [
    { "id": 101, "name": "Laptop A" },
    { "id": 102, "name": "Laptop B" }
  ],
  "pageable": { "pageNumber": 0, "pageSize": 10 },
  "totalElements": 150
}

This approach avoids overloading the response with excess data and is particularly useful for lightweight frontend requests.

Final Thoughts

Combining native queries with projections offers a high degree of flexibility and performance in Spring Boot applications. Leveraging these techniques lets you craft APIs that are efficient and scalable, even for demanding use cases with large datasets.

Start incorporating these practices into your projects and explore official documentation for even more capabilities:

• Spring Data JPA Pagination
• Spring Data Projections
• Native Queries in JPA.

Implement these best practices today, and watch your APIs handle data at scale more efficiently than ever before!