JPA获取一对多 [英] JPA fetching one to many

问题描述

我有以下实体：事件和属性。一个事件可以有很多属性。

I have the following entities: Event and Attribute. An event can have many attributes.

我遇到的问题是，我似乎无法找到在单个SQL查询中返回具有其属性的事件的查询。我可能有数百万个需要迭代的事件，我不想懒惰地加载它们，出于明显的性能原因。

The problem I am having is, I can't seem to find a query that returns the events with their attributes in a single SQL query. I could have millions of events that need to be iterated through and I don't want to lazy load them all for obvious performance reasons.

我试过使用fetch该查询，但它为每个属性返回一个事件。即如果事件具有2个属性，则它将返回2个具有一个属性的事件。

I've tried using fetch in the query, but it returns an event for every attribute. i.e. if an event had 2 attributes it would return 2 events each with one attribute.

我想要的是一个具有2个属性的事件。

What I want is one event with 2 attributes.

SELECT e FROM Event e LEFT JOIN FETCH e.attributes

如果我添加了DISTINCT，它会起作用，但它会创建一个独特的SQL查询，它对于大型数据集非常缓慢。

If I add DISTINCT it works, but then it creates a distinct SQL query which is extremely slow over large data sets.

public class Event {
  @OneToMany(mappedBy = "event", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  public Set<Attribute> getAttributes(){}
}

public class Attribute {
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name = "event_id", nullable = false)
    public Event getEvent() {
        return event;
    }
}

解决方案

我无法找到JPA解决方案。相反，我将EntityManager解包到Hibernate会话中，并使用@milkplusvellocet建议的条件结果转换器。这会检索不同的根实体，而不会创建独立的SQL查询。

I couldn't find a JPA solution. Instead, I unwrapped the EntityManager to the Hibernate session and used the criteria's result transformer as @milkplusvellocet suggested. This retrieves distinct root entities without creating a distinct SQL query.

Session session = em.unwrap(Session.class);
Criteria criteria = session.createCriteria(Event.class);
criteria.setFetchMode("attributes", FetchMode.JOIN);

criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);  

需要注意的一点是，我尝试从解包的会话中使用HQL。我向查询添加了DISTINCT，并将结果转换器设置为DISTINCT_ROOT_ENTITY，类似于上面的标准解决方案。这样做 still 创建了一个独特的SQL查询。

One thing to note is, I tried using HQL from the unwrapped session. I added DISTINCT to the query and set the result transformer to DISTINCT_ROOT_ENTITY similar to the criteria solution above. Doing it this way still created a distinct SQL query.

推荐答案

您需要一个 ResultTransformer 。

尝试以下HQL：

Try the following HQL:

SELECT DISTINCT e FROM Event e LEFT JOIN FETCH e.attributes

这相当于在 CriteriaSpecification.DISTINCT_ROOT_ENTITY 中使用一个标准查询。

This is equivalent to using CriteriaSpecification.DISTINCT_ROOT_ENTITY in a criteria query.

这篇关于JPA获取一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！