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
