| CriteriaQuery with ManyToMany eager join fetch [message #654775] |
Wed, 16 February 2011 23:52  |
Eclipse User |
|
|
|
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 #656853 is a reply to message #656845] |
Mon, 28 February 2011 14:14  |
| Eclipse User |
|
|
|
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...
|
|
|
Powered by
FUDForum. Page generated in 0.44150 seconds
Home