MACRO NEPAL
Pagination and Sorting in Spring Data JPA
awiskar acharya
November 5, 2025
2:22 pm

Table of Contents

  1. Introduction to Pagination and Sorting
  2. Spring Data JPA Repository Setup
  3. Basic Pagination Implementation
  4. Sorting Strategies
  5. Advanced Pagination Techniques
  6. REST API Implementation
  7. Performance Optimization
  8. Testing Pagination and Sorting

Introduction to Pagination and Sorting

Pagination and sorting are essential features for building scalable and user-friendly applications. They help manage large datasets by breaking them into manageable chunks and organizing data in a meaningful way.

Key Benefits:

  • Improved Performance: Reduce database load and network transfer
  • Better UX: Users can navigate through data efficiently
  • Scalability: Handle large datasets without performance degradation
  • Flexibility: Customizable page sizes and sorting criteria

Spring Data JPA Repository Setup

1. Domain Models

// Product.java
@Entity
@Table(name = "products")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@Column(length = 1000)
private String description;
@Column(nullable = false)
private Double price;
@Column(nullable = false)
private String category;
@Column(name = "created_date")
private LocalDateTime createdDate;
@Column(name = "stock_quantity")
private Integer stockQuantity;
@Column(name = "is_active")
private Boolean isActive = true;
// Constructors
public Product() {}
public Product(String name, String description, Double price, String category) {
this.name = name;
this.description = description;
this.price = price;
this.category = category;
this.createdDate = LocalDateTime.now();
}
// Getters and setters
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getDescription() { return description; }
public void setDescription(String description) { this.description = description; }
public Double getPrice() { return price; }
public void setPrice(Double price) { this.price = price; }
public String getCategory() { return category; }
public void setCategory(String category) { this.category = category; }
public LocalDateTime getCreatedDate() { return createdDate; }
public void setCreatedDate(LocalDateTime createdDate) { this.createdDate = createdDate; }
public Integer getStockQuantity() { return stockQuantity; }
public void setStockQuantity(Integer stockQuantity) { this.stockQuantity = stockQuantity; }
public Boolean getIsActive() { return isActive; }
public void setIsActive(Boolean isActive) { this.isActive = isActive; }
}
// User.java
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String firstName;
@Column(nullable = false)
private String lastName;
@Column(nullable = false, unique = true)
private String email;
@Column(name = "registration_date")
private LocalDateTime registrationDate;
@Column(nullable = false)
private String country;
@Column(name = "last_login")
private LocalDateTime lastLogin;
// Constructors
public User() {}
public User(String firstName, String lastName, String email, String country) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.country = country;
this.registrationDate = LocalDateTime.now();
}
// Getters and setters
// ... similar to Product class
}

2. Repository Interfaces

// ProductRepository.java
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
// Basic pagination methods
Page<Product> findAll(Pageable pageable);
// Pagination with filtering
Page<Product> findByCategory(String category, Pageable pageable);
Page<Product> findByPriceBetween(Double minPrice, Double maxPrice, Pageable pageable);
Page<Product> findByNameContainingIgnoreCase(String name, Pageable pageable);
Page<Product> findByIsActiveTrue(Pageable pageable);
// Count queries for pagination info
long countByCategory(String category);
long countByPriceBetween(Double minPrice, Double maxPrice);
// Custom query with pagination
@Query("SELECT p FROM Product p WHERE p.category = :category AND p.price > :minPrice")
Page<Product> findPremiumProductsInCategory(@Param("category") String category, 
@Param("minPrice") Double minPrice, 
Pageable pageable);
// Native query with pagination
@Query(value = "SELECT * FROM products p WHERE p.stock_quantity > 0 AND p.is_active = true", 
countQuery = "SELECT count(*) FROM products p WHERE p.stock_quantity > 0 AND p.is_active = true",
nativeQuery = true)
Page<Product> findAvailableProducts(Pageable pageable);
}
// UserRepository.java
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
Page<User> findAll(Pageable pageable);
Page<User> findByCountry(String country, Pageable pageable);
Page<User> findByRegistrationDateAfter(LocalDateTime date, Pageable pageable);
Page<User> findByLastNameContainingIgnoreCase(String lastName, Pageable pageable);
@Query("SELECT u FROM User u WHERE u.firstName LIKE %:name% OR u.lastName LIKE %:name%")
Page<User> findByFirstNameOrLastNameContaining(@Param("name") String name, Pageable pageable);
// Multiple sorting criteria
Page<User> findByCountryOrderByRegistrationDateDesc(String country, Pageable pageable);
}

