如何获取JPA-ECLIPSELINK中的嵌套实体，以防属于@manytoone

Question

我有多个我想在单个查询中获取的实体，因为n+1查询持续时间太长。 例如，SQL加入查询持续5秒在DB上，但是由于n+1提取，Elcipselink持久性提取持续50-80秒。 我发现，在实现@manytoone关系后，左加入Fetch无法正常工作。 有人知道解决此案的解决方案吗？

请在下面找到简化的实体。

 @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; 
}

这就是我的获取方式。 我已经尝试了以下所有组合，但是每个组合都会对centerdata中的每一行（对象）进行查询，或者在某些情况下对每种加入的情况data coindata加入centaineRecord。

     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();

Answer 1

由于您没有明确使用Eclipselink版本，因此我只假设版本2.6。

按照 eclipselink.left_fetch 不是受支持的查询提示。您可能正在尝试使用eclipselink.join-fetch或eclipselink.left-join-fetch as 。

使用此提示的正确方法沿着：

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();

另一种方法是批次提取。

这将起作用：

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

您可能会测试两种方法并收集一些指标；然后，确定哪个对DB模式和使用模式更好（在性能方面）。