Re: [eclipselink-users] Use of eclipselink hinting

["Dave Brosius" <dbrosius@xxxxxxxxxxxxxxx>]

Here's the code. I have commented out the hint for t.project, but notice i have a join fetch on t.project.

        List<Task> results = new ArrayList<Task>();
        StringBuilder sb = new StringBuilder("select distinct t from Task t");
        sb.append(" inner join t.taskrsrcCollection tr");
        sb.append(" inner join tr.rsrchourCollection rh");
        sb.append(" inner join tr.resource r");
        sb.append(" inner join rh.timesheetPeriod tp");
        sb.append(" join fetch t.project");
        sb.append(" where t.project.origProjId is null");
        sb.append(" and t.projectWBS.statusCode = ?1");
        sb.append(" and r.resourceId = ?2");
        sb.append(" and tp.timesheetPeriodId = ?3");

        Query query = getEntityManager().createQuery(sb.toString());
        query.setParameter(1, ProjectWBSStatus.WS_Open);
        query.setParameter(2, resourceId);
        query.setParameter(3, timesheetId);
        //query.setHint(QueryHints.FETCH, "t.project");
        query.setHint(QueryHints.FETCH, "t.taskrsrcCollection");
        query.setHint(QueryHints.FETCH, "t.projectWBS");        
        query.setHint(QueryHints.FETCH, "t.projectWBS.obs");
        results.addAll(query.getResultList());

Here is the query. Notice no fields for project (t4) are loaded.  (When i put the setHint back in fields on project are loaded)

SELECT DISTINCT t1.TASK_ID, t1.RSRC_ID, t1.ACT_END_DATE, t1.TASK_CODE, t1.TASK_NAME, t1.REVIEW_TYPE, t1.STATUS_CODE, t1.TASK_TYPE, t1.ACT_START_DATE, t1.RESTART_DATE, t1.REEND_DATE, t1.REVIEW_END_DATE, t1.TARGET_START_DATE, t1.TARGET_END_DATE, t1.EXPECT_END_DATE, t1.PRIORITY_TYPE, t1.DURATION_TYPE, t1.TOTAL_FLOAT_HR_CNT, t1.TARGET_DRTN_HR_CNT, t1.COMPLETE_PCT_TYPE, t1.SUSPEND_DATE, t1.RESUME_DATE, t1.ACT_WORK_QTY, t1.ACT_EQUIP_QTY, t1.TARGET_WORK_QTY, t1.TARGET_EQUIP_QTY, t1.REMAIN_DRTN_HR_CNT, t1.REMAIN_WORK_QTY, t1.EST_WT, t1.LATE_END_DATE, t1.REM_LATE_END_DATE, t1.FREE_FLOAT_HR_CNT, t1.REV_FDBK_FLAG, t1.EARLY_START_DATE, t1.EARLY_END_DATE, t1.REM_LATE_START_DATE, t1.PHYS_COMPLETE_PCT, t1.LATE_START_DATE, t1.REMAIN_EQUIP_QTY, t1.PROJ_ID, t1.CLNDR_ID, t1.WBS_ID, t0.TASKRSRC_ID, t0.TASK_ID, t0.RSRC_ID, t0.ACT_END_DATE, t0.PEND_COMPLETE_PCT, t0.PEND_REMAIN_QTY, t0.PEND_ACT_OT_QTY, t0.PEND_ACT_REG_QTY, t0.ACT_START_DATE, t0.RESTART_DATE, t0.REEND_DATE, t0.ACT_REG_QTY, t0.ACT_OT_QTY, t0.TARGET_QTY, t0.RELAG_DRTN_HR_CNT, t0.REMAIN_QTY_PER_HR, t0.REMAIN_QTY, t0.TARGET_START_DATE, t0.TARGET_END_DATE, t0.ACTUAL_CRV, t0.REMAIN_CRV, t0.TARGET_LAG_DRTN_HR_CNT, t0.TARGET_QTY_PER_HR, t0.TARGET_COST, t0.ACCT_ID, t0.OT_FACTOR, t0.GUID, t0.CURV_ID, t0.TS_PEND_ACT_END_FLAG, t0.COST_QTY_LINK_FLAG, t0.RSRC_TYPE, t0.COST_PER_QTY, t0.ACT_OT_COST, t0.ACT_REG_COST, t0.REMAIN_COST, t0.POBS_ID, t0.ROLLUP_DATES_FLAG, t0.PROJ_ID, t0.ROLE_ID, t2.WBS_ID, t2.PROJ_ID, t2.WBS_NAME, t2.STATUS_CODE, t2.SEQ_NUM, t2.WBS_SHORT_NAME, t2.PROJ_NODE_FLAG, t2.EST_WT, t2.OBS_ID, t2.PARENT_WBS_ID, t3.OBS_ID, t3.SEQ_NUM, t3.OBS_NAME FROM RSRCHOUR t8, TSDATES t7, TASKRSRC t6, RSRC t5, PROJECT t4, OBS t3, PROJWBS t2, TASK t1, TASKRSRC t0 WHERE (((((t4.ORIG_PROJ_ID IS NULL) AND (t2.STATUS_CODE = ?)) AND (t5.RSRC_ID = ?)) AND (t7.TS_ID = ?)) AND ((((((((t4.PROJ_ID = t1.PROJ_ID) AND (t2.WBS_ID = t1.WBS_ID)) AND (t6.TASK_ID = t1.TASK_ID)) AND (t5.RSRC_ID = t6.RSRC_ID)) AND (t8.TASKRSRC_ID = t6.TASKRSRC_ID)) AND (t7.TS_ID = t8.TS_ID)) AND (t0.TASK_ID = t1.TASK_ID)) AND (t3.OBS_ID = t2.OBS_ID)))
    bind => [WS_Open, 5654, 792]


-----Original Message-----
From: "James Sutherland" <jamesssss@xxxxxxxxx>
Sent: Thursday, January 29, 2009 10:58am
To: eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] Use of eclipselink hinting


Using "JOIN FETCH" in JPQL should work, the hint is only required for nested
join fetchs which are not part of the JPQL spec.

Please include your full query code and JPQL and the SQL generated for it.



Dave Brosius-2 wrote:
> 
> Greetings, i've noticed that for my queries that specify join fetches,
> EclipseLink doesn't actually do the join fetch. I then noticed the
> specification to do
> 
> query.setHint("eclipselink.join-fetch", "t.project");
> 
> and that does, indeed, cause a join fetch.
> 
> Am I to understand that specifying 'join fetch' by itself in queries does
> nothing in eclipse link and i must use the hinting as above?
> 
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/OneToOne-Mapping-works-with-FetchType.EAGER-but-not-with-FetchType.LAZY--%3E-Exception-when-deploying-%28SGES-2.1%29-tp21711849p21729668.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users