Basic Pagination Implementation

1. Service Layer Implementation

// ProductService.java
@Service
@Transactional
public class ProductService {
private final ProductRepository productRepository;
public ProductService(ProductRepository productRepository) {
this.productRepository = productRepository;
}
// Basic pagination
public Page<Product> getAllProducts(Pageable pageable) {
return productRepository.findAll(pageable);
}
// Pagination with filtering
public Page<Product> getProductsByCategory(String category, Pageable pageable) {
return productRepository.findByCategory(category, pageable);
}
public Page<Product> searchProducts(String searchTerm, Pageable pageable) {
return productRepository.findByNameContainingIgnoreCase(searchTerm, pageable);
}
public Page<Product> getProductsByPriceRange(Double minPrice, Double maxPrice, Pageable pageable) {
return productRepository.findByPriceBetween(minPrice, maxPrice, pageable);
}
public Page<Product> getActiveProducts(Pageable pageable) {
return productRepository.findByIsActiveTrue(pageable);
}
// Custom pagination method
public Page<Product> getPremiumProductsInCategory(String category, Double minPrice, Pageable pageable) {
return productRepository.findPremiumProductsInCategory(category, minPrice, pageable);
}
// Get available products with native query
public Page<Product> getAvailableProducts(Pageable pageable) {
return productRepository.findAvailableProducts(pageable);
}
}
// UserService.java
@Service
@Transactional
public class UserService {
private final UserRepository userRepository;
public UserService(UserRepository userRepository) {
this.userRepository = userRepository;
}
public Page<User> getAllUsers(Pageable pageable) {
return userRepository.findAll(pageable);
}
public Page<User> getUsersByCountry(String country, Pageable pageable) {
return userRepository.findByCountry(country, pageable);
}
public Page<User> getRecentUsers(LocalDateTime sinceDate, Pageable pageable) {
return userRepository.findByRegistrationDateAfter(sinceDate, pageable);
}
public Page<User> searchUsersByName(String name, Pageable pageable) {
return userRepository.findByFirstNameOrLastNameContaining(name, pageable);
}
}

2. Custom Page Response DTO

// PageResponse.java
public class PageResponse<T> {
private List<T> content;
private int currentPage;
private int totalPages;
private long totalElements;
private int pageSize;
private boolean first;
private boolean last;
private boolean empty;
private int numberOfElements;
// Constructors
public PageResponse() {}
public PageResponse(Page<T> page) {
this.content = page.getContent();
this.currentPage = page.getNumber();
this.totalPages = page.getTotalPages();
this.totalElements = page.getTotalElements();
this.pageSize = page.getSize();
this.first = page.isFirst();
this.last = page.isLast();
this.empty = page.isEmpty();
this.numberOfElements = page.getNumberOfElements();
}
public PageResponse(List<T> content, Pageable pageable, long totalElements) {
this.content = content;
this.currentPage = pageable.getPageNumber();
this.pageSize = pageable.getPageSize();
this.totalElements = totalElements;
this.totalPages = (int) Math.ceil((double) totalElements / pageSize);
this.first = currentPage == 0;
this.last = currentPage == totalPages - 1;
this.empty = content.isEmpty();
this.numberOfElements = content.size();
}
// Getters and setters
public List<T> getContent() { return content; }
public void setContent(List<T> content) { this.content = content; }
public int getCurrentPage() { return currentPage; }
public void setCurrentPage(int currentPage) { this.currentPage = currentPage; }
public int getTotalPages() { return totalPages; }
public void setTotalPages(int totalPages) { this.totalPages = totalPages; }
public long getTotalElements() { return totalElements; }
public void setTotalElements(long totalElements) { this.totalElements = totalElements; }
public int getPageSize() { return pageSize; }
public void setPageSize(int pageSize) { this.pageSize = pageSize; }
public boolean isFirst() { return first; }
public void setFirst(boolean first) { this.first = first; }
public boolean isLast() { return last; }
public void setLast(boolean last) { this.last = last; }
public boolean isEmpty() { return empty; }
public void setEmpty(boolean empty) { this.empty = empty; }
public int getNumberOfElements() { return numberOfElements; }
public void setNumberOfElements(int numberOfElements) { this.numberOfElements = numberOfElements; }
}

