Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel