Hibernate/JPQL:如何基于对子项的查询将所有子项加载到父项中

问题描述

所以我们提供的服务简化了这两个实体

So we have a service with simplified these two entities

@Entity
public class Ticket {
/* simplified*/

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "ticket", cascade = CascadeType.ALL, orphanRemoval = true)
  private Set<Grant> grants = new HashSet<>();
}

@Entity
public class Grant {
/* simplified*/

  @NotNull
  @ManyToOne(fetch = LAZY)
  @JoinColumn(name = UsageGrant.FK_TICKET, nullable = false)
  private Ticket ticket;

  @NotNull
  @Column(name = "specialNumber", nullable = false)
  private Integer specialNumber;
}

我想查询一个查询，该查询选择所有包含带有特定"specialNumber"赠款的票证.我要抓住的是，我想让票证与所有赠款一起退还，而不仅仅是一个匹配项.我尝试过

I'd like to have a query that selects all tickets that contain a grant with a specific "specialNumber". The catch is that I want to have the ticket returned with all grants, not only the one matching. I tried it with

@Repository
public interface TicketRepository extends JpaRepository<Ticket, String> {

@Query("SELECT DISTINCT ti FROM Ticket ti JOIN FETCH ti.grants g WHERE
g.specialNumber = :specialNumber "
  )
  List<Ticket> findBySpecialNumberAndLoadAllGrantsOnTicket(
      @NotNull @Param("specialNumber") Integer specialNumber);
}

但是这给了我匹配的那个.我是否需要将其分为两个查询? Criteria API也无济于事，因为那里也不支持RIGHT JOIN.

but this gives me just the matching one. Do I need to split it up into two queries? Criteria API also doesn't help, because RIGHT JOIN is also not supported there.

更新

我可以做到

SELECT g FROM Grant g LEFT JOIN FETCH g.ticket ti JOIN FETCH ti.grants WHERE g.specialNumber = :specialNumber

，然后使用g.getTicket()访问票证.结果查询看起来很疯狂，我不确定这是否是一个聪明的方法.

and accessing the ticket with g.getTicket(). The resulting query looks crazy and I'm not sure if this is a clever approach at all.

推荐答案

您可以使用@EntityGraph来获取grants，并使用JPA方法查询以供specialNumber

You can use @EntityGraph for fetch grants and query using JPA method for select by specialNumber

@EntityGraph(attributePaths = {"grants"})
public List<Ticket> findByGrantsSpecialNumber(Integer specialNumber);

或

您可以使用@NamedEntityGraph

@NamedEntityGraph(name = "Ticket.Grants", attributeNodes = { @NamedAttributeNode("grants") })
public class Ticket {

和

@EntityGraph(value = "Ticket.Grants", type = EntityGraphType.LOAD)
public List<Ticket> findByGrantsSpecialNumber(Integer specialNumber);

这篇关于Hibernate/JPQL:如何基于对子项的查询将所有子项加载到父项中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！