Sorting Strategies

1. Basic Sorting Implementation

// SortingService.java
@Service
public class SortingService {
private final ProductRepository productRepository;
private final UserRepository userRepository;
public SortingService(ProductRepository productRepository, UserRepository userRepository) {
this.productRepository = productRepository;
this.userRepository = userRepository;
}
// Single field sorting
public List<Product> getProductsSortedByPrice(String direction) {
Sort sort = direction.equalsIgnoreCase("desc") 
? Sort.by("price").descending() 
: Sort.by("price").ascending();
return productRepository.findAll(sort);
}
public List<Product> getProductsSortedByName() {
return productRepository.findAll(Sort.by("name").ascending());
}
// Multiple field sorting
public List<Product> getProductsSortedByCategoryAndPrice() {
Sort sort = Sort.by("category").ascending()
.and(Sort.by("price").descending());
return productRepository.findAll(sort);
}
public List<User> getUsersSortedByRegistrationDateAndName() {
Sort sort = Sort.by("registrationDate").descending()
.and(Sort.by("firstName").ascending())
.and(Sort.by("lastName").ascending());
return userRepository.findAll(sort);
}
// Dynamic sorting from query parameters
public List<Product> getProductsWithDynamicSorting(List<String> sortFields, String direction) {
List<Sort.Order> orders = new ArrayList<>();
for (String field : sortFields) {
Sort.Order order = direction.equalsIgnoreCase("desc") 
? Sort.Order.desc(field) 
: Sort.Order.asc(field);
orders.add(order);
}
Sort sort = Sort.by(orders);
return productRepository.findAll(sort);
}
// Case-insensitive sorting
public List<User> getUsersSortedByEmailIgnoreCase() {
Sort sort = Sort.by(Sort.Order.by("email").ignoreCase());
return userRepository.findAll(sort);
}
}

2. Advanced Sorting with Pagination

// AdvancedPaginationService.java
@Service
public class AdvancedPaginationService {
private final ProductRepository productRepository;
private final UserRepository userRepository;
public AdvancedPaginationService(ProductRepository productRepository, UserRepository userRepository) {
this.productRepository = productRepository;
this.userRepository = userRepository;
}
// Pagination with single sort
public Page<Product> getProductsWithPaginationAndSorting(int page, int size, String sortBy, String direction) {
Sort sort = direction.equalsIgnoreCase("desc") 
? Sort.by(sortBy).descending() 
: Sort.by(sortBy).ascending();
Pageable pageable = PageRequest.of(page, size, sort);
return productRepository.findAll(pageable);
}
// Pagination with multiple sorting
public Page<Product> getProductsWithMultipleSorting(int page, int size, String[] sort) {
List<Sort.Order> orders = new ArrayList<>();
for (String sortOrder : sort) {
String[] _sort = sortOrder.split(",");
orders.add(new Sort.Order(getSortDirection(_sort[1]), _sort[0]));
}
Pageable pageable = PageRequest.of(page, size, Sort.by(orders));
return productRepository.findAll(pageable);
}
// Complex pagination with filtering and sorting
public Page<Product> getFilteredAndSortedProducts(String category, Double minPrice, Double maxPrice, 
int page, int size, String sortBy, String direction) {
Sort sort = Sort.by(sortBy);
sort = direction.equalsIgnoreCase("desc") ? sort.descending() : sort.ascending();
Pageable pageable = PageRequest.of(page, size, sort);
if (category != null && minPrice != null && maxPrice != null) {
return productRepository.findByCategoryAndPriceBetween(category, minPrice, maxPrice, pageable);
} else if (category != null) {
return productRepository.findByCategory(category, pageable);
} else if (minPrice != null && maxPrice != null) {
return productRepository.findByPriceBetween(minPrice, maxPrice, pageable);
} else {
return productRepository.findAll(pageable);
}
}
// Helper method to get Sort Direction
private Sort.Direction getSortDirection(String direction) {
if (direction.equalsIgnoreCase("desc")) {
return Sort.Direction.DESC;
}
return Sort.Direction.ASC;
}
// Custom sorting with JPA query
public Page<User> getUsersSortedByCustomCriteria(int page, int size) {
Pageable pageable = PageRequest.of(page, size, 
Sort.by("lastLogin").descending()
.and(Sort.by("registrationDate").ascending()));
return userRepository.findAll(pageable);
}
}

