JPA / eclipselink: join fetch all children annotated @OneToOne in one query

Question

we try to join fetch data in one query but only the LocationAddress is join-fetched. for the LocationGeodata jpa always produces a second query. The @JoinFetch-Annotation in LocationAddress is not respected.

given the following model and unidirectional annotations:

Location-----<1>LocationAddress-----<1>LocationGeodata

each location has one address and each address references a global geodata-table.it is also granted that we don't have null values in any table.

mappings:

@Entity
public class Location {

   @OneToOne(cascade=CascadeType.ALL)
   @JoinFetch(JoinFetchType.INNER)  
   private LocationAddress locationAddress;
}

@Entity
public class LocationAddress {

    @OneToOne   
    @JoinFetch(JoinFetchType.INNER)
    private LocationGeodata locationGeoData;    
}

selecting a location by its id produces always two queries:

@NamedQuery(
        name="Location.getById",              
        query="SELECT l FROM Location l WHERE l.id = :id"
)

//produces same sql-result as above query
@NamedQuery(
        name="Location.getById",              
        query="SELECT l, lg FROM Location l
               JOIN FETCH l.locationAddress a
               JOIN FETCH a.locatonGeodata lg
               WHERE l.id = :id"
)

SELECT t1.ID, t1.DESCRIPTION, t1.LOCATIONADDRESS_ID, t2.STREET, 
       t2.HOUSENUMBER, t2.LOCATIONGEODATA_ID FROM LOCATION t1 
INNER JOIN LOCATIONADDRESS t2 
WHERE t1.LOCATIONADDRESS_ID = t2.ID AND t1.ID = ?


SELECT ID, ADMINCODE1, ADMINNAME1, COUNTRYCODE, LAT, LONG 
FROM LOCATIONGEODATA WHERE (ID = ?)

how can we combine the second query into the first one? thanks for advice!

Relevant Table Definition:

Location
locationAdress_ID

LocationAddress
loationGeoData_ID

Steve · Answer 1 · 2016-08-19T07:14:47.357

strange, i spent days to try and error and find an answer and after posted the question here, the solution is found. adding a query hint produces the desired query:

@NamedQuery(
    name="Location.getById",              
    query="SELECT l FROM Location l WHERE l.id = :id",
    hints= {
        @QueryHint(name = "eclipselink.join-fetch", 
                   value = "l.locationAddress.locationGeodata")               
    }

)

thanks to

http://java-persistence-performance.blogspot.de/2010/08/batch-fetching-optimizing-object-graph.html

topic refers to section "Nested join fetch query hint"

JPA / eclipselink: join fetch all children annotated @OneToOne in one query

1 Answers1