|
Re: Fetch Join Problem [message #520193 is a reply to message #520031] |
Thu, 11 March 2010 14:29 |
|
As I already said, the reason the JPA spec (and EclipseLink) do not allow the aliasing of join fetches, is that if you join fetch something, the same alias should not be used in the where clause, as it could result in returning corrupt objects.
i.e. in your example if the User had several groups, some of which had id=123 and another had id=456, then your join fetch query will only bring back the one with 123. This will mean the JPA provider will build and return User 'John' with only a single group 123, even though 'John' should have 2 groups, this is invalid data.
To correct this in JPA you must use a normal join in conjunction with the fetch join,
i.e.
String jpql = "select distinct u from User u ";
jpql += "join fetch u.groups join u.groups g ";
jpql += "where u.name = 'John' ";
jpql += "and g.id = 123 ";
Query query = em.createQuery(jpql);
User result = (User) query.getSingleResult();
This will return the correct result.
If you really only want the one User and one Group back, then you should just query both, without the join fetch,
i.e.
String jpql = "select u, g from User u, Group g ";
jpql += "where u.name = 'John' ";
jpql += "and g.id = 123 ";
jpql += "and g.user = u ";
Query query = em.createQuery(jpql);
User result = (User) query.getSingleResult();
There are also better way to load relationships in EclipseLink and join fetching. You can remove the join fetch, and instead use a batch fetch using the "eclipselink.batch"="u.groups" query hint. This will load all of the groups for all of the users in one query, and is more efficient than the join fetch, as no duplicate data is required.
If you are still having issue please include the JPQL and resulting SQL, and what SQL you want.
James : Wiki : Book : Blog : Twitter
|
|
|
|
|
Re: Fetch Join Problem [message #522463 is a reply to message #520525] |
Mon, 22 March 2010 17:35 |
|
The REPORT table should be joined twice, assuming there is a join twice in the where clause this is correct.
An alternative the join fetching is batch reading, which is more efficient for a 1-m or m-m. You could try the "eclipselink.batch" query hint.
James : Wiki : Book : Blog : Twitter
|
|
|
Powered by
FUDForum. Page generated in 0.03624 seconds