Advanced Pagination Techniques

1. Custom Pagination Utility

// PaginationUtils.java
@Component
public class PaginationUtils {
public static Pageable createPageable(Integer page, Integer size, String sortBy, String direction) {
if (page == null) page = 0;
if (size == null) size = 20;
if (sortBy == null) sortBy = "id";
if (direction == null) direction = "asc";
Sort sort = Sort.by(sortBy);
sort = direction.equalsIgnoreCase("desc") ? sort.descending() : sort.ascending();
return PageRequest.of(page, size, sort);
}
public static Pageable createPageableWithMultipleSort(Integer page, Integer size, String[] sort) {
if (page == null) page = 0;
if (size == null) size = 20;
List<Sort.Order> orders = new ArrayList<>();
if (sort != null) {
for (String sortOrder : sort) {
String[] _sort = sortOrder.split(",");
Sort.Direction sortDirection = _sort[1].equalsIgnoreCase("desc") 
? Sort.Direction.DESC 
: Sort.Direction.ASC;
orders.add(new Sort.Order(sortDirection, _sort[0]));
}
} else {
orders.add(new Sort.Order(Sort.Direction.ASC, "id"));
}
return PageRequest.of(page, size, Sort.by(orders));
}
public static <T> PageResponse<T> toPageResponse(Page<T> page) {
return new PageResponse<>(page);
}
public static <T> PageResponse<T> toPageResponse(List<T> content, Pageable pageable, long totalElements) {
return new PageResponse<>(content, pageable, totalElements);
}
// Validate pagination parameters
public static void validatePaginationParams(Integer page, Integer size) {
if (page != null && page < 0) {
throw new IllegalArgumentException("Page index must not be less than zero");
}
if (size != null && size < 1) {
throw new IllegalArgumentException("Page size must not be less than one");
}
if (size != null && size > 1000) {
throw new IllegalArgumentException("Page size must not be greater than 1000");
}
}
}

2. Slice-based Pagination

// SlicePaginationService.java
@Service
public class SlicePaginationService {
private final ProductRepository productRepository;
public SlicePaginationService(ProductRepository productRepository) {
this.productRepository = productRepository;
}
// Using Slice for better performance (no count query)
public Slice<Product> getProductsSlice(int page, int size) {
Pageable pageable = PageRequest.of(page, size, Sort.by("id").ascending());
return productRepository.findAll(pageable);
}
public Slice<Product> searchProductsSlice(String searchTerm, int page, int size) {
Pageable pageable = PageRequest.of(page, size, Sort.by("name").ascending());
return productRepository.findByNameContainingIgnoreCase(searchTerm, pageable);
}
// Convert Slice to custom response
public <T> Map<String, Object> sliceToResponse(Slice<T> slice) {
Map<String, Object> response = new HashMap<>();
response.put("content", slice.getContent());
response.put("currentPage", slice.getNumber());
response.put("pageSize", slice.getSize());
response.put("first", slice.isFirst());
response.put("last", slice.isLast());
response.put("hasNext", slice.hasNext());
response.put("hasPrevious", slice.hasPrevious());
response.put("numberOfElements", slice.getNumberOfElements());
return response;
}
}

3. Cursor-based Pagination

