基于Spring JPA Hibernate构建带排序、分页的PostgreSQL动态查询

Got it, let's break down how to implement dynamic sorting with PostgreSQL-style LIMIT/OFFSET pagination in Spring JPA Hibernate, using your WorkflowDetailsInterface that extends a repository. Here's a step-by-step solution:

First, switch from CrudRepository to JpaRepository—it comes with built-in pagination and sorting utilities that will make this easier. We'll add two options: one using Spring Data's native Pageable (recommended for clean code) and another using raw PostgreSQL syntax if you want direct control over offset and limit.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface WorkflowDetailsInterface extends JpaRepository<WorkflowDetails, Long> {

    // Option 1: Use Spring Data's Pageable (handles LIMIT/OFFSET under the hood)
    Page<WorkflowDetails> findAll(Pageable pageable);

    // Option 2: Directly use PostgreSQL LIMIT/OFFSET with dynamic sort (native query)
    @Query(value = "SELECT * FROM workflow_details ORDER BY :sortField :sortType LIMIT :limit OFFSET :offset", 
           nativeQuery = true)
    List<WorkflowDetails> findAllWithDynamicSortAndOffsetLimit(
        String sortField, 
        String sortType, 
        int offset, 
        int limit
    );
}

Let's build out the service logic to handle your four parameters: sort field, sort direction, offset, and limit. We'll focus on the recommended Pageable approach first, then cover the native query option.

Option 1: Using Spring Data Pageable #

This approach is cleaner because Spring handles translating Pageable into PostgreSQL's LIMIT and OFFSET automatically. We just need to dynamically create a Sort object from your parameters.

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class WorkflowDetailsService {

    private final WorkflowDetailsInterface workflowRepo;

    // Constructor injection (preferred over @Autowired)
    public WorkflowDetailsService(WorkflowDetailsInterface workflowRepo) {
        this.workflowRepo = workflowRepo;
    }

    public List<WorkflowDetails> getPaginatedWorkflows(String sortField, String sortDirection, int offset, int limit) {
        // First, validate inputs to prevent issues like SQL injection
        validateSortParameters(sortField, sortDirection);
        validatePaginationParameters(offset, limit);

        // Convert sort direction to Spring's Sort.Direction enum
        Sort.Direction dir = Sort.Direction.fromString(sortDirection.toUpperCase());
        // Build dynamic Sort object
        Sort sort = Sort.by(dir, sortField);

        // Calculate page number from offset and limit (since Pageable uses page index, not raw offset)
        int pageNumber = offset / limit;
        // Create Pageable with our sort, page, and limit
        Pageable pageable = PageRequest.of(pageNumber, limit, sort);

        // Fetch the page and extract the content list
        Page<WorkflowDetails> workflowPage = workflowRepo.findAll(pageable);
        return workflowPage.getContent();
    }

    // Validate sort field is a valid property of WorkflowDetails (prevents SQL injection)
    private void validateSortParameters(String sortField, String sortDirection) {
        List<String> allowedSortFields = List.of("id", "workflowName", "createdTimestamp"); // Replace with your entity's fields
        if (!allowedSortFields.contains(sortField)) {
            throw new IllegalArgumentException("Invalid sort field: " + sortField);
        }

        if (!List.of("ASC", "DESC").contains(sortDirection.toUpperCase())) {
            throw new IllegalArgumentException("Sort direction must be ASC or DESC");
        }
    }

    // Ensure pagination values are positive
    private void validatePaginationParameters(int offset, int limit) {
        if (offset < 0) {
            throw new IllegalArgumentException("Offset cannot be negative");
        }
        if (limit <= 0) {
            throw new IllegalArgumentException("Limit must be a positive number");
        }
    }
}

Option 2: Using Native PostgreSQL Query #

If you prefer working directly with offset and limit without calculating page numbers, use the native query method. Just remember to still validate inputs to avoid SQL injection risks.

// Add this method to your WorkflowDetailsService
public List<WorkflowDetails> getWorkflowsWithNativePagination(String sortField, String sortDirection, int offset, int limit) {
    validateSortParameters(sortField, sortDirection);
    validatePaginationParameters(offset, limit);

    // Make sure sortField matches the actual database column name (not just entity field)
    List<String> allowedDbColumns = List.of("id", "workflow_name", "created_timestamp"); // Replace with your DB columns
    if (!allowedDbColumns.contains(sortField)) {
        throw new IllegalArgumentException("Invalid database column for sorting: " + sortField);
    }

    return workflowRepo.findAllWithDynamicSortAndOffsetLimit(sortField, sortDirection.toUpperCase(), offset, limit);
}

• Spring Data's Pageable automatically generates PostgreSQL-compatible LIMIT and OFFSET clauses, so no extra configuration is needed.
• For native queries, ensure your sortField matches the exact column names in your PostgreSQL table (not the entity's field names, unless you're using default naming strategies).
• Always validate inputs—this prevents malicious SQL injection attempts and ensures your queries behave as expected.

内容的提问来源于stack exchange，提问作者Pranav MS