[
https://issues.apache.org/jira/browse/OPENJPA-292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fay Wang updated OPENJPA-292:
-----------------------------
Attachment: openjpa_292.patch
The problem described by Rob is similar to JIRA-241 and JIRA-134 scenario 3 in
that they are all about the retrieval of the inverse relationship. This JIRA
deals with 1-1 relationship while JIRA-241 is for 1-many relationship. The
following test case is used to reproduce the problem:
@Entity
public class EntityC {
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Id private int id;
private String name;
private int age;
private int balance;
@OneToOne(fetch=FetchType.EAGER, mappedBy="entityC")
private EntityD entityD = null;
...
}
@Entity
public class EntityD {
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Id private int id;
private String name;
private int loginCount;
private int logoutCount;
private String email;
@OneToOne(fetch=FetchType.EAGER)
private EntityC entityC = null;
...
}
The test case:
EntityManagerImpl em = (EntityManagerImpl) emf.createEntityManager();
String query = "select c FROM EntityC c";
Query q = em.createQuery(query);
List list = q.getResultList();
System.out.println("size = " + list.size());
for (int i = 0; i < list.size(); i++) {
EntityC c = (EntityC)list.get(i);
System.out.println("EntityC = " + c.getName());
System.out.println("EntityD = " + c.getD().getName());
System.out.println("EntityD's C = " + c.getD().getC().getName());
System.out.println();
}
The following SQL are generated:
(1) SELECT t0.id, t0.age, t0.balance, t1.id, t1.email, t1.loginCount,
t1.logoutCount, t1.name, t0.name
FROM EntityC t0
LEFT OUTER JOIN EntityD t1 ON t0.id = t1.ENTITYC_ID
(2) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount,
t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ?
optimize for 1 row [params=(int) 101]
(3) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount,
t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ?
optimize for 1 row [params=(int) 102]
(4) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount,
t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ?
optimize for 1 row [params=(int) 104]
(5) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount,
t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ?
optimize for 1 row [params=(int) 103]
Since there are four entityD in the database, openjpa makes four separate SQL
calls to retrieve each entityD (sql (2) - (5)). These sql has inner join and
left outer join between EntityC and EntityD as they are bi-directional 1-1
relationship.
The attached patch detects this inverse relationship (mappedBy relationship) to
get rid of sql (2) -(5).
> Extra JOIN on eager bi-directional relationship
> -----------------------------------------------
>
> Key: OPENJPA-292
> URL: https://issues.apache.org/jira/browse/OPENJPA-292
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.0.0
> Reporter: Rob Wisniewski
> Attachments: openjpa_292.patch
>
>
> I have a pretty simple 1-1 bi-directional relationship. If I set both sides
> to eager and then do a select on one side, the following SQL is executed:
> SELECT t1.USERID, t2.ACCOUNTID, t2.BALANCE
> , t2.CREATIONDATE, t2.LASTLOGIN, t2.LOGINCOUNT, t2.LOGOUTCOUNT,
> t2.OPENBALANCE,
> t1.ADDRESS, t1.CREDITCARD, t1.EMAIL, t1.FULLNAME, t1.PASSWD FROM ACCOUNTEJB
> t0 I
> NNER JOIN ACCOUNTPROFILEEJB t1 ON t0.PROFILE_USERID = t1.USERID LEFT OUTER
> JOIN
> ACCOUNTEJB t2 ON t1.USERID = t2.PROFILE_USERID WHERE t0.ACCOUNTID = ?
> optimize
> for 1 row
> the relationship is account <-> accountprofile. you can see we actually do
> 2 joins.
> This is one in a family of problems which was supposed to have been solved in
> https://issues.apache.org/jira/browse/OPENJPA-134
> There is also a related issue where an uneeded load is done, which I
> documented a while ago in https://issues.apache.org/jira/browse/OPENJPA-241
> I think this is a general comment on the lack of sophistication of the
> persistence engine to understand when data is logically going to be there
> anyways, and not to add joins or trigger data loading.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