// CursorPaginationService.java
@Service
public class CursorPaginationService {
private final ProductRepository productRepository;
public CursorPaginationService(ProductRepository productRepository) {
this.productRepository = productRepository;
}
// Cursor-based pagination for infinite scroll
public List<Product> getProductsAfterCursor(Long cursorId, int limit) {
return productRepository.findByIdGreaterThanOrderByIdAsc(cursorId, 
PageRequest.of(0, limit, Sort.by("id").ascending()));
}
public List<Product> getProductsBeforeCursor(Long cursorId, int limit) {
return productRepository.findByIdLessThanOrderByIdDesc(cursorId, 
PageRequest.of(0, limit, Sort.by("id").descending()));
}
// Date-based cursor pagination
public List<Product> getProductsAfterDate(LocalDateTime cursorDate, int limit) {
return productRepository.findByCreatedDateAfterOrderByCreatedDateAsc(cursorDate, 
PageRequest.of(0, limit));
}
// Price-based cursor pagination
public List<Product> getProductsAfterPrice(Double cursorPrice, Long cursorId, int limit) {
return productRepository.findByPriceGreaterThanOrPriceEqualsAndIdGreaterThanOrderByPriceAscIdAsc(
cursorPrice, cursorPrice, cursorId, PageRequest.of(0, limit));
}
// Custom cursor response
public static class CursorResponse<T> {
private List<T> items;
private String nextCursor;
private boolean hasMore;
// Constructors, getters, setters
public CursorResponse(List<T> items, String nextCursor, boolean hasMore) {
this.items = items;
this.nextCursor = nextCursor;
this.hasMore = hasMore;
}
// Getters and setters
public List<T> getItems() { return items; }
public void setItems(List<T> items) { this.items = items; }
public String getNextCursor() { return nextCursor; }
public void setNextCursor(String nextCursor) { this.nextCursor = nextCursor; }
public boolean isHasMore() { return hasMore; }
public void setHasMore(boolean hasMore) { this.hasMore = hasMore; }
}
}

REST API Implementation

1. Controller Implementation

// ProductController.java
@RestController
@RequestMapping("/api/products")
@Validated
public class ProductController {
private final ProductService productService;
private final AdvancedPaginationService advancedPaginationService;
public ProductController(ProductService productService, AdvancedPaginationService advancedPaginationService) {
this.productService = productService;
this.advancedPaginationService = advancedPaginationService;
}
// Basic pagination with query parameters
@GetMapping
public ResponseEntity<PageResponse<Product>> getAllProducts(
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "id") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
PaginationUtils.validatePaginationParams(page, size);
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<Product> productsPage = productService.getAllProducts(pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(productsPage));
}
// Filtered pagination
@GetMapping("/category/{category}")
public ResponseEntity<PageResponse<Product>> getProductsByCategory(
@PathVariable String category,
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "name") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<Product> productsPage = productService.getProductsByCategory(category, pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(productsPage));
}
// Search with pagination
@GetMapping("/search")
public ResponseEntity<PageResponse<Product>> searchProducts(
@RequestParam String q,
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "name") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<Product> productsPage = productService.searchProducts(q, pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(productsPage));
}
// Advanced filtering with multiple parameters
@GetMapping("/filter")
public ResponseEntity<PageResponse<Product>> filterProducts(
@RequestParam(required = false) String category,
@RequestParam(required = false) Double minPrice,
@RequestParam(required = false) Double maxPrice,
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "price") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<Product> productsPage = advancedPaginationService.getFilteredAndSortedProducts(
category, minPrice, maxPrice, page, size, sortBy, direction);
return ResponseEntity.ok(PaginationUtils.toPageResponse(productsPage));
}
// Multiple sorting fields
@GetMapping("/sorted")
public ResponseEntity<PageResponse<Product>> getProductsWithMultipleSorting(
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(required = false) String[] sort) {
Pageable pageable = PaginationUtils.createPageableWithMultipleSort(page, size, sort);
Page<Product> productsPage = productService.getAllProducts(pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(productsPage));
}
// Slice-based pagination
@GetMapping("/slice")
public ResponseEntity<Map<String, Object>> getProductsSlice(
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size) {
Slice<Product> productsSlice = productService.getProductsSlice(page, size);
Map<String, Object> response = new HashMap<>();
response.put("products", productsSlice.getContent());
response.put("hasNext", productsSlice.hasNext());
response.put("currentPage", productsSlice.getNumber());
return ResponseEntity.ok(response);
}
}

