Spring Data JPA 实体投影与关联查询：高效获取特定字段数据

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

@Entity
@Table(name = "Subject")
public class Subject {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id; // 推荐使用包装类型

    @Column(name = "date_field") // 避免使用SQL保留字'date'
    public Date dateField; // 字段名修改以避免与SQL保留字冲突

    @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 getDateField() { return dateField; }
    public void setDateField(Date dateField) { this.dateField = dateField; }
    public Category getCategory() { return category; }
    public void setCategory(Category category) { this.category = category; }
}

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

@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<>(); // 字段名修改以避免与实体名冲突

    // 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 getDateField(); // 方法名需与实体字段的getter方法对应
}

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

public interface SubjectRepository extends JpaRepository<Subject, Integer> {
    /**
     * 根据Category ID查询Subject的日期字段列表，并投影为DatesOnly接口
     * Spring Data JPA会自动解析方法名：
     * - "findAllBy" 表示查询所有
     * - "CategoryId" 表示根据Category实体中的id字段进行过滤
     * @param categoryId Category的ID
     * @return 包含日期字段的DatesOnly对象列表
     */
    List<DatesOnly> findAllByCategoryId(Integer categoryId);
}

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> {
    /**
     * 使用JPQL根据Category ID查询Subject实体，并投影为DatesOnly接口
     * 注意：JPQL中选择的是整个Subject实体（s），而非s.dateField。
     * Spring Data JPA在执行投影时会从返回的Subject实体中提取所需字段。
     * @param categoryId Category的ID
     * @return 包含日期字段的DatesOnly对象列表
     */
    @Query("SELECT s FROM Subject s WHERE s.category.id = :categoryId")
    List<DatesOnly> findDatesProjectedByCategoryId(@org.springframework.data.repository.query.Param("categoryId") Integer categoryId);
}

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

@RestController
@RequestMapping("/subjects")
public class SubjectController {
    private final SubjectRepository subjectRepository;

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

    @PostMapping
    public Subject createSubject(@RequestBody Subject subject) {
        return subjectRepository.save(subject);
    }

    @GetMapping("/by-category/{categoryId}")
    public List<DatesOnly> getDatesByCategoryId(@PathVariable Integer categoryId) {
        // 使用派生查询方法
        return subjectRepository.findAllByCategoryId(categoryId);
        // 或者使用JPQL查询方法
        // return subjectRepository.findDatesProjectedByCategoryId(categoryId);
    }
}