1

In my application, I have an entity A with a list of entities B that should be fetched eagerly :

@Entity
public class A
{
    ...
    /* @OrderBy("cValue.id ASC") */
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name="A_ID", nullable=false)
    private List<B> BEntries = new ArrayList<B>();
    ...
}

@Entity
public class B
{
    ...
    @ManyToOne
    @JoinColumn(name = "C_ID", nullable = false)
    private C cValue;
    ...
}

In order to get the list of A, I was first doing this simple query :

CriteriaBuilder critBuilder = em.getCriteriaBuilder();
CriteriaQuery<A> critQuery = critBuilder.createQuery(A.class);
Root<A> critRoot = critQuery.from(A.class);
critQuery.select(critRoot);

But there I saw that Hibernate was doing N+1 select queries on the database, 1 on class A, and N on class B (where N is the number of tuples of A in DB).

I was very surprise that, for eager fetching, Hibernate was not directly doing a LEFT JOIN query.

So I first tried to use the annotation @Fetch(FetchMode.JOIN) of Hibernate, but it was not working as expected.

So I transformed my list query with the following additional instructions:

Join<A,B> joinAB = critRoot.join(A_.BEntries, JoinType.LEFT);
joinAB.join(B_.cValue, JoinType.LEFT);

Ok, now the resulting SQL query contains all the needed LEFT JOIN to build the full A object eagerly... but it's still doing the other N queries on B table! I first thought it was coming from the @OrderBy annotation I put on the Bentries parameter, but even when removed, it's still doing N+1 selects instead of 1...

Any idea why it's behaving like this?... and even why it's not doing a LEFT JOIN by default on eagerly fetched collections in entities?

Stéphane Wantiez
  • 71
  • 1
  • 4
  • 3
    You want a *join fetch* not just a *join*. I believe you need to change `critRoot.join` to `critRoot.fetch` – Colin M Jul 10 '13 at 12:42
  • Great, it's doing a single select now. That's was simple, thanks. :) – Stéphane Wantiez Jul 10 '13 at 12:46
  • BTW, why is it doing N+1 selects by default, and not such fetch? – Stéphane Wantiez Jul 10 '13 at 12:49
  • There are many reasons why you might want to join a separate table without actually fetching it. Maybe you want to filter by a column on a related table, or order by a column of a related table. But, you don't need the data to come back in your result. Consider the case where I want to see all of the posts from all of my friends. Well, I don't care (in this case) *who* my friends are, I just care that the only posts I get back are ones that are from them. This is a case where you'd just want a join without the data. – Colin M Jul 10 '13 at 12:51
  • So it's not possible to do it through annotation? I got the same issue with @Fetch(FetchMode.JOIN) not working. – TheBakker Aug 07 '15 at 08:57

0 Answers0