如何在Spring Data JPA中基于条件查询数据？

Hey there! Great question—Spring Data JPA offers several practical approaches to add conditional queries, just like Hibernate Criteria. Let's walk through the most useful methods using your existing code as a reference:

This is the easiest way for straightforward, fixed conditions. Spring Data JPA automatically generates the underlying SQL/JPQL based on your repository method name.

Update your ProductRepository to include condition-based methods:

public interface ProductRepository extends JpaRepository<Products, Long> {
    // Find products by exact product name
    List<Products> findByProductName(String productName);
    
    // Find products where price is less than a given value
    List<Products> findByPriceLessThan(String maxPrice);
    
    // Combine conditions: product name contains a keyword AND quantity is greater than a threshold
    List<Products> findByProductNameContainingAndQuantityGreaterThan(String keyword, String minQuantity);
}

Then use these methods in your service (plus a helper to avoid repeating DTO conversion):

@Override
public List<ProductDto> getProductsByName(String productName) {
    LOGGER.info("Fetching products by name: {}", productName);
    List<Products> products = productRepository.findByProductName(productName);
    return convertToDtoList(products);
}

// Reusable DTO conversion helper
private List<ProductDto> convertToDtoList(List<Products> products) {
    List<ProductDto> dtoList = new ArrayList<>();
    if (products != null && !products.isEmpty()) {
        for (Products product : products) {
            ProductDto dto = new ProductDto();
            dto.setId(product.getId());
            dto.setProductName(product.getProductName());
            dto.setDescription(product.getDescription());
            dto.setPrice(product.getPrice());
            dto.setQuantity(product.getQuantity());
            dtoList.add(dto);
        }
    }
    return dtoList;
}

Add a corresponding controller endpoint:

@GetMapping("/products/name/{productName}")
public ResponseEntity<List<ProductDto>> getProductsByName(@PathVariable String productName) {
    List<ProductDto> products = productService.getProductsByName(productName);
    return products.isEmpty() ? 
        new ResponseEntity<>(HttpStatus.NOT_FOUND) : 
        new ResponseEntity<>(products, HttpStatus.OK);
}

When conditions get too complex for derived method names, use the @Query annotation to write explicit JPQL or native SQL.

Update your repository:

public interface ProductRepository extends JpaRepository<Products, Long> {
    // JPQL query for products matching description keyword and price range
    @Query("SELECT p FROM Products p WHERE p.description LIKE %:descKeyword% AND p.price <= :maxPrice")
    List<Products> findByDescriptionAndPriceRange(@Param("descKeyword") String descKeyword, @Param("maxPrice") String maxPrice);
    
    // Native SQL query (useful for database-specific features)
    @Query(value = "SELECT * FROM product_details WHERE quantity > :minQty", nativeQuery = true)
    List<Products> findProductsWithMinimumQuantity(@Param("minQty") String minQty);
}

Call these methods in your service just like the derived ones—no extra setup needed.

If you need dynamic, reusable conditions (exactly like Hibernate Criteria), use Specification. First, extend your repository with JpaSpecificationExecutor:

public interface ProductRepository extends JpaRepository<Products, Long>, JpaSpecificationExecutor<Products> {
}

Then build a dynamic Specification to handle flexible filters:

// Add this method to your service (or a utility class)
private Specification<Products> buildProductSpecification(String productName, String maxPrice) {
    return (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();
        
        // Add condition only if productName is provided
        if (productName != null && !productName.isEmpty()) {
            predicates.add(criteriaBuilder.like(root.get("productName"), "%" + productName + "%"));
        }
        
        // Add condition only if maxPrice is provided
        if (maxPrice != null && !maxPrice.isEmpty()) {
            predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("price"), maxPrice));
        }
        
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

Use this in your service to fetch filtered products:

@Override
public List<ProductDto> getFilteredProducts(String productName, String maxPrice) {
    LOGGER.info("Fetching filtered products: name={}, maxPrice={}", productName, maxPrice);
    Specification<Products> spec = buildProductSpecification(productName, maxPrice);
    List<Products> products = productRepository.findAll(spec);
    return convertToDtoList(products);
}

Add a flexible controller endpoint that accepts optional parameters:

@GetMapping("/products/filtered")
public ResponseEntity<List<ProductDto>> getFilteredProducts(
        @RequestParam(required = false) String productName,
        @RequestParam(required = false) String maxPrice) {
    List<ProductDto> products = productService.getFilteredProducts(productName, maxPrice);
    return products.isEmpty() ? 
        new ResponseEntity<>(HttpStatus.NOT_FOUND) : 
        new ResponseEntity<>(products, HttpStatus.OK);
}

This is ideal for scenarios where filters change based on user input (e.g., a search form with optional fields).

For larger projects with complex, frequent queries, Querydsl offers type-safe query building (eliminating typos in entity field names). It requires adding extra dependencies and annotation processing, but it's a powerful option if you need advanced querying capabilities.

Quick Decision Guide #

• Use Derived Query Methods for simple, fixed conditions.
• Use @Query for custom, static queries that are too complex for derived methods.
• Use Specifications for dynamic, reusable conditions (your direct replacement for Hibernate Criteria).

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