Spring Data JPA CRUD 与动态查询示例

目标

演示 Spring Data JPA 的基本 CRUD、命名查询、@Query 自定义查询、Specification 动态查询。

完整代码

1. 实体定义

package com.example.entity;

import jakarta.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(precision = 10, scale = 2)
    private Double price;

    private Integer stock;

    @Enumerated(EnumType.STRING)
    private Status status;

    private LocalDateTime createdAt;

    public enum Status { ACTIVE, INACTIVE, DISCONTINUED }

    // 无参构造（JPA 必需）
    public Product() {}

    public Product(String name, Double price, Integer stock) {
        this.name = name;
        this.price = price;
        this.stock = stock;
        this.status = Status.ACTIVE;
        this.createdAt = LocalDateTime.now();
    }

    // getters & 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 Double getPrice() { return price; }
    public void setPrice(Double price) { this.price = price; }
    public Integer getStock() { return stock; }
    public void setStock(Integer stock) { this.stock = stock; }
    public Status getStatus() { return status; }
    public void setStatus(Status status) { this.status = status; }
    public LocalDateTime getCreatedAt() { return createdAt; }
}

2. Repository 接口

package com.example.repository;

import com.example.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ProductRepository extends
        JpaRepository<Product, Long>,
        JpaSpecificationExecutor<Product> {

    // 方法命名查询
    List<Product> findByNameContaining(String keyword);

    List<Product> findByPriceBetween(Double min, Double max);

    Optional<Product> findByName(String name);

    List<Product> findByStatusOrderByCreatedAtDesc(Product.Status status);

    boolean existsByName(String name);

    long countByStatus(Product.Status status);

    // JPQL 自定义查询
    @Query("SELECT p FROM Product p WHERE p.price >= :minPrice AND p.stock > 0")
    List<Product> findAvailableProducts(@Param("minPrice") Double minPrice);

    // 原生 SQL
    @Query(value = "SELECT * FROM products WHERE stock < :threshold",
           nativeQuery = true)
    List<Product> findLowStock(@Param("threshold") Integer threshold);

    // 更新操作
    @Modifying
    @Query("UPDATE Product p SET p.stock = p.stock - :quantity " +
           "WHERE p.id = :id AND p.stock >= :quantity")
    int deductStock(@Param("id") Long id, @Param("quantity") Integer quantity);
}

3. Specification 动态查询

package com.example.specification;

import com.example.entity.Product;
import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;

public class ProductSpecification {

    public static Specification<Product> filterBy(
            String nameLike,
            Double minPrice,
            Double maxPrice,
            Product.Status status) {

        return (Root<Product> root, CriteriaQuery<?> query,
                CriteriaBuilder cb) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (nameLike != null && !nameLike.isEmpty()) {
                predicates.add(
                    cb.like(root.get("name"), "%" + nameLike + "%"));
            }
            if (minPrice != null) {
                predicates.add(
                    cb.greaterThanOrEqualTo(root.get("price"), minPrice));
            }
            if (maxPrice != null) {
                predicates.add(
                    cb.lessThanOrEqualTo(root.get("price"), maxPrice));
            }
            if (status != null) {
                predicates.add(cb.equal(root.get("status"), status));
            }

            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }
}

4. 使用示例

package com.example;

import com.example.entity.Product;
import com.example.entity.Product.Status;
import com.example.repository.ProductRepository;
import com.example.specification.ProductSpecification;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    // 基本 CRUD
    public Product create(String name, Double price, Integer stock) {
        return productRepository.save(new Product(name, price, stock));
    }

    public Product findById(Long id) {
        return productRepository.findById(id)
            .orElseThrow(() -> new RuntimeException("Product not found"));
    }

    // 分页查询
    public Page<Product> searchWithPagination(String name, int page, int size) {
        return productRepository.findByNameContaining(name,
            PageRequest.of(page, size, Sort.by("price").ascending()));
    }

    // 动态查询
    public List<Product> dynamicSearch(
            String name, Double minPrice, Double maxPrice, Status status) {
        return productRepository.findAll(
            ProductSpecification.filterBy(name, minPrice, maxPrice, status));
    }

    // 扣减库存（事务保证）
    public boolean purchase(Long id, int quantity) {
        int rows = productRepository.deductStock(id, quantity);
        return rows > 0;
    }
}

运行测试

@SpringBootTest
class ProductServiceTest {
    @Autowired
    private ProductService productService;

    @Test
    void testCrud() {
        Product p = productService.create("MacBook Pro", 12999.00, 50);
        assertNotNull(p.getId());
        Product found = productService.findById(p.getId());
        assertEquals("MacBook Pro", found.getName());
    }
}