Eclipse Community Forums: EclipseLink » CriteriaQuery with ManyToMany eager join fetch

Help

Home

Home » Eclipse Projects » EclipseLink » CriteriaQuery with ManyToMany eager join fetch

Show: Today's Messages :: Show Polls :: Message Navigator

CriteriaQuery with ManyToMany eager join fetch [message #654775]

Thu, 17 February 2011 04:52

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...

Report message to a moderator

Re: CriteriaQuery with ManyToMany eager join fetch [message #654921 is a reply to message #654775]

Thu, 17 February 2011 15:52

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

Report message to a moderator

Re: CriteriaQuery with ManyToMany eager join fetch [message #654930 is a reply to message #654921]

Thu, 17 February 2011 16:33

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.

Report message to a moderator

Re: CriteriaQuery with ManyToMany eager join fetch [message #654950 is a reply to message #654930]

Thu, 17 February 2011 17:53

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...

Report message to a moderator

Re: CriteriaQuery with ManyToMany eager join fetch [message #656845 is a reply to message #654930]

Mon, 28 February 2011 18:53

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

Report message to a moderator

Re: CriteriaQuery with ManyToMany eager join fetch [message #656853 is a reply to message #656845]

Mon, 28 February 2011 19:14

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...

Report message to a moderator

Previous Topic:	CacheKey getMutex() is not thread-safe
Next Topic:	Expression and ws qurey builder

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 01:51:15 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter