JPA如何根据演员ID查询关联电视剧及分集列表

• 需求：根据演员ID（Actor Id）查询对应的电视剧（tv_shows）列表
• 现状：已实现根据演员ID查询对应分集（chapters）列表的功能，用相同逻辑直接查询TvShow实体时触发报错
• 约束：不希望额外新建数据表，不确定是否必须建表才能实现Actor到TvShow的关联访问
• 库表关系：层级为 tv_shows > seasons > chapters，独立actors表，通过chapters_has_actors多对多中间表关联章节和演员，数据库EER图：查看数据库EER图

现有实体定义 #

TvShow.java #

@Entity
@Table(name = "TV_SHOWS")
public class TvShow implements Serializable {

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

    @Column(name = "NAME")
    private String name;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "tvShow")
    private List<Season> seasons;

// Getters and Setters
}

Season.java #

@Entity
@Table(name = "SEASONS")
public class Season implements Serializable {

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

    @Column(name = "NAME")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "TV_SHOW_ID", nullable = false)
    private TvShow tvShow;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "season")
    private List<Chapter> chapters;

// Getters and Setters
}

Chapter.java #

@Entity
@Table(name = "CHAPTERS")
public class Chapter implements Serializable {

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

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SEASON_ID", nullable = false)
    private Season season;
    
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable (name = "CHAPTERS_HAS_ACTORS",
            joinColumns = @JoinColumn(name = "CHAPTER_ID"),
            inverseJoinColumns = @JoinColumn(name ="ACTOR_ID"))
    private List<Actor> actor;

// Getters and Setters
}

已实现的分集查询代码 #

ChapterServiceImpl.java #

@Service
public class ChapterServiceImpl implements ChapterService {

    @Autowired
    private ChapterRepository chapterRepository;
    @Autowired
    private ActorRepository actorRepository;

    private ModelMapper modelMapper = new ModelMapper();

    @Override
    public List<ChapterRest> getChaptersByActorId(Long actorId){
        Optional<Actor> actor = actorRepository.findById(actorId);
        List<ChapterRest> chapterList =  chapterRepository.findByActorId(actorId)
                .stream()
                .map(chapter -> modelMapper
                .map(chapter, ChapterRest.class))
                .collect(Collectors.toList());
        return chapterList;
    }

ChapterControllerImpl.java #

@RestController
@RequestMapping("/tv-shows/{tvShowId}/seasons/{seasonNumber}/chapters")
public class ChapterControllerImpl implements ChapterController {

    @Autowired
    private ChapterService chapterService;

    @Override
    @GetMapping(value = "/actors", produces = MediaType.APPLICATION_JSON_VALUE)
    public List<ChapterRest> getChaptersByActorId(
            @RequestParam Long actorId) {
        return chapterService.getChaptersByActorId(actorId);
    }
}

ChapterRepository.java #

@Repository
public interface ChapterRepository extends JpaRepository<Chapter, Long> {
    List<Chapter> findByActorId(Long actorId);
}

不需要新建任何数据表，现有表关联关系已完全支持跨层级查询，通过JPA嵌套属性路径派生查询即可实现，具体操作如下：

• 新建TvShow对应的Repository接口，利用JPA支持的嵌套关联属性路径派生查询方法，框架会自动生成跨表关联的SQL语句：

@Repository
public interface TvShowRepository extends JpaRepository<TvShow, Long> {
    // 关联路径：TvShow.seasons -> Season.chapters -> Chapter.actor -> Actor.id
    // 加Distinct关键字避免同一部剧因演员参演多集返回重复记录
    List<TvShow> findDistinctBySeasonsChaptersActorId(Long actorId);
}

• 优化查询性能，在Repository方法上添加@EntityGraph注解指定需要一次性加载的关联属性，避免懒加载导致的JSON序列化异常，同时解决多层关联带来的N+1查询问题：

@Repository
public interface TvShowRepository extends JpaRepository<TvShow, Long> {
    @EntityGraph(attributePaths = {"seasons", "seasons.chapters", "seasons.chapters.actor"})
    List<TvShow> findDistinctBySeasonsChaptersActorId(Long actorId);
}

• 补充对应的Service、Controller层逻辑，调用上述Repository方法即可返回带关联季、分集数据的电视剧列表，全程不需要修改现有数据库结构。

注意：如果不需要返回全量的季、分集关联数据，可以根据业务需求调整@EntityGraph中attributePaths的加载范围，减少不必要的数据查询。

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