Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <1381141327.1223873084198.JavaMail.jira@brutus>
Date: Sun, 12 Oct 2008 21:44:44 -0700 (PDT)
From: "Catalina Wei (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Resolved: (OPENJPA-744) Extra SQL on LAZY/EAGER  ManyToOne
 relation
In-Reply-To: <153284065.1223872484314.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Catalina Wei resolved OPENJPA-744.
----------------------------------

       Resolution: Fixed
    Fix Version/s: 1.3.0

fix checked in under trunk r703912

> Extra SQL on LAZY/EAGER  ManyToOne relation
> -------------------------------------------
>
>                 Key: OPENJPA-744
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-744
>             Project: OpenJPA
>          Issue Type: Sub-task
>          Components: jdbc
>    Affects Versions: 1.2.1, 1.3.0
>            Reporter: Catalina Wei
>            Assignee: Catalina Wei
>             Fix For: 1.3.0
>
>
> OPENJPA-241 has eliminated some extra SQLs for  2 entities  having a LAZY OneToMany,  and inverse EAGER ManyToOne relations.
> There are still  extra SQLs that can be avoided in the cases where  2 entities are related by  an EAGER OneToMany,  and inverse LAZY ManyToOne relations.
> Consider the following 2 entities:
>     Publisher  (1) <--->  (M)  Magazine
>     @Entity
> public class Publisher implements Serializable {
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Column(name="id")
>     private int id;
>     @OneToMany(mappedBy="idPublisher",  fetch=FetchType.EAGER)
>     private Set<Magazine> magazineCollection;   
> ...
> }
> @Entity
> public class Magazine implements Serializable {
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Column(name="id")
>     private int id;
>     @ManyToOne(fetch=FetchType.LAZY)
>     @JoinColumn(name="id_publisher")
>     private Publisher idPublisher;
> ...
> }
> Query = em.createQuery("SELECT p from Publisher p);
> Since Publisher has a OneToMany Eager relation (magazines), We generate following  SQLs 
>  (1)  SELECT t0.id, t0.name FROM Publisher t0
>  (2)  SELECT t0.id, t1.id, t1.date_published, t1.id_publisher, t1.name FROM Publisher t0 INNER JOIN Magazine t1 ON t0.id = t1.id_publisher ORDER BY t0.id ASC
> However, the following extra SQLs are also generated (making additional database trips)
>  (3) SELECT t0.name, t1.id_publisher, t1.id, t1.date_published, t1.name FROM Publisher t0 LEFT OUTER JOIN Magazine t1 ON t0.id = t1.id_publisher WHERE t0.id = ? [params=(int) 2]
> ... more depending on how many publishers there are in the database.
> The fact that SQL(2) has  already returned all magazines of all publishers, SQL (3) is unnecessary. 
> The inverse ManyToOne relation should  be established from the SQL (2) result.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.