Join Table with additional columns: One SQL Select to fetch data?

[Joe Mc.]

Hi,

I am playing with additional columns in join tables.
The following example:
Customer visits some Courses
This n-m-Relation contains additional attributes in the n-m-table (join table), for example: note, year etc.

One solution is to use @OneToMany and @ManyToOne with a concret join table in Java, insteed of @ManyToMany.
In this Association class, is it possible to hold the additional columns
This solution is fine and worked.

In @ManyToMany is it possible to tell EclipseLink, that he use only ONE Select statement, to get all the data from the different tables (with outer joins etc.).
Is it possible to do this now with the OneToMany and ManyToOne?

At the moment I get a join of Customer and Courses. And EclipseLink fires for every Course, the customer holds, an additional SQL SELECT statement to get the data :-(

I general: I want the same SQL SELECT statement as in the ManyToMany case. Is it possible?


Thanks for any advice!

Regards

M.Joe

[James Sutherland]

You can use join fetching or batch reading for this, either at the query level, or the mapping level.

For mapping level, you can use the EclipseLink @JoinFetch annotation or xml to always join the relationship from Courses to Course.  You could also use a DescriptorCustomizer to set enable batch reading on the OneToOneMapping for course.

Joe Mc. wrote:

Hi,

I am playing with additional columns in join tables.
The following example:
Customer visits some Courses
This n-m-Relation contains additional attributes in the n-m-table (join table), for example: note, year etc.

One solution is to use @OneToMany and @ManyToOne with a concret join table in Java, insteed of @ManyToMany.
In this Association class, is it possible to hold the additional columns
This solution is fine and worked.

In @ManyToMany is it possible to tell EclipseLink, that he use only ONE Select statement, to get all the data from the different tables (with outer joins etc.).
Is it possible to do this now with the OneToMany and ManyToOne?

At the moment I get a join of Customer and Courses. And EclipseLink fires for every Course, the customer holds, an additional SQL SELECT statement to get the data :-(

I general: I want the same SQL SELECT statement as in the ManyToMany case. Is it possible?


Thanks for any advice!

Regards

M.Joe

[Joe Mc.]

Hi,

yes I am using @JoinFetch.
Here are some Code

@Entity
class Customer {
   
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "customers")
    @JoinFetch(JoinFetchType.OUTER)
    private List<MapCustomerCourse> mapCourses;
}

@Entity
class Course {
   @Id
   private Integer id;
}

@Entity
class MapCustomerCourse {

   @ManyToOne(fetch = FetchType.EAGER)
   @JoinColumn(name = "customerId")
   @JoinFetch(JoinFetchType.OUTER)
   private Customer customer;

   @ManyToOne(fetch = FetchType.EAGER)
   @JoinColumn(name = "courseId")
   @JoinFetch(JoinFetchType.OUTER)
   private Course course;
}


SQL Select:
Query q = entityManager.createQuery("SELECT c FROM Customer c WHERE (c.id = 1) OR (c.id = 2)");
q.setHint(QueryHints.READ_ONLY, HintValues.TRUE);
q.getResultList();

Resulting SQL:
SELECT t1...., t0...
FROM Customer t1 LEFT OUTER JOIN Map_Customer_Course t0 ON (t0.customerId = t1.ID)
WHERE ((t1.ID = ?) OR (t1.ID = ?))

I missing here the Join with the Course-Table and the resulting Course.* data.
EclipseLink fires now for every resulting Course-ID a SQL query.


I get a better solution with the following query:
SQL Select:
Query q = entityManager.createQuery("SELECT m FROM MapCustomerCourse m WHERE (m.customerId = 1) OR (m.customerId = 2)");
q.setHint(QueryHints.READ_ONLY, HintValues.TRUE);
q.getResultList();

Resulting SQL:
SELECT t1..., t0..., t2...
FROM Map_Customer_Course t1 LEFT OUTER JOIN Customer t0 ON (t0.ID = t1.customerId) LEFT OUTER JOIN Course t2 ON (t2.ID = t1.courseId) WHERE ((t1.customerId = ?) OR (t1.customerId = ?))

Ok, here I get the Join with Course-Table and the data.
Unfortunately, I get a list of Map-Java-Objects...I have to iterate through this list to get the resulting Customers etc...unaesthetic.

I would do the select from the Customer-table and fetch all the necessary data with one SQL Select.

Maybe I set the JoinFetch Annotations wrong?

Thanks in advance!

M.Joe




You can use join fetching or batch reading for this, either at the query level, or the mapping level.

For mapping level, you can use the EclipseLink @JoinFetch annotation or xml to always join the relationship from Courses to Course.  You could also use a DescriptorCustomizer to set enable batch reading on the OneToOneMapping for course.

Joe Mc. wrote:

Hi,

I am playing with additional columns in join tables.
The following example:
Customer visits some Courses
This n-m-Relation contains additional attributes in the n-m-table (join table), for example: note, year etc.

One solution is to use @OneToMany and @ManyToOne with a concret join table in Java, insteed of @ManyToMany.
In this Association class, is it possible to hold the additional columns
This solution is fine and worked.

In @ManyToMany is it possible to tell EclipseLink, that he use only ONE Select statement, to get all the data from the different tables (with outer joins etc.).
Is it possible to do this now with the OneToMany and ManyToOne?

At the moment I get a join of Customer and Courses. And EclipseLink fires for every Course, the customer holds, an additional SQL SELECT statement to get the data :-(

I general: I want the same SQL SELECT statement as in the ManyToMany case. Is it possible?


Thanks for any advice!

Regards

M.Joe

[Joe Mc.]

Hi,

thanks again J.Sutherland!
I study the chapters about Join Fetch or Batch reading (e.g:
http://wiki.eclipse.org/Optimizing_the_EclipseLink_Application_(ELUG)#How_to_Use_Batch_and_Join_Reading_for_Optimization etc)

and for example with
q.setHint(QueryHints.LEFT_FETCH, "c.mapCourses");
q.setHint(QueryHints.LEFT_FETCH, "c.mapCourses.course");
and removing the @JoinFetch at the properties I get the wanted result.

I playing now with LEFT_FETCH and BATCH to get the perfect (performance optimal) queries.

Thanks!

McJoe