[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] FW: Re: Use of eclipselink hinting
|
You missed the join fetch in the JPQL.
Dave Brosius-2 wrote:
>
> Ok, i removed all the hinting, and join fetches as such but again, the
> only table that fields were selected from was the main (Task) table
>
> 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(" inner join t.project p");
> sb.append(" where p.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.taskrsrcCollection.resource");
> //query.setHint(QueryHints.FETCH, "t.taskrsrcCollection.role");
> //query.setHint(QueryHints.FETCH, "t.projectWBS");
> //query.setHint(QueryHints.FETCH, "t.projectWBS.obs");
> results.addAll(query.getResultList());
>
>
> 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.WBS_ID, t1.PROJ_ID, t1.CLNDR_ID FROM RSRCHOUR t6,
> TSDATES t5, TASKRSRC t4, RSRC t3, PROJWBS t2, TASK t1, PROJECT t0 WHERE
> (((((t0.ORIG_PROJ_ID IS NULL) AND (t2.STATUS_CODE = ?)) AND (t3.RSRC_ID =
> ?)) AND (t5.TS_ID = ?)) AND ((((((t0.PROJ_ID = t1.PROJ_ID) AND (t2.WBS_ID
> = t1.WBS_ID)) AND (t4.TASK_ID = t1.TASK_ID)) AND (t3.RSRC_ID =
> t4.RSRC_ID)) AND (t6.TASKRSRC_ID = t4.TASKRSRC_ID)) AND (t5.TS_ID =
> t6.TS_ID)))
> bind => [WS_Open, 98984, 20081221]
> -----Original Message-----
> From: "Dave Brosius" <dbrosius@xxxxxxxxxxxxxxx>
> Sent: Monday, February 2, 2009 12:44pm
> To: "EclipseLink User Discussions" <eclipselink-users@xxxxxxxxxxx>
> Subject: Re: [eclipselink-users] Use of eclipselink hinting
>
> I am using version 1.02.
>
> I will experiment with simplifying the hints used and see what happens
>
> -----Original Message-----
> From: "James Sutherland" <jamesssss@xxxxxxxxx>
> Sent: Monday, February 2, 2009 11:10am
> To: eclipselink-users@xxxxxxxxxxx
> Subject: Re: [eclipselink-users] Use of eclipselink hinting
>
>
> That is odd. I can't see any reason for this to occur. What version of
> EclipseLink are you using?
>
> It may be somehow related to the mixing of join fetch in the JPQL and the
> other hints. If you remove the other hints, or simplify the JPQL (remove
> the inner joins), does it work?
>
>
>
> Dave Brosius-2 wrote:
>>
>> Here's the code. I have commented out the hint for t.project, but notice
>> i
>> have a join fetch on t.project.
>>
>> List results = new ArrayList();
>> 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"
>> 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/FW%3A-Re%3A-Use-of-eclipselink-hinting-tp21814105p21830546.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
