使用Spring Data JPA高效查询实体子集：投影与最佳实践

import javax.persistence.*;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

// Subject实体
@Entity
@Table(name="Subject")
public class Subject {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id; // 建议使用包装类型

    @Column(name = "subject_date") // 避免使用数据库保留字"date"
    public Date date;

    @ManyToOne
    @JoinColumn(name="course_category", nullable=false)
    private Category category;

    // Getters and Setters
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }
}

// Category实体
@Entity
@Table(name="Category")
public class Category {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id; // 建议使用包装类型

    @Column(name = "name") // 添加一个name字段方便测试
    private String name;

    @OneToMany(cascade=CascadeType.ALL, mappedBy="category")
    private Set<Subject> subjects = new HashSet<>(); // 字段名改为subjects更具描述性

    // Getters and Setters
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Subject> getSubjects() {
        return subjects;
    }

    public void setSubjects(Set<Subject> subjects) {
        this.subjects = subjects;
    }
}

import java.util.Date;

public interface DatesOnly {
    Date getDate();
}

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

public interface SubjectRepository extends JpaRepository<Subject, Integer> {

    /**
     * 方式一：使用声明式方法命名查询（推荐）
     * Spring Data JPA 会根据方法名自动生成查询。
     * 查找所有Category ID匹配的Subject，并投影为DatesOnly接口。
     */
    List<DatesOnly> findAllByCategoryId(Integer categoryId);

    /**
     * 方式二：使用JPQL查询并投影
     * 注意：JPQL查询必须返回完整的实体对象（如Subject s），
     * 而不是单个字段（如s.date），这样Spring Data JPA才能正确地创建代理对象，
     * 并通过DatesOnly接口的getDate()方法获取date字段。
     *
     * @param id Category的ID
     * @return 匹配Category ID的Subject的date字段列表，通过DatesOnly接口投影
     */
    @Query("Select s from Subject s Where s.category.id = :id")
    List<DatesOnly> findDatesProjectedByCategoryId(Integer id);
}

import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Date;

@RestController
@RequestMapping("/subjects")
public class SubjectController {
    private final SubjectRepository subjectRepository;
    private final CategoryRepository categoryRepository; // 假设有一个CategoryRepository

    public SubjectController(SubjectRepository subjectRepository, CategoryRepository categoryRepository) {
        this.subjectRepository = subjectRepository;
        this.categoryRepository = categoryRepository;
    }

    /**
     * 创建Subject实体，用于测试数据填充
     * POST /subjects
     * Request Body:
     * {
     *     "category": {
     *         "id": 1
     *     },
     *     "date": "2023-01-01T10:00:00.000Z"
     * }
     */
    @PostMapping
    public Subject createSubject(@RequestBody Subject subject) {
        // 确保category存在
        if (subject.getCategory() != null && subject.getCategory().getId() != null) {
            categoryRepository.findById(subject.getCategory().getId())
                    .ifPresent(subject::setCategory);
        }
        return subjectRepository.save(subject);
    }

    /**
     * 获取指定Category ID下所有Subject的日期列表（使用声明式方法）
     * GET /subjects/dates/category/{id}
     */
    @GetMapping("/dates/category/{id}")
    public List<DatesOnly> getDatesByCategoryId(@PathVariable Integer id) {
        return subjectRepository.findAllByCategoryId(id);
    }

    /**
     * 获取指定Category ID下所有Subject的日期列表（使用JPQL投影）
     * GET /subjects/dates-jpql/category/{id}
     */
    @GetMapping("/dates-jpql/category/{id}")
    public List<DatesOnly> getDatesByCategoryIdUsingJpql(@PathVariable Integer id) {
        return subjectRepository.findDatesProjectedByCategoryId(id);
    }
}