Tuesday, August 26, 2008

JPA and Optional Associations

I was writing some unit tests to test a few of my queries and noticed they were failing to return results (even though the resultant rows were clearly in the database). Looking into my changes, the only differences was that I recently added the EclipseLink eclipselink.join-fetch for two one-to-many relationships. From looking at the resultant SQL, it became clear what the problem was. By adding these join-fetch statements, my query became more efficient (since I didn't have subsequent row-by-row lazy fetches later), but it also became invalid in some circumstances. In particular, the fetch join added some AND statements to the WHERE clause whereby the foreign key id was equal to the primary table id. However if the many-side relationship is empty, then this statement will not return any rows. I think the way around this would be to box the AND statement in a compound OR statement with an exists condition. Currently to my knowledge the JPA implementations do not support this, and I am going to research whether or not this is achievable by manually modifying the join-fetch statement.

No comments: