如果子物體與@ManyToOne相關，如何在JPA-eclipselink中獲取連接嵌套物體

我有多個要在單個查詢中獲取的物體，因為 N 1 查詢持續時間太長。例如，SQL 連接查詢在 DB 上持續 5 秒，但由于 N 1 提取，elcipselink 持久性提取持續 50-80 秒。我發現一旦實作@ManyToOne 關系，LEFT JOIN FETCH 就無法正常作業。有誰知道這種情況下 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 加入 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();

uj5u.com熱心網友回復：

由于您沒有明確說明正在使用的 EclipseLink 版本，我只假設版本為 2.6。

根據官方 EclipseLink 檔案，eclipselink.LEFT_FETCH不支持查詢提示。您可能正在嘗試使用eclipselink.join-fetch或eclipselink.left-join-fetch按照此處記錄的方式。

使用此提示的正確方法是：

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");
//...

您可能會測驗這兩種方法并收集一些指標；然后決定哪一個更適合您的資料庫架構和使用模式（在性能方面）。

標籤：爪哇 jpa 日食链接

上一篇：嘗試使用JPA查詢我的資料庫時出現500錯誤

下一篇：Java@Overrideequals():當this.getClass()!=o.getClass()失敗但不應該