I am using JSR-220 PDF, section 4 as a reference. Not very user
friendly, but that's ultimate reference:
http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
Hmm, the issue with EJBQL prefetching was fixed some time ago per https://issues.apache.org/jira/browse/CAY-1366
.. (and we should have unit tests for it somewhere) Maybe you
stumbled on a bug?
Andrus
On May 6, 2010, at 1:44 PM, Andrew Lindesay wrote:
> Hi Andrus;
>
> Thanks for that –– is there a resource book/web-site/cheat-sheet on
> EJBQL which you might be able to recommend? My Googling has only
> really dredged up poor material thus far. I am a new to EJBQL so am
> not sure on the significance of the "c" in this old query I was
> running, but adding "FETCH" seems to not be able to parse it;
>
> ...JOIN u.someToMany c WHE...
>
> For this reason, I have removed the "c". I tried both;
>
> SELECT u FROM FooBar u LEFT OUTER JOIN FETCH u.someToMany WHERE
> u.userType = :userType ORDER BY u.username
> SELECT u FROM FooBar u LEFT JOIN FETCH u.someToMany WHERE
> u.userType = :userType ORDER BY u.username
>
> I guess the latter "LEFT JOIN FETCH" makes more sense to me in this
> case. In both cases I find that the to-many relationship is
> loaded, but is empty. If I remove the JOIN FETCH and restart the
> application server it will produce the correct result.
>
> The SQL it runs seems to gather all the columns for "u" (above) and
> then additional rows from the to-many. The data is there when I run
> the query in a console, but the to-many seems to be empty in the
> java environment when I get the "u" objects back from Cayenne.
>
> Maybe I am doing something wrong?
>
> cheers.
>
>> Yes, the syntax is something like this:
>> [LEFT[OUTER]|INNER]JOIN FETCH
>> So you are looking for "LEFT JOIN FETCH", not just "LEFT JOIN".
>> I.e. "FETCH" is what does the prefetching of a relationship, and
>> the rest is specifying the join semantics and can be used with or
>> without prefetching.
> ...
>>> Thanks for the suggestion. I did try that with this;
>>> SELECT u FROM FooBar u OUTER JOIN u.someToMany c WHERE u.userType
>>> = :userType ORDER BY u.username
>>> I get the following exception;
> ...
>>> I also tried a LEFT JOIN which I think would do what I want and it
>>> certainly picks up the rows as I would anticipate from the SQL
>>> log, but the to-many doesn't seem to freshen.
> ...
>>>> IIRC there's also an OUTER fetch join that handles this case.
>>> ...
>>>>>> yeah, you can use fetch joins (since 3.0). something like
>>>>>> select f FROM FooBar f inner join fetch f.bars b ...
>>> ...
>>>>> I tried that and it produces a query which excludes objects from
>>>>> the result-set where there is nothing in the join. The
>>>>> behaviour of "query.addPrefetch(..)" would be to run two SELECT
>>>>> database queries to get the additional data and freshen-up the
>>>>> relationship if my thinking is correct.
>
> ___
> Andrew Lindesay
> www.silvereye.co.nz
>
This archive was generated by hypermail 2.0.0 : Thu May 06 2010 - 06:55:30 EDT