[
https://issues.apache.org/jira/browse/OPENJPA-241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fay Wang updated OPENJPA-241:
-----------------------------
Attachment: openjpa_241.patch
The attached patch fixes the scenario described by Rob. The following is the
test I used to reproduce the problem:
@Entity
public class Customer {
...
@OneToMany(fetch=FetchType.LAZY, mappedBy="customer")
private Collection<Order> orders = new ArrayList<Order>();
...
}
@Entity
public class Order {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
int oid;
double amount;
boolean delivered;
@ManyToOne (fetch=FetchType.EAGER)
Customer customer;
...
}
The test case:
String query = "select c FROM Customer c";
Query q = em.createQuery(query);
List list = q.getResultList();
for (int i = 0; i < list.size(); i++) {
Customer c = (Customer)list.get(i);
System.out.println("Customer = " + c);
Collection orders = c.getOrders();
for (Iterator iter=orders.iterator(); iter.hasNext();) {
Order order = (Order)iter.next();
System.out.println("order = " + order);
}
}
As Rob indicates, since the Orders field in the Customer entity is lazy, when
the Customer is first loaded, the Orders will not be loaded. After the Orders
is loaded, the extra join sql is executed to get the customer field in the
Order because it is an eager field.
The above test generates the following sql:
(1) SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street,
t0.zip
FROM PdqCustomer t0
(2) SELECT t0.name FROM PdqCustomer t0 WHERE t0.countryCode = ? AND t0.id = ?
(3) SELECT t0.oid, t0.version, t0.amount, t0.delivered
FROM PdqOrder t0 WHERE t0.CUSTOMER_COUNTRYCODE = ? AND t0.CUSTOMER_ID = ?
(4) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street,
t1.zip
FROM PdqOrder t0
INNER JOIN PdqCustomer t1
ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode AND t0.CUSTOMER_ID = t1.id
WHERE t0.oid = ? optimize for 1 row
(5) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street,
t1.zip
FROM PdqOrder t0
INNER JOIN PdqCustomer t1
ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode AND t0.CUSTOMER_ID = t1.id
WHERE t0.oid = ? optimize for 1 row
(6) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street,
t1.zip
FROM PdqOrder t0
INNER JOIN PdqCustomer t1
ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode AND t0.CUSTOMER_ID = t1.id
WHERE t0.oid = ? optimize for 1 row
(7) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street,
t1.zip
FROM PdqOrder t0
INNER JOIN PdqCustomer t1
ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode AND t0.CUSTOMER_ID = t1.id
WHERE t0.oid = ? optimize for 1 row
The purpose of sql (4) - (7) is to retrieve the inverse relationship of
Customer in the Order entity. The attached fix will detect this relationship to
get rid of these sql.
Please note that this patch included the fix in JIRA-134 scenario 3. Please
code review it. Any comments are mostly appreciated.
> Extra SQL on lazy CMR load
> --------------------------
>
> Key: OPENJPA-241
> URL: https://issues.apache.org/jira/browse/OPENJPA-241
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 1.0.0
> Environment: all
> Reporter: Rob Wisniewski
> Attachments: openjpa_241.patch
>
>
> This issue is similar to openjpa-134 but occurs in a slightly different
> manner.
> I've got two entities:
> Account (LAZY) (one) <--------> (many) (EAGER) Holding
> So when I load account nothing is loaded from holdings, but once I load the
> holding, I observe each holding separately making a database call to load
> it's eager account. This is quite alot of overhead, and through a simple
> conditional check we should be able to avoid it. Essentially if we're
> loading an entity from a CMR, and the multiplicity of the field from the
> bidirectional relationship is one, then we don't need to load the data for
> that entity. We did something similar to this in openjpa-134 when everything
> was eager (essentially avoiding the extra left out joing back into account).
> Can we avoid these separate calls?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
