Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » CriteriaQuery with ManyToMany eager join fetch
CriteriaQuery with ManyToMany eager join fetch [message #654775] Thu, 17 February 2011 04:52 Go to next message
Fericit Bostan is currently offline Fericit Bostan
Messages: 68
Registered: June 2010
Member
I'm using EclipseLink 2.1 and have a ManyToMany association between two of my entities. I'm using a CriteriaQuery to retrieve the result and I want to eagerly fetch the Collection of the Child entities rather than having a separate SQL statement executed for each parent in the list retrieved. Currently the query, in the database, retrieves 811 records, but because I am attempting to fetch all the child records of the ManyToMany this explodes to over 3500 records.

My parent record is a Student that can contain N number of Language records.

@Entity
@Table(name="Student)
public class Student implements Serializable {
@JoinTable(name = "StudentLanguage",
joinColumns = { @JoinColumn(name = "StudentId", referencedColumnName = "ID") },
inverseJoinColumns = { @JoinColumn(name = "StudentId", referencedColumnName = "ID")})
@ManyToMany(fetch = FetchType.EAGER)
private java.util.List<Language> languages;



When I use the CriteriaQuery I am attempting to fetch the Languages with the Student in a single SQL statement in the following manner.


CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Student> criteria = builder.createQuery( Student.class );
Root<Student> studentRoot = criteria.from( Student.class );

// Eagerly fetch the Languages
studentRoot.fetch(Student_.languages, JoinType.LEFT);
criteria.select( studentRoot );


I know that I am doing something incorrect but I am having difficulty finding the correct syntax to get the records grouped together before being returned.

Thanks for the help...


Re: CriteriaQuery with ManyToMany eager join fetch [message #654921 is a reply to message #654775] Thu, 17 February 2011 15:52 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Using fetch is correct. What error are you getting, what SQL is generated?

You may also consider using batch fetching.

http://java-persistence-performance.blogspot.com/2010/08/bat ch-fetching-optimizing-object-graph.html


James : Wiki : Book : Blog : Twitter
Re: CriteriaQuery with ManyToMany eager join fetch [message #654930 is a reply to message #654921] Thu, 17 February 2011 16:33 Go to previous messageGo to next message
Fericit Bostan is currently offline Fericit Bostan
Messages: 68
Registered: June 2010
Member
I'm not getting an error and the SQL generated is correct. The issue is that each top level entity, in this case Student, is returned multiple times, based upon how many languages the student has. If I perform a SELECT for a single entity, based upon its primary key, and the student has 4 languages, then rather than the single student being returned with 4 languages I get 4 student entities (duplicates) returned, each having 4 languages.

Thanks for the assistance.
Re: CriteriaQuery with ManyToMany eager join fetch [message #654950 is a reply to message #654930] Thu, 17 February 2011 17:53 Go to previous messageGo to next message
Fericit Bostan is currently offline Fericit Bostan
Messages: 68
Registered: June 2010
Member
I removed the Join Fetch in exchange for @BatchFetch(BatchFetchType.EXISTS), based upon the link you provided me, and it resolved my issue. I'll need to experiment with @BatchFetch(BatchFetchType.JOIN) & @BatchFetch(BatchFetchType.IN) to see the performance difference based upon the amount of data returned, but this seems to have been a very simple solution to my problem.

Thanks for the help...
Re: CriteriaQuery with ManyToMany eager join fetch [message #656845 is a reply to message #654930] Mon, 28 February 2011 18:53 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You need to add a "select distinct" to your JPQL query to remove the duplicates. The JPA spec actually requires the duplicates to be there by default for some odd reason...


James : Wiki : Book : Blog : Twitter
Re: CriteriaQuery with ManyToMany eager join fetch [message #656853 is a reply to message #656845] Mon, 28 February 2011 19:14 Go to previous message
Fericit Bostan is currently offline Fericit Bostan
Messages: 68
Registered: June 2010
Member
How odd. I was going to try SELECT DISTINCT but thought that I really shouldn't need to add it.

For now, I'm using the @BatchFetch(BatchFetchType.JOIN) which seems to work extremely well. For the amount of data that I am returning there didn't seem to be any significant performance difference between @BatchFetch(BatchFetchType.JOIN) and @BatchFetch(BatchFetchType.EXISTS), so I stuck with the JOIN. But I guess it would be worth while to go back and experiment with the SELECT DISTINCT.

Thanks for the info...
Previous Topic:CacheKey getMutex() is not thread-safe
Next Topic:Expression and ws qurey builder
Goto Forum:
  


Current Time: Sun Oct 26 09:42:23 GMT 2014

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

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