所以我想获取整个树结构的XmlObject,对象有父子OneToMany关系,childObjects到自己和OneToMany关系到xmlPeriods,xmlPeriods只在最后一级。
树是这样的。
XmlObject
|_XmlObject
|_XmlObject
|_XmlObject
|_XmlPeriod
以下是我的实体的关系。
@Entity
@Table(name = "xml_object")
public class XmlObject implements Serializable {
@Fetch(value = FetchMode.SELECT)
@BatchSize(size = 50)
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true)
@JoinTable(
name = "xml_object_tree",
joinColumns = {@JoinColumn(name = "parent_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "child_id", referencedColumnName = "id")}
)
@ToString.Exclude
private List<XmlObject> childObjects;
@Fetch(value = FetchMode.SUBSELECT) // FetchMode.JOIN loads eagerly always
@OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY, orphanRemoval = false)
@JoinColumn(name = "xml_object_id", nullable = false)
@ToString.Exclude
private List<XmlPeriod> xmlPeriods;
我从这个查询开始。
@Query(value = "select xo.id from XmlObject xo " +
" where xo.objectType = :type and bw_and(xo.status, :status) = 0")
Optional<List<Long>> findAllObjectsIdsOfTypeAndNotInStatusesAndNotDeletedN(
ObjectTypeEnum type, Integer status, Pageable pageable);
- bw_and* -通过自定义Postgres方言按位应用 and
我不想设置fetch = FetchType.EAGER,我不想在下面的“设置为抑制错误”下隐藏问题
cannot simultaneously fetch multiple bags
我不能使用来自Vlad Mihalcea的两个查询solution,因为对象引用了自己,xmlPeriod位于最后一级。
从那篇文章中我不明白如何请求查尔兹的完整树,我可以使用下面的代码请求第一级,但是如何请求N?
private Set<XmlObject> getXOToProcessVM() {
List<XmlObject> xmlObjects = entityManager.createQuery(
" select distinct p " +
" from XmlObject p " +
" left join fetch p.childObjects " +
" where p.id between :minId and :maxId", XmlObject.class)
.setParameter("minId", 1L)
.setParameter("maxId", 50L)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();
xmlObjects = entityManager.createQuery(
" select distinct p " +
" from XmlObject p " +
" left join fetch p.childObjects " +
" where p in :xmlObjects", XmlObject.class)
.setParameter("xmlObjects", xmlObjects)
.setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
.getResultList();
return Set.copyOf(xmlObjects);
}
除了其他尝试,我还尝试了这个存储库中的本地查询,从Postgres的Angular 来看,这似乎是我真正需要的
@Query(value = "select DISTINCT * from xml_object message " +
" left join xml_object_tree tree1 on message.id = tree1.parent_id " +
" left join xml_object sender on sender.id = tree1.child_id " +
" left join xml_object_tree tree2 on sender.id = tree2.parent_id " +
" left join xml_object area on area.id = tree2.child_id " +
" left join xml_object_tree tree3 on area.id = tree3.parent_id " +
" left join xml_object measuringPoint on measuringPoint.id = tree3.child_id " +
" left join xml_object_tree tree4 on measuringPoint.id = tree4.parent_id " +
" left join xml_object measuringChannel on measuringChannel.id = tree4.child_id " +
" left join xml_period period on period.xml_object_id = measuringChannel.id " +
" where message.id IN :ids", nativeQuery = true)
Set<XmlObject> findAllObjectsByIdsFetchChildsAndPeriods(Iterable<Long> ids);
但是它为连接的每个对象原因返回几行,这是不可接受的。
我已经尝试了这个查询从Hibernate的Angular 来看,但它只提取第一级的查尔兹,但我需要所有的树加上XmlPeriods从最后一级。不能使用几个JOIN FETCH原因笛卡尔积(集)和JPA限制(列表)
@Query(value = "select xo from XmlObject xo " +
" join fetch xo.childObjects senders " +
" where xo.id IN :ids")
List<XmlObject> findAllObjectsByIdsFetchingChilds(Iterable<Long> ids);
我
1条答案
按热度按时间68bkxrlz1#
您将需要一个递归CTE来对此建模,并且由于Hibernate不支持递归获取连接,因此您还必须自己构建对象图。
我们的想法是这样编写一个HQL查询:
您将需要Hibernate 6.2或Blaze-Persistence。
结果是所有节点的列表,然后您只需正确地连接它们