2. User Controller with Advanced Features

// UserController.java
@RestController
@RequestMapping("/api/users")
@Validated
public class UserController {
private final UserService userService;
private final SortingService sortingService;
public UserController(UserService userService, SortingService sortingService) {
this.userService = userService;
this.sortingService = sortingService;
}
@GetMapping
public ResponseEntity<PageResponse<User>> getAllUsers(
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "id") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<User> usersPage = userService.getAllUsers(pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(usersPage));
}
@GetMapping("/country/{country}")
public ResponseEntity<PageResponse<User>> getUsersByCountry(
@PathVariable String country,
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size,
@RequestParam(defaultValue = "registrationDate") String sortBy,
@RequestParam(defaultValue = "desc") String direction) {
Pageable pageable = PaginationUtils.createPageable(page, size, sortBy, direction);
Page<User> usersPage = userService.getUsersByCountry(country, pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(usersPage));
}
@GetMapping("/search")
public ResponseEntity<PageResponse<User>> searchUsers(
@RequestParam String name,
@RequestParam(defaultValue = "0") Integer page,
@RequestParam(defaultValue = "20") Integer size) {
Pageable pageable = PageRequest.of(page, size, Sort.by("firstName").ascending());
Page<User> usersPage = userService.searchUsersByName(name, pageable);
return ResponseEntity.ok(PaginationUtils.toPageResponse(usersPage));
}
// Sorting only (no pagination)
@GetMapping("/sorted")
public ResponseEntity<List<User>> getSortedUsers(
@RequestParam(defaultValue = "firstName") String sortBy,
@RequestParam(defaultValue = "asc") String direction) {
List<User> users = sortingService.getUsersWithDynamicSorting(
Arrays.asList(sortBy), direction);
return ResponseEntity.ok(users);
}
// Multiple field sorting
@GetMapping("/sorted/multiple")
public ResponseEntity<List<User>> getUsersWithMultipleSorting() {
List<User> users = sortingService.getUsersSortedByRegistrationDateAndName();
return ResponseEntity.ok(users);
}
}

Performance Optimization

1. Repository Optimization

// OptimizedProductRepository.java
@Repository
public interface OptimizedProductRepository extends JpaRepository<Product, Long> {
// Use @EntityGraph to avoid N+1 queries
@EntityGraph(attributePaths = {"category"})
Page<Product> findAll(Pageable pageable);
@EntityGraph(attributePaths = {"category"})
Page<Product> findByCategory(String category, Pageable pageable);
// Use projection for better performance
@Query("SELECT p.id as id, p.name as name, p.price as price FROM Product p WHERE p.category = :category")
Page<ProductSummary> findProductSummariesByCategory(@Param("category") String category, Pageable pageable);
// Custom interface for projection
public interface ProductSummary {
Long getId();
String getName();
Double getPrice();
}
// Use native query for complex operations
@Query(value = """
SELECT p.*, 
(SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) as order_count
FROM products p 
WHERE p.is_active = true 
ORDER BY order_count DESC
""", 
countQuery = "SELECT COUNT(*) FROM products p WHERE p.is_active = true",
nativeQuery = true)
Page<Product> findPopularProducts(Pageable pageable);
}
// Index suggestions for better performance
/*
CREATE INDEX idx_product_category ON products(category);
CREATE INDEX idx_product_price ON products(price);
CREATE INDEX idx_product_active ON products(is_active);
CREATE INDEX idx_user_country ON users(country);
CREATE INDEX idx_user_registration_date ON users(registration_date);
*/

2. Service Layer Optimization

// OptimizedPaginationService.java
@Service
@Transactional(readOnly = true)
public class OptimizedPaginationService {
private final OptimizedProductRepository optimizedProductRepository;
public OptimizedPaginationService(OptimizedProductRepository optimizedProductRepository) {
this.optimizedProductRepository = optimizedProductRepository;
}
// Use read-only transactions for queries
public Page<Product> getProductsOptimized(Pageable pageable) {
return optimizedProductRepository.findAll(pageable);
}
// Use projections to reduce data transfer
public Page<OptimizedProductRepository.ProductSummary> getProductSummaries(String category, Pageable pageable) {
return optimizedProductRepository.findProductSummariesByCategory(category, pageable);
}
// Batch processing for large datasets
public void processProductsInBatches(int batchSize, Consumer<List<Product>> processor) {
int page = 0;
Page<Product> productPage;
do {
Pageable pageable = PageRequest.of(page, batchSize, Sort.by("id").ascending());
productPage = optimizedProductRepository.findAll(pageable);
processor.accept(productPage.getContent());
page++;
} while (productPage.hasNext());
}
// Cache frequently accessed paginated results
@Cacheable(value = "products", key = "#category + '-' + #pageable.pageNumber + '-' + #pageable.pageSize")
public Page<Product> getCachedProductsByCategory(String category, Pageable pageable) {
return optimizedProductRepository.findByCategory(category, pageable);
}
}

Testing Pagination and Sorting

1. Repository Tests

// ProductRepositoryTest.java
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class ProductRepositoryTest {
@Autowired
private TestEntityManager entityManager;
@Autowired
private ProductRepository productRepository;
@BeforeEach
void setUp() {
// Create test data
Product product1 = new Product("Laptop", "Gaming laptop", 999.99, "Electronics");
Product product2 = new Product("Phone", "Smartphone", 699.99, "Electronics");
Product product3 = new Product("Book", "Programming book", 39.99, "Books");
Product product4 = new Product("Chair", "Office chair", 199.99, "Furniture");
entityManager.persist(product1);
entityManager.persist(product2);
entityManager.persist(product3);
entityManager.persist(product4);
entityManager.flush();
}
@Test
void whenFindAllWithPagination_thenReturnPage() {
// Given
Pageable pageable = PageRequest.of(0, 2, Sort.by("name").ascending());
// When
Page<Product> result = productRepository.findAll(pageable);
// Then
assertThat(result.getContent()).hasSize(2);
assertThat(result.getTotalElements()).isEqualTo(4);
assertThat(result.getTotalPages()).isEqualTo(2);
assertThat(result.getContent().get(0).getName()).isEqualTo("Book");
}
@Test
void whenFindByCategoryWithPagination_thenReturnFilteredPage() {
// Given
Pageable pageable = PageRequest.of(0, 10, Sort.by("price").descending());
// When
Page<Product> result = productRepository.findByCategory("Electronics", pageable);
// Then
assertThat(result.getContent()).hasSize(2);
assertThat(result.getContent().get(0).getPrice()).isEqualTo(999.99);
}
@Test
void whenSearchProducts_thenReturnMatchingResults() {
// Given
Pageable pageable = PageRequest.of(0, 10);
// When
Page<Product> result = productRepository.findByNameContainingIgnoreCase("lap", pageable);
// Then
assertThat(result.getContent()).hasSize(1);
assertThat(result.getContent().get(0).getName()).isEqualTo("Laptop");
}
}

2. Service Layer Tests

// ProductServiceTest.java
@ExtendWith(SpringExtension.class)
@SpringBootTest
class ProductServiceTest {
@Autowired
private ProductService productService;
@Autowired
private ProductRepository productRepository;
@BeforeEach
void setUp() {
productRepository.deleteAll();
// Create test data
List<Product> products = Arrays.asList(
new Product("Product A", "Description A", 100.0, "Category1"),
new Product("Product B", "Description B", 200.0, "Category1"),
new Product("Product C", "Description C", 300.0, "Category2"),
new Product("Product D", "Description D", 400.0, "Category2"),
new Product("Product E", "Description E", 500.0, "Category3")
);
productRepository.saveAll(products);
}
@Test
void whenGetAllProductsWithPagination_thenReturnCorrectPage() {
// Given
Pageable pageable = PageRequest.of(0, 2, Sort.by("name").ascending());
// When
Page<Product> result = productService.getAllProducts(pageable);
// Then
assertThat(result.getContent()).hasSize(2);
assertThat(result.getTotalElements()).isEqualTo(5);
assertThat(result.getTotalPages()).isEqualTo(3);
}
@Test
void whenGetProductsByCategory_thenReturnFilteredResults() {
// Given
Pageable pageable = PageRequest.of(0, 10);
// When
Page<Product> result = productService.getProductsByCategory("Category1", pageable);
// Then
assertThat(result.getContent()).hasSize(2);
assertThat(result.getContent())
.extracting(Product::getCategory)
.containsOnly("Category1");
}
@Test
void whenSearchProducts_thenReturnMatchingProducts() {
// Given
Pageable pageable = PageRequest.of(0, 10);
// When
Page<Product> result = productService.searchProducts("Product", pageable);
// Then
assertThat(result.getContent()).hasSize(5);
}
@Test
void whenGetProductsByPriceRange_thenReturnProductsInRange() {
// Given
Pageable pageable = PageRequest.of(0, 10);
// When
Page<Product> result = productService.getProductsByPriceRange(200.0, 400.0, pageable);
// Then
assertThat(result.getContent()).hasSize(2);
assertThat(result.getContent())
.extracting(Product::getPrice)
.allMatch(price -> price >= 200.0 && price <= 400.0);
}
}

3. Controller Tests

// ProductControllerTest.java
@WebMvcTest(ProductController.class)
class ProductControllerTest {
@Autowired
private MockMvc mockMvc;
@MockBean
private ProductService productService;
@MockBean
private AdvancedPaginationService advancedPaginationService;
@Test
void whenGetAllProducts_thenReturnPaginatedResponse() throws Exception {
// Given
List<Product> products = Arrays.asList(
new Product("Product1", "Desc1", 100.0, "Cat1"),
new Product("Product2", "Desc2", 200.0, "Cat1")
);
Page<Product> productPage = new PageImpl<>(products, PageRequest.of(0, 20), 2);
given(productService.getAllProducts(any(Pageable.class))).willReturn(productPage);
// When & Then
mockMvc.perform(get("/api/products")
.param("page", "0")
.param("size", "20")
.param("sortBy", "name")
.param("direction", "asc"))
.andExpect(status().isOk())
.andExpect(jsonPath("$.content").isArray())
.andExpect(jsonPath("$.content.length()").value(2))
.andExpect(jsonPath("$.totalElements").value(2))
.andExpect(jsonPath("$.totalPages").value(1))
.andExpect(jsonPath("$.currentPage").value(0));
}
@Test
void whenGetProductsByCategory_thenReturnFilteredResponse() throws Exception {
// Given
List<Product> products = Collections.singletonList(
new Product("Product1", "Desc1", 100.0, "Electronics")
);
Page<Product> productPage = new PageImpl<>(products);
given(productService.getProductsByCategory(eq("Electronics"), any(Pageable.class)))
.willReturn(productPage);
// When & Then
mockMvc.perform(get("/api/products/category/Electronics")
.param("page", "0")
.param("size", "10"))
.andExpect(status().isOk())
.andExpect(jsonPath("$.content[0].category").value("Electronics"));
}
@Test
void whenSearchProducts_thenReturnSearchResults() throws Exception {
// Given
List<Product> products = Arrays.asList(
new Product("Laptop", "Gaming laptop", 999.99, "Electronics")
);
Page<Product> productPage = new PageImpl<>(products);
given(productService.searchProducts(eq("laptop"), any(Pageable.class)))
.willReturn(productPage);
// When & Then
mockMvc.perform(get("/api/products/search")
.param("q", "laptop"))
.andExpect(status().isOk())
.andExpect(jsonPath("$.content[0].name").value("Laptop"));
}
}

Conclusion

Pagination and sorting are crucial for building efficient and user-friendly applications with Spring Data JPA. By implementing the patterns and techniques shown in this guide, you can:

  1. Handle large datasets efficiently with proper pagination
  2. Provide flexible sorting options for better user experience
  3. Optimize performance with proper indexing and query optimization
  4. Build robust APIs with comprehensive error handling and validation
  5. Test thoroughly to ensure pagination and sorting work correctly

Remember to choose the right pagination strategy (offset-based vs cursor-based) based on your specific use case and consider performance implications when working with large datasets.

Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper