How to eagerly fetch two-level nested associations?

[Paulo Rodrigues Pinto <re...@gmail.com>]

I'm trying to efficiently fetch a two-level nested association using
OpenJPA 2.2.0. Basically, I have one Athat has one B that has many C:

@Entitypublic class A {
    ...

    @ManyToOne(fetch=FetchType.EAGER)
    private B b;}
@Entitypublic class B {
    ...

    @OneToMany(mappedBy="b", fetch=FetchType.EAGER)
    private List<C> cs;}
@Entitypublic class C {
    ...}

My JPQL query is as follows:

SELECT a
FROM A a
WHERE a.id = some condition

 I set the EagerFetchMode<http://openjpa.apache.org/builds/2.2.0/apache-openjpa/docs/ref_guide_perfpack_eager.html>
to
"PARALLEL" in order to have a second query that retrieves the Cs of all the
Bs:

<property name="openjpa.jdbc.EagerFetchMode"
value="parallel"/><property
name="org.apache.openjpa.persistence.jdbc.EagerFetchMode"
value="PARALLEL"/>

 The problem is that openjpa is generating n+1 queries instead of 2: one
for the As and Bs and one for each C:

SELECT *FROM aLEFT OUTER JOIN b
    ON a.b_id = b.id WHERE some condition

 and n times

SELECT *FROM c WHERE c.b_id = x

 *How can I make openjpa issue two queries instead of n+1?*

RE: How to eagerly fetch two-level nested associations?

[Henno Vermeulen <he...@huizemolenaar.nl>]

I have been struggling for quite some time with the exact same situation to the point of using the debugger, downloading the code and creating official OpenJPA unit tests. My current conclusion is that it's impossible with OpenJPA. So please comment/vote/watch the issue that I created https://issues.apache.org/jira/browse/OPENJPA-2296. Unfortunately I have had no response at all from an OpenJPA committer yet. I even succeeded in getting the situation A *..1 B 1..* C working by "flipping a bit" however I have no idea on the full consequences of such a change.

I found that Hibernate has an annotation @Fetch(FetchMode.SUBSELECT) which does exactly what we want. Alternatively Hibernate has an @BatchSize annotation to fetch a given number of C at once. But unfortunately I have not found such an option with OpenJPA.

What is actually happening is that for each B and each field in B that is a relation such as a collection of C, one query is executed to fetch that relation. (So I guess you did not mean a query for each C, but a query on C for each B). I think an ugly workaround may be done by using cached entities in the entity manager context. If you first select all B that match your where on A, OpenJPA should be able to reuse these cached B's in your query for A.

Henno


-----Oorspronkelijk bericht-----
Van: Paulo Rodrigues Pinto [mailto:regularjack@gmail.com] 
Verzonden: zondag 2 december 2012 2:04
Aan: users@openjpa.apache.org
Onderwerp: How to eagerly fetch two-level nested associations?

I'm trying to efficiently fetch a two-level nested association using
OpenJPA 2.2.0. Basically, I have one Athat has one B that has many C:

@Entitypublic class A {
    ...

    @ManyToOne(fetch=FetchType.EAGER)
    private B b;}
@Entitypublic class B {
    ...

    @OneToMany(mappedBy="b", fetch=FetchType.EAGER)
    private List<C> cs;}
@Entitypublic class C {
    ...}

My JPQL query is as follows:

SELECT a
FROM A a
WHERE a.id = some condition

 I set the EagerFetchMode<http://openjpa.apache.org/builds/2.2.0/apache-openjpa/docs/ref_guide_perfpack_eager.html>
to
"PARALLEL" in order to have a second query that retrieves the Cs of all the
Bs:

<property name="openjpa.jdbc.EagerFetchMode"
value="parallel"/><property
name="org.apache.openjpa.persistence.jdbc.EagerFetchMode"
value="PARALLEL"/>

 The problem is that openjpa is generating n+1 queries instead of 2: one
for the As and Bs and one for each C:

SELECT *FROM aLEFT OUTER JOIN b
    ON a.b_id = b.id WHERE some condition

 and n times

SELECT *FROM c WHERE c.b_id = x

 *How can I make openjpa issue two queries instead of n+1?*