You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2008/10/13 06:44:44 UTC
[jira] Resolved: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
[ 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.