I have a question. I had a made a small change to some of my JPA based
services and while it worked fine in the client(s), the unit tests were
failing. Upon closer examination, the issue seems to be tied to my usage
of performing multiple join-fetch statements on a query.
In all cases I am performing a query on object Stamp, which has two
relationships which for these queries are important for me to fetch at
once (since some values are used in tables).
The first relationship, CatalogueNumbers is a 1:1...N relatinship.
The second relationship, Ownership is an optional one of 1:0...N
cardinality.
When I include the query hints to include both stamp.catalogueNumbers and
stamp.stampOwnerships the Queries look alright, but the results always
come back empty. I am assuming this is because there is an AND condition
where "stamp.id=ownership.stamp.id" only ownership is empty in this case.
Does anyone know how to change the query to "optionally" join-fetch if
ownership is defined?
My original query was a named query of:
"SELECT stamp FROM Stamp stamp WHERE stamp.country.id=:country_id"
The resultant SQL (I am using MySQL 5 if that is relevant) for a single
join-fetch to CatalogueNumbers is (which works):
SELECT t1.ID, t1.CREATESTAMP, t1.MODIFYSTAMP, t1.DESCRIPTION,
t1.DENOMINATION, t1.PRINTING, t1.COUNTRY_ID, t0.ID, t0.CREATESTAMP,
t0.MODIFYSTAMP, t0.CATALOGUEVALUE, t0.ACTIVE, t0.CAT_CONDITION, t0.NUMBER,
t0.STAMP_ID, t0.CATALOGUE_REF FROM CATALOGUENUMBERS t3, STAMPS t2, STAMPS
t1, CATALOGUENUMBERS t0 WHERE (((t1.ID = t2.ID) AND (t3.NUMBER LIKE ?))
AND ((t2.ID = t3.STAMP_ID) AND (t0.STAMP_ID = t1.ID)))
Whereas the mutliple join-fetch (including the 0 cardinality Ownership) is:
SELECT t1.ID, t1.CREATESTAMP, t1.MODIFYSTAMP, t1.DESCRIPTION,
t1.DENOMINATION, t1.PRINTING, t1.COUNTRY_ID, t0.ID, t0.CREATESTAMP,
t0.MODIFYSTAMP, t0.CATALOGUEVALUE, t0.ACTIVE, t0.CAT_CONDITION, t0.NUMBER,
t0.STAMP_ID, t0.CATALOGUE_REF, t2.ID, t2.CREATESTAMP, t2.MODIFYSTAMP,
t2.PRICE, t2.PURCHASED, t2.GRADE, t2.THECONDITION, t2.IMAGE, t2.NOTES,
t2.CURRENCY, t2.STAMP_ID, t2.ALBUM_ID FROM CATALOGUENUMBERS t4, STAMPS t3,
OWNERSHIP t2, STAMPS t1, CATALOGUENUMBERS t0 WHERE (((t1.ID = t3.ID) AND
(t4.NUMBER LIKE ?)) AND (((t3.ID = t4.STAMP_ID) AND (t0.STAMP_ID = t1.ID))
AND (t2.STAMP_ID = t1.ID)))