如何在JPA Repository中实现带where子句注入的记录查询，并支持分页、自定义过滤与排序？

Nice question! Implementing dynamic filtering, sorting, and pagination with JPA Repository is totally achievable—let’s walk through exactly how to build this for your use case:

First, start with your entity class (I’ll use a User example based on your filter criteria):

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String lastname;
    private boolean enabled;
    
    // Getters, setters, and default constructor
}

Next, create a repository that extends JpaRepository, plus JpaSpecificationExecutor (for dynamic filtering) and PagingAndSortingRepository (for pagination/sorting):

public interface UserRepository extends JpaRepository<User, Long>,
                                      JpaSpecificationExecutor<User>,
                                      PagingAndSortingRepository<User, Long> {
}

Map the incoming JSON request to a Java DTO to easily access parameters:

public class UserSearchRequest {
    private int page_index;
    private int page_size;
    private String search_string;
    private String sort_criteria;
    
    // Getters and setters
}

We’ll convert the user’s search_string into a JPA Specification (this lets us build dynamic WHERE clauses safely).

⚠️ Important: Manual string parsing can be risky for production—always sanitize inputs! This example is simplified; for complex queries, consider using Querydsl or a proper expression parser.

private Specification<User> buildFilterSpecification(String searchString) {
    return (root, query, criteriaBuilder) -> {
        if (searchString == null || searchString.trim().isEmpty()) {
            return criteriaBuilder.conjunction(); // No filter applied
        }

        // Split conditions by "and" and build predicates
        String[] conditions = searchString.split("and");
        List<Predicate> predicates = new ArrayList<>();

        for (String condition : conditions) {
            condition = condition.trim();
            String[] keyValue = condition.split("=");
            if (keyValue.length != 2) continue;

            String field = keyValue[0].trim();
            String value = keyValue[1].trim().replace("'", ""); // Remove quotes from string values

            switch (field) {
                case "name":
                    predicates.add(criteriaBuilder.equal(root.get(field), value));
                    break;
                case "enabled":
                    predicates.add(criteriaBuilder.equal(root.get(field), Boolean.parseBoolean(value)));
                    break;
                // Add other fields you need to support here
            }
        }
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

Turn the sort_criteria string into a Sort object that JPA understands:

private Sort buildSortCriteria(String sortCriteria) {
    if (sortCriteria == null || sortCriteria.trim().isEmpty()) {
        return Sort.unsorted();
    }

    String[] sortPairs = sortCriteria.split(",");
    List<Sort.Order> orders = new ArrayList<>();

    for (String pair : sortPairs) {
        pair = pair.trim();
        String[] fieldDirection = pair.split(" ");
        String field = fieldDirection[0].trim();
        Sort.Direction direction = fieldDirection.length > 1 
            ? Sort.Direction.valueOf(fieldDirection[1].trim().toUpperCase()) 
            : Sort.Direction.ASC;

        orders.add(new Sort.Order(direction, field));
    }
    return Sort.by(orders);
}

Create a service method that combines pagination, filtering, and sorting:

@Service
public class UserService {
    private final UserRepository userRepository;

    // Constructor injection (preferred over @Autowired)
    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public Page<User> searchUsers(UserSearchRequest request) {
        // Validate input (add more checks as needed)
        if (request.getPage_index() < 1 || request.getPage_size() < 1) {
            throw new IllegalArgumentException("Page index and size must be positive numbers");
        }

        // Build filter and sort
        Specification<User> filterSpec = buildFilterSpecification(request.getSearch_string());
        Sort sort = buildSortCriteria(request.getSort_criteria());

        // JPA uses 0-based page indices—convert from user's 1-based index
        Pageable pageable = PageRequest.of(request.getPage_index() - 1, request.getPage_size(), sort);

        // Execute the query
        return userRepository.findAll(filterSpec, pageable);
    }

    // Include buildFilterSpecification and buildSortCriteria methods here
}

Expose a REST endpoint to receive the search request and return results:

@RestController
@RequestMapping("/api/users")
public class UserController {
    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @PostMapping("/search")
    public ResponseEntity<Page<User>> searchUsers(@RequestBody UserSearchRequest request) {
        Page<User> results = userService.searchUsers(request);
        return ResponseEntity.ok(results);
    }
}

• Security: Never trust raw user input! For production, replace manual string parsing with Querydsl (which generates type-safe queries) or a library like Spring Data Specifications with proper input validation to avoid SQL injection.
• Error Handling: Add global exception handlers to catch invalid input formats or database errors.
• Performance: Index the fields used in filtering and sorting (e.g., name, enabled) in your database to speed up queries.

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