在子实体与@ManyToOne相关的情况下，如何在JPA eclipselink中获取连接嵌套实体

Question

我想在单个查询中获取多个实体，因为n1查询持续时间太长。例如，SQL join查询在数据库上持续5秒，但由于N 1抓取，elcipselink持久性抓取持续50-80秒。我发现只要@ManyToOne关系被实现，LEFT JOIN 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; 
}

我就是这样取的。我已经尝试了以下所有组合，但每个组合都会对SituationData中的每一行（对象）进行查询，或者在某些情况下对每个连接的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();

Answer 1

由于您没有明确声明正在使用的Eclipse Link版本，我将假设版本2.6。

根据EclipseLink官方文档，EclipseLink。LEFT_FETCH不是受支持的查询提示。您可能正在尝试使用eclipselink。加入fetch或eclipselink。左连接获取，如本文所述。

使用此提示的正确方法如下：

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模式和使用模式（在性能方面）。