users@glassfish.java.net

Re: sth doubt about using left out join under Toplink Essentials

From: Gordon Yorke <gordon.yorke_at_oracle.com>
Date: Tue, 29 Jul 2008 09:04:42 -0400

Hello Wu,
The query generated performs a JOIN from the join table to the target
table providing a set of all related objects. Then an outer join is
performed from the source table to the target table providing a set of
all persons (those with and without related objects). An outer join is
not needed from the join table to the target table because there is no
entry in the join table that does not have a corresponding row in the
target table. That is the nature of a join table. In separate queries
issue a JOIN and an OUTER JOIN between the join table and the target
table and see that the same rows are always returned.

If you actually have a join table that can include nulls from a legacy
implementation (very rare) then you will need to map this relationship
through an association class with a OneToMany and a OneToOne mapping as
this sort of structure is not supported by the ManyToManyMapping.
--Gordon

Wu Jie wrote:
> Hi Peter
> Hi GF) Dev Teams,
> Hi Everyone,
>
> I have some doubt about using "left out join" under TopLink Essentials(TLE for short).
>
> 1. background of doubt
> when I do some test about using "left out join" for the entity which has
> m:m or 1:m relationship,I am eager to know that how TLE parses the m:m or
> 1:m relationship.
> the test what I did as follows: (Database is DB2)
>
> ========================================================================
> EntityManagerFactory emf = Persistence.createEntityManagerFactory("testsqlPU"); (1)
> EntityManager em = emf.createEntityManager(); (2)
> Query query = em.createQuery("SELECT o from Person o LEFT OUTER JOIN o.persons");(3)
> List<Person> list = (List<Person>) query.getResultList() ; (4)
> ========================================================================
> The JPQL in line 3 is parsed into the following SQL in runtime:
>
> "SELECT t0.ID, t0.NAME, t1.ID, t1.NAME FROM {oj PERSON t0 LEFT OUTER JOIN (PERSON_PERSON t2 JOIN PERSON t1 ON (t1.ID = t2.persons_ID)) ON (t2.Person_ID = t0.ID)}"
>
> 2. my research and doubt
> How TLE parses the JPQL into the above SQL?
>
> I tracked into the source code of TLE and located at SQLSelectStatement Class.
> (oracle.toplink.essentials.internal.expressions.SQLSelectStatement)
> the method appendFromClauseForOuterJoin() of SQLSelectStatement does the parse.
>
> I cite the comment of line 365-368 of SQLSelectStatement below.
> ========================================================================
> // Must outerjoin each of the targets tables.
> // The first table is joined with the mapping join criteria,
> // the rest of the tables are joined with the additional join criteria.
> // For example: EMPLOYEE t1 LEFT OUTER JOIN (PROJ_EMP t3 LEFT OUTER JOIN PROJECT t0 ON (t0.PROJ_ID = t3.PROJ_ID)) ON (t3.EMP_ID = t1.EMP_ID)
> ========================================================================
>
> We can learn that we should use the "LEFT OUTER JOIN" for parsing
> m:m or 1:m relationship from the comment, however in fact TLE uses "JOIN"
> in line 390 of SQLSelectStatement.
>
> So I have two questions.
> 1. The meaning of using "JOIN" instead of "LEFT OUTER JOIN".
> 2. If using "LEFT OUTER JOIN",is there any problem?
>
> Could you please help me to give some suggestion and if possible,
> resolve the above 2 questions and supply some useful material.
>
> Appreciate Ahead!
>
> Thanks.
> Wu.
>
>