Re: [eclipselink-users] Join Table with additional columns: One SQL Sele

["Joe Mc." <m_joe_c@xxxxxxxxxxx>]

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




James Sutherland wrote:
> 
> 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
>> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Join-Table-with-additional-columns%3A-One-SQL-Select-to-fetch-data--tp24304704p24321401.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.