java - Avoiding n+1 eager fetching of child collection element association

Question

I have the following classes:

@Entity
@Table(name = "base")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "DISCRIMINATOR", discriminatorType = DiscriminatorType.STRING)
@ForceDiscriminator
public class Base {
    // ...
}

@Entity
@DiscriminatorValue("foo")
public class Foo extends Base {
    @OneToMany( mappedBy = "foo", cascade=CascadeType.ALL )
    private List<Bar> bars = new ArrayList<Bar>();

    // ...
}

@Entity
public class Bar {
    @ManyToOne (optional = false)
    @JoinColumn(name = "foo_id" )
    private Foo foo;

    @OneToOne
    @JoinColumn(name = "baz_id", nullable = false)
    private Baz baz;

    //...
}

@Entity
public class Baz {
    // ...
}

Now I basically want to load all Base, but eager load bars when applicable, so I use the following query:

SELECT b FROM Base b LEFT JOIN FETCH b.bars

While this works, it seems to generate a SELECT N+1 problem for the Bar entities:

Hibernate: /* SELECT b FROM Base b LEFT JOIN FETCH b.bars */ SELECT ...
Hibernate: /* load com.company.domain.Baz */ SELECT ...
Hibernate: /* load com.company.domain.Baz */ SELECT ...

Is it possible to tell hibernate to eagerly load an association for each element in the child collection without resorting to N+1 SELECTs?

I tried something along the lines of the following query, which obviously does not work since its a collection:

SELECT b FROM Base b LEFT JOIN FETCH b.bars LEFT JOIN FETCH b.bars.baz
//Results in: illegal attempt to dereference collection [Foo.id.bars] with element property reference [baz]

I also tried using IN(b.bars) bars, and while this allows me to reference the child collection, it does not seem to eagerly load the bars collection which is my goal.

An explanation of why this happens would also be nice, since I cannot seem to figure it out.

Answer 1

If you would like to retrieve Bar and Baz with out (n+1) selects please use the following hql.

SELECT b FROM Base b LEFT JOIN FETCH b.bars bar LEFT JOIN FETCH bar.baz

This should result in just one sql.

Also, if you do not want to fetch 'Baz', just the make the association from Bar->Baz 'lazy'.

JPA by default enforces 'eager' fetching for '@OneToOne' and '@ManyToOne' associations. So, you have to explicitly make it lazy as shown below.

@Entity
public class Bar {

    @OneToOne
    @JoinColumn(name = "baz_id", nullable = false, fetch=FetchType.Lazy)
    private Baz baz;

    //...
}