Spring Data JPA双向一对多关联查询如何仅获取指定子记录？

问题场景 #

在使用Spring Data JPA的双向一对多关联时，执行Customer findByIdAndDepartmentsDeptName(int customerId, String deptName);查询时，会返回该Customer的所有Department子记录，而非仅匹配指定名称的Department。

相关实体定义 #

Customer实体：

@Entity
@Table(name = "customer")
public class Customer {

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

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

    @OneToMany(mappedBy = "customer", orphanRemoval = true, cascade = CascadeType.ALL)
    @Fetch(FetchMode.SELECT)
    private List<Department> departments;
}

Department实体：

@Entity
@Table(name = "department")
public class Department {

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

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

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "customer_id",referencedColumnName = "id")
    private Customer customer;
}

当前问题表现 #

执行派生查询后，返回的Customer对象包含所有关联的Department，对应的SQL会先过滤出符合条件的Customer，再执行第二条SQL加载该Customer的所有子记录：

select c1_0.id,c1_0.name from customer c1_0 left join department d1_0 on c1_0.id=d1_0.customer_id where c1_0.id=? and d1_0.dept_name=?
select d1_0.customer_id,d1_0.id,d1_0.dept_name from department d1_0 where d1_0.customer_id=?

理想解决方案 #

方案一：JOIN FETCH 关联查询（推荐） #

直接通过JPQL的JOIN FETCH实现关联查询，一次性获取符合条件的Customer和匹配的Department，避免N+1查询，同时精准过滤子记录：

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {
    @Query("SELECT c FROM Customer c JOIN FETCH c.departments d WHERE c.id = :customerId AND d.deptName = :deptName")
    Customer findCustomerWithMatchingDept(@Param("customerId") int customerId, @Param("deptName") String deptName);
}

注意：如果存在多个匹配的Department，该查询会返回多个相同Customer对象（每个匹配的Department对应一个），可以通过DISTINCT去重：SELECT DISTINCT c FROM ...

方案二：DTO投影封装 #

通过自定义DTO来封装需要的结果，不修改原实体的加载逻辑：

1. 定义CustomerDTO：

public class CustomerDTO {
    private int id;
    private String name;
    private List<Department> matchingDepartments;

    // 用于JPQL投影的构造函数
    public CustomerDTO(int id, String name, List<Department> matchingDepartments) {
        this.id = id;
        this.name = name;
        this.matchingDepartments = matchingDepartments;
    }

    // Getter和Setter方法
}

2. 在Repository中定义查询：

@Query("SELECT new com.yourpackage.dto.CustomerDTO(c.id, c.name, COLLECT(d)) FROM Customer c JOIN c.departments d WHERE c.id = :customerId AND d.deptName = :deptName GROUP BY c.id, c.name")
CustomerDTO findCustomerWithMatchingDeptDto(@Param("customerId") int customerId, @Param("deptName") String deptName);

方案三：实体图（Entity Graph）控制加载 #

通过实体图指定需要加载的关联属性，结合JPQL过滤条件：

1. 在Customer实体上定义实体图：

@Entity
@Table(name = "customer")
@NamedEntityGraph(
    name = "Customer.withMatchingDept",
    attributeNodes = @NamedAttributeNode("departments")
)
public class Customer {
    // 原有代码不变
}

2. 在Repository方法上关联实体图：

@EntityGraph(value = "Customer.withMatchingDept", type = EntityGraph.EntityGraphType.FETCH)
@Query("SELECT c FROM Customer c JOIN c.departments d WHERE c.id = :customerId AND d.deptName = :deptName")
Customer findCustomerWithMatchingDept(@Param("customerId") int customerId, @Param("deptName") String deptName);

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