如何编写JPA Repository方法查询关联特定账单用户的银行

问题描述 #

现需查询所有为特定Billing对应的Person提供贷款的Bank。由于实体间关联关系为Bank -> PersonCredit -> Person，并非直接关联，请问如何在BankRepository中编写对应的JPA查询方法？

实体类代码 #

Person 实体类 #

@Entity
@Getter
@Setter
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Basic(optional = false)
    private String name;

    @Basic(optional = false)
    private int credit;

}

Bank 实体类 #

@Entity
@Getter
@Setter
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Bank implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Basic(optional = false)
    private String name;

    @Basic(optional = false)
    private int duration;

    @Singular
    @OneToMany(cascade = CascadeType.PERSIST)
    private Set<PersonCredit> personCredits = new HashSet<>();

}

PersonCredit 实体类 #

@Entity
@Getter
@Setter
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PersonCredit implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne(optional = false)
    private Person person;

    @Basic(optional = false)
    private int annualRate;

}

Billing 实体类 #

@Entity
@Getter
@Setter
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Billing implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne(optional = false)
    private Person person;

    @Basic(optional = false)
    private LocalDate lastPaymentDate;

    @Basic(optional = false)
    private long amount;
}

解决方案 #

可以通过两种方式实现该查询：

方式一：Spring Data JPA 方法命名查询 #

利用Spring Data JPA的方法名推导规则，直接在BankRepository中定义方法，无需手动编写查询语句：

public interface BankRepository extends JpaRepository<Bank, Long> {
    // 通过传入特定Billing对应的Person对象，查询关联该Person的所有Bank
    List<Bank> findByPersonCreditsPerson(Person person);
    
    // 直接通过Billing的ID串联关联关系查询
    List<Bank> findByPersonCreditsPersonBillingId(Long billingId);
}

方式二：使用@Query注解编写JPQL #

如果需要更灵活的查询逻辑，可通过@Query注解自定义JPQL语句：

public interface BankRepository extends JpaRepository<Bank, Long> {
    @Query("SELECT b FROM Bank b JOIN b.personCredits pc WHERE pc.person = (SELECT p FROM Billing bl WHERE bl.id = :billingId)")
    List<Bank> findBanksByBillingId(@Param("billingId") Long billingId);
}

该写法无需额外关联配置，直接通过Billing的ID定位到对应的Person，再关联查询出对应的Bank。

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