Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Fetch Join Problem
Fetch Join Problem [message #520031] Wed, 10 March 2010 21:43 Go to next message
Nobili  is currently offline Nobili
Messages: 6
Registered: March 2010
Junior Member
I'm trying to execute this query using EclipseLink, but I get this error: syntax error at [g].


String jpql = "select u from User u ";
jpql += "join fetch u.groups g ";
jpql += "where u.name = 'John' ";
jpql += "and g.id = 123 ";

Query query = em.createQuery(jpql);
User result = (User) query.getSingleResult();



Is it a bug or I really can't do this way?
Re: Fetch Join Problem [message #520193 is a reply to message #520031] Thu, 11 March 2010 14:29 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 #520525 is a reply to message #520193] Fri, 12 March 2010 17:58 Go to previous messageGo to next message
Nobili  is currently offline Nobili
Messages: 6
Registered: March 2010
Junior Member
James,

I understand that the JPA specification don't allow to use alias with fecth. Then I tried to execute the example below, but it generates a SQL with this "from" clause: "FROM REPORT t0, REPORT t2, SYSTEM t1", with the table REPORT duplicated. This example refers to a one-to-many relationship. In a many-to-many relationship this query results in a cartesian product.

String jpql = "select s from System s ";
jpql += "join fetch s.reports ";
jpql += "join s.reports r ";
jpql += "where s.name = 'MySystem' ";
jpql += "and r.naame = 'Developer' ";


Query assembled by Eclipselink: "... FROM REPORT t0, REPORT t2, SYSTEM t1 ..."


I would like to get all objects loaded without using fetchtype.eager on entity.

Re: Fetch Join Problem [message #521227 is a reply to message #520031] Tue, 16 March 2010 19:47 Go to previous messageGo to next message
Tom Ware is currently offline Tom Ware
Messages: 17
Registered: July 2009
Junior Member
We have discussed this a bit in the following bug:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=305499

Correct me if I am wrong, but the issue comes down to the question:

How do I write a JPA Query that fetch joins an xToMany attribute and also tests some criteria on that same attribute?

The initial issue you had was with the JPQL - the jJPA specification does not allow an alias on a fetch join. (e.g. join fetch s.reports s), so we suggested you try the JPQL you have above.

The JPQL you have above generates SQL that joins the Report table twice and you are hoping for an alternative that does not do that.

Can you suggest what the SQL you would like to see should look like? The hardest part of writing that SQL is to write it in such a way as to satisy the query criteria (r.naame = 'Developer' ) and still return the rows you are trying to fetch join.

e.g.
- Imagine you have one System with 2 Reports
- One of those reports has naame="Developer"
- The other one has naame="SomethingElse"
- How do you write SQL that will get all the rows from System and all the rows for both reports based on the query you suggest above?
Re: Fetch Join Problem [message #522463 is a reply to message #520525] Mon, 22 March 2010 17:35 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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
Previous Topic:Bug in DDL generation for Oracle
Next Topic:Migrating Toplink CMP to TopLink/EclipseLink JPA
Goto Forum:
  


Current Time: Sun Oct 26 01:21:20 GMT 2014

Powered by FUDForum. Page generated in 0.04686 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software