JPA fetching one to many

Question

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

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.

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.

What I want is one event with 2 attributes.

SELECT e FROM Event e LEFT JOIN FETCH e.attributes

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;
    }
}

Solution

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);  

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.

Answer 1

You need a ResultTransformer.

Try the following HQL:

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

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