Pageable & Sort — phân trang, sắp xếp, validate, projection DTO

public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByCategory(String category, Pageable pageable);
}

@RestController
@RequestMapping("/products")
public class ProductController {

    private final ProductRepository repo;

    @GetMapping
    public Page<ProductDto> list(
        @RequestParam(required = false) String category,
        Pageable pageable
    ) {
        Page<Product> page = category == null
            ? repo.findAll(pageable)
            : repo.findByCategory(category, pageable);
        return page.map(ProductDto::from);
    }
}

-- Query 1: lấy data trang hiện tại
SELECT p.* FROM products p
WHERE p.category = 'ELECTRONICS'
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- Query 2: đếm tổng để tính totalPages
SELECT COUNT(*) FROM products p
WHERE p.category = 'ELECTRONICS';

// Page interface (rút gọn)
public interface Page<T> extends Slice<T> {
    long getTotalElements(); // tổng row khớp filter
    int getTotalPages();     // = ceil(totalElements / size)
    boolean isFirst();
    boolean isLast();
    boolean hasNext();
    List<T> getContent();
    int getNumber();         // trang hiện tại (0-based)
    int getSize();           // size đã request
}

{
  "content": [ { "id": 1, "name": "Laptop" }, "..." ],
  "totalElements": 240,
  "totalPages": 12,
  "number": 0,
  "size": 20,
  "first": true,
  "last": false,
  "numberOfElements": 20,
  "empty": false
}

// Sort một trường
Sort s1 = Sort.by("createdAt").descending();

// Sort nhiều trường: priority giảm dần, name tăng dần
Sort s2 = Sort.by(
    Sort.Order.desc("priority"),
    Sort.Order.asc("name")
);

// Tạo Pageable với sort tường minh
Pageable pageable = PageRequest.of(0, 20, s2);

// URL tương đương:
// ?sort=priority,desc&sort=name,asc
// Spring bind nhiều tham số sort=... thành Sort multi-field

SELECT p.* FROM products p
ORDER BY p.priority DESC, p.name ASC
LIMIT 20 OFFSET 0;

Sort stableSort = Sort.by(
    Sort.Order.desc("priority"),
    Sort.Order.asc("name"),
    Sort.Order.asc("id")  // tie-breaker: id unique dam bao thu tu nhat quan
);

// Tập trường được phép sort — define trong service/controller
private static final Set<String> ALLOWED_SORT_FIELDS =
    Set.of("createdAt", "name", "price", "priority", "updatedAt");

@GetMapping
public Page<ProductDto> list(Pageable pageable) {
    // Validate truoc khi goi DB
    pageable.getSort().forEach(order -> {
        if (!ALLOWED_SORT_FIELDS.contains(order.getProperty())) {
            throw new ResponseStatusException(
                HttpStatus.BAD_REQUEST,
                "Sort field not allowed: " + order.getProperty()
            );
        }
    });
    return repo.findAll(pageable).map(ProductDto::from);
}

public enum ProductSortField {
    CREATED_AT("createdAt"),
    NAME("name"),
    PRICE("price");

    private final String field;
    ProductSortField(String f) { this.field = f; }
    public String getField() { return field; }
}

spring:
  data:
    web:
      pageable:
        default-page-size: 20   # size mặc định khi client không gửi
        max-page-size: 100      # hard cap — Spring tự trim, không throw
        one-indexed-parameters: false  # 0-based (mặc định)

@GetMapping
public Page<ProductDto> list(
    @RequestParam(defaultValue = "0") @Min(0) int page,
    @RequestParam(defaultValue = "20") @Min(1) @Max(100) int size,
    @RequestParam(defaultValue = "createdAt,desc") String sort
) {
    // @Max(100) throw ConstraintViolationException -> 400 Bad Request
    // Client biet tuong minh la size vuot gioi han
    Sort parsedSort = parseSortParam(sort);
    Pageable pageable = PageRequest.of(page, size, parsedSort);
    return repo.findAll(pageable).map(ProductDto::from);
}

// Load toat Project entity -> 15+ column, bao gom large CLOB
Page<Project> page = repo.findByStatus(status, pageable);
return page.map(ProjectDto::from); // map in-memory sau khi load

// DTO chi chon 4 field can thiet
public record ProjectSummary(Long id, String name, ProjectStatus status, long taskCount) {}

@Query(value = """
    SELECT new com.example.dto.ProjectSummary(
        p.id, p.name, p.status, COUNT(t.id)
    )
    FROM Project p LEFT JOIN p.tasks t
    WHERE p.status = :status
    GROUP BY p.id, p.name, p.status
    """,
    countQuery = """
    SELECT COUNT(p) FROM Project p WHERE p.status = :status
    """)
Page<ProjectSummary> findSummariesByStatus(
    @Param("status") ProjectStatus status,
    Pageable pageable
);

-- Count query mặc định (Spring Data tự sinh) - BAD:
SELECT COUNT(p.id, p.name, p.status, COUNT(t.id))
FROM Project p LEFT JOIN tasks t ON t.project_id = p.id
WHERE p.status = ?
GROUP BY p.id, p.name, p.status;
-- JOIN tasks cho count -- vo ich, cham

-- Count query custom - GOOD:
SELECT COUNT(p) FROM projects p WHERE p.status = ?;
-- Khong JOIN, khong GROUP BY -- nhanh

@Service
@Transactional(readOnly = true)
public class ProjectService {

    private final ProjectRepository repo;

    public Page<ProjectSummary> search(ProjectFilter filter, Pageable pageable) {
        Specification<Project> spec = ProjectSpecs.matching(filter);
        // findAll(Specification, Pageable) tu dong sinh WHERE + ORDER BY + LIMIT/OFFSET
        return repo.findAll(spec, pageable)
                   .map(ProjectSummary::from);
    }
}

@GetMapping("/projects")
public Page<ProjectSummary> list(
    @RequestParam(required = false) ProjectStatus status,
    @RequestParam(required = false) String keyword,
    @PageableDefault(size = 20)
    @SortDefault(sort = "createdAt", direction = Sort.Direction.DESC)
    Pageable pageable
) {
    return service.search(new ProjectFilter(status, keyword), pageable);
}

// SAI — khong co sort -> DB tra theo "any order"
Page<Product> page = repo.findAll(PageRequest.of(0, 20));
// page 0 lan 1 va lan 2 co the khac nhau neu DB optimizer chon khac

// DUNG — luon sort theo field co index
Page<Product> page = repo.findAll(
    PageRequest.of(0, 20, Sort.by("createdAt").descending())
);

// SAI — Spring tự sinh count wrap toàn query
@Query("""
    SELECT p FROM Project p LEFT JOIN p.tasks t
    WHERE p.status = :status GROUP BY p.id
    """)
Page<Project> findByStatus(@Param("status") ProjectStatus status, Pageable pageable);
// -> count query se JOIN tasks -- cham

// DUNG — custom countQuery
@Query(value = "...", countQuery = "SELECT COUNT(p) FROM Project p WHERE p.status = :status")
Page<ProjectSummary> findByStatus(...);

// SAI — load toan bo entity, co the hang trieu row
List<Product> all = repo.findAll();

// DUNG — luon dung Pageable cho list endpoint
Page<Product> page = repo.findAll(PageRequest.of(0, 20));