How to fetch join nested entities in JPA-eclipselink, in case of sub-entity being in relation @ManyT-CodePudding

I have multiple entities that i want to fetch in single query, because N 1 queries last too long. For example SQL join query lasts 5 seconds on DB, but elcipselink persistence fetching lasts 50-80 seconds due to N 1 fetching. I found out that LEFT JOIN FETCH is not working as soon as @ManyToOne relation is implemented. Does anyone know solution to LEFT JOIN FETCH for this case?

Please find below simplified entities.

 @Entity
@Table(name="SITUATION_DATA")
@NamedQuery(name="SituationData.findAll", query="SELECT s FROM SituationData s")
public class DatexSituationData implements Serializable {
private static final long serialVersionUID = 1L;
    
//bi-directional many-to-one association to SituationRecord
@OneToMany(mappedBy="datexSituationData", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecord> situationRecords; 
}


 @Entity
@Table(name="SituationRecord")
@NamedQuery(name="SituationRecord.findAll", query="SELECT s FROM SituationRecord s")
public class SituationRecord implements Serializable {
private static final long serialVersionUID = 1L;

@OneToMany(mappedBy="situationRecord", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecordComment> situationRecordComment;

@OneToMany(mappedBy="situationRecord", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
private List<SituationRecordTypeElement> situationRecordTypeElements;
    
//bi-directional many-to-one association to SituationLocation
@ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
@JoinFetch(value=JoinFetchType.OUTER)
@JoinColumn(name="ID_LOKACIJE")
private SituationLocation situationLocation;

//bi-directional many-to-one association to DatexSituationData
@ManyToOne()
@JoinColumns({
    @JoinColumn(name="SITUATION_ID", referencedColumnName="ID", nullable=false),
    @JoinColumn(name="SITUATION_VERSION", referencedColumnName="VERSION", nullable=false)
    })
private DatexSituationData datexSituationData;  
}

 @Entity
@Table(name="SITUATION_LOCATIONS")
@NamedQuery(name="SituationLocation.findAll", query="SELECT s FROM SituationLocation s")
public class SituationLocation implements Serializable {
private static final long serialVersionUID = 1L;

@Id 
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="situation_location_seq")
@SequenceGenerator(name="situation_location_seq", sequenceName="SEQ_SITUATION_LOCATION", allocationSize=1)
@Column(name="ID_LOKACIJE", unique=true, nullable=false)    
private long idLokacije;

//bi-directional many-to-one association to SituationRecord
@OneToMany(mappedBy="situationLocation", cascade = CascadeType.PERSIST) 
private List<SituationRecord> situationRecords; 
}

This is how i fetch it. I have tried all of the below combinations, but every combinations makes query for each row (object) in SituationData, or in some cases for each joined SituationData join SituationRecord.

     String sQuery =                
    //"select * from SITUATION_DATA t";
    //"SELECT * FROM (select t.*, rank() over(partition by t.id order by version desc) rnk from SITUATION_DATA t) where rnk = 1";
    "SELECT ds FROM SituationData ds LEFT JOIN FETCH ds.situationRecords sr LEFT JOIN FETCH sr.situationLocation sl LEFT JOIN FETCH sr.situationRecordTypeElements sre LEFT JOIN FETCH sr.situationRecordComment src";      

 EntityManager em = Emf.getInstance().getFactory().createEntityManager();
 //Query q = em.createNativeQuery(sQuery, DatexSituationData.class);
        Query q = em.createQuery(sQuery, DatexSituationData.class);
//      q.setHint("eclipselink.LEFT_FETCH", "t.situationRecords.situationRecordComment");
        q.setHint("eclipselink.LEFT_FETCH", "ds.sr.sl");
        q.setHint("eclipselink.LEFT_FETCH", "ds.sr.sre");
        q.setHint("eclipselink.LEFT_FETCH", "ds.sr.src");
//      q.setHint("eclipselink.JDBC_FETCH_SIZE", "100");
                
        lResult = q.getResultList();

CodePudding user response：

Since you are not explicitly state EclipseLink version being used, I'll just assume version 2.6.

As per the official EclipseLink documentation, eclipselink.LEFT_FETCH is not a supported query hint. You are probably trying to use eclipselink.join-fetch or eclipselink.left-join-fetch as documented here.

The proper way to use this hint is along the lines of:

String sQuery = "SELECT ds FROM DatexSituationData ds";      

EntityManager em = emf.getInstance().getFactory().createEntityManager();
TypedQuery q = em.createQuery(sQuery, DatexSituationData.class);
q.setHint("eclipselink.join-fetch", "ds.situationRecords");
q.setHint("eclipselink.join-fetch", "ds.situationLocation");
// ...

lResult = q.getResultList();

Another approach would be batch fetching.

This would work as:

//...
em
.createQuery("SELECT ds FROM DatexSituationData ds")
.setHint("eclipselink.batch", "ds.situationRecords")
//...
.setHint("eclipselink.batch.type", "IN")
.setHint("eclipselink.batch_size", "1000");
//...

You would probably test both approaches and gather some metrics; then decide which one works better (in terms of performance) for your DB schema and usage patterns.