openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Simons <michael.sim...@optitool.de>
Subject Re: How work around OpenJPA-1365
Date Thu, 19 Nov 2009 09:46:08 GMT
Hello Mike,

My Model:
Product(1:*)LocalizedProduct
Product(1:*)ProductDisplayOption
ProductDisplayOption extends DisplayOption

I've setup a FetchPlan with this:
@FetchGroups({
	@FetchGroup(name="productDetails", attributes={
					@FetchAttribute(name="locals"),
					@FetchAttribute(name="displayOptions")
	})
})
public class Product implements java.io.Serializable, SingleLocalized, Customizable {
  /** Localizations */
  @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="product")
  private Set<LocalizedProduct> locals = new HashSet<LocalizedProduct> (13);

  @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="product")
  private Set<ProductDisplayOption> displayOptions = new HashSet<ProductDisplayOption>
(113);
  ...
}

When running the following code:
  private void retrieveProducts (ISentinel cache) {
    Query query = em.createQuery ("SELECT p from Product p");
    OpenJPAQuery oq = OpenJPAPersistence.cast (query);
    oq.getFetchPlan ().setMaxFetchDepth (10).addFetchGroup ("productDetails");
    for (Object obj : query.getResultList ()) {
      ...
    }
  }


Unfortunately the produced SQL looks like this:
SELECT t0.product_id, t0.jdo_version, t0.alt_ex_id, t0.density, t0.ext_id, t0.indefinite FROM
product t0

SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version, t1.display_color, ...
FROM product_display_option t0 INNER JOIN display_option t1 ON t0.display_option_id =
t1.display_option_id
WHERE t0.product_id = ?

SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0 WHERE t0.product_id = ?

SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version, t1.display_color, ...
FROM product_display_option t0 INNER JOIN display_option t1 ON t0.display_option_id =
t1.display_option_id
WHERE t0.product_id = ?

SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0 WHERE t0.product_id = ?

... // the second and third statement are repeated for every record in the product table.

What I would expect (and got with the formerly used JDO Impl) is like this:
SELECT ...
FROM product t0
LEFT JOIN product_display_option t1 on t1.product_id = t0.product_id
JOIN display_option t2 on t2.display_option_id = t1.display_option_id;
SELECT ...
FROM product t0
LEFT JOIN localized_product t1 on t1.product_id = t0.product_id;

---
So how do I convince OpenJPA to join the tables when using fetchplan?

When I run the following code instead without a fetchplan:
  private void retrieveProducts (ISentinel cache) {
    Query query = em.createQuery (
      "SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH p.locals");
    for (Object obj : query.getResultList ()) {
      ...
    }
  }

The produced SQL looks like (what's more like what I would have expected, but still produces
too
much "single selects"):
SELECT ...
FROM product t0
INNER JOIN product_display_option t1 ON t0.product_id = t1.product_id
INNER JOIN display_option t2 ON t1.display_option_id = t2.display_option_id

SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0
WHERE t0.product_id = ?
... // The last statement again is executed for every record in the product table.

How can I reduce the number of statements generated?

kind regards,
Michael

Michael Dick schrieb:
> Hi Michael,
> 
> Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to take
> a look at that. It probably happens if the query does not fetch records from
> the database. If that's what you're seeing then I just need to update the
> JIRA with that info.
> 
> I'm not terribly familiar with JDO. I was under the impression that
> OpenJPA's FetchPlans were identical (or nearly identical) to those in JDO.
> 
> To use my example (Employee and Manager) you would do something like this :
>     OpenJPAEntityManager em = getEntityManager();
> 
>     OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
>     q.getFetchPlan().addField(Manager.class, "employees");
> 
>     List<Manager> managers = q.getResultList();
> 
> There's a lot more in the manual about it, I'd start looking at [1].
> 
> [1]
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
> 
> Hope this helps,
> -mike
> 
> 
> On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <michael.simons@optitool.de
>> wrote:
> 
>> Hi Mike,
>>
>> Do you mean fetchplan like described in the manual in chapter 1.7.4?
>> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
>> there a description, how
>> to do that?
>>
>> kind regards,
>> Michael
>>
>> Michael Dick schrieb:
>>> Hi all,
>>>
>>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
>> correct
>>> behavior. If you don't want the duplicates to be returned you should use
>> the
>>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>>>
>>> There are several outstanding issues though (which I'm working on):
>>>
>>> 1.) OPENJPA-894:  When results are returned from the database OpenJPA
>>> automatically removes duplicates from the list. If the results are
>> fetched
>>> from memory the duplicates reappear.
>>>
>>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
>>> keyword doesn't work. This is because OpenJPA merely prepends the
>> DISTINCT
>>> keyword to the SQL generated which doesn't work if you're selecting
>> across
>>> multiple tables. Instead we need to filter the result list after
>> retrieving
>>> from the database.
>>>
>>> There are two proposed fixes for OPENJPA-894 each of which have some
>>> drawbacks.
>>>
>>> 3a.) Mike's fix : supports pagination but does not support multiple JOIN
>>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
>>> FETCH m.projects returns the wrong number of results).
>>>
>>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
>>> support pagination (ie query.setMaxResults(), query.setFirstResult()
>> doesn't
>>> page forward as expected).
>>>
>>> So there is work being done, but it's turned out to be a very ticklish
>> issue
>>> to solve.
>>>
>>> At the risk of muddying the waters a bit if you're migrating a JDO
>>> application have you considered using OpenJPA's FetchPlan implementation
>> to
>>> eagerly load some fields? Over medium - large datasets I've found this to
>> be
>>> significantly faster than using a JOIN FETCH, but YMMV.
>>>
>>> Hope this helps,
>>>
>>> -mike
>>>
>>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <daryl@6degrees.com>
>> wrote:
>>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>>>> <michael.simons@optitool.de>wrote:
>>>>
>>>>
>>>>> You state, that you're query with distinct and join fetch does work
>>>>> properly. But this would
>>>>> mean OpenJPA-1365 doesn't occur, does it?
>>>>>
>>>> The JIRA states:
>>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>>>
>>>> So 1365 does not occur unless you've patched your code such that 894 is
>>>> fixed. What version of OpenJPA are you using and do you have any patches
>> in
>>>> place?
>>>>
>>>>
>>>>> When we call "select a from A a join fetch B" we get n instances of A,
>>>> with
>>>>> n = numbers of
>>>>> A-B-associations.
>>>>>
>>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
>>>> distinct rows of A which sounds like what you (and I) want but is
>> improper.
>>>> 894 shows that a second run of the query in the same EntityManager
>> yields
>>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>>>>
>>>> select distinct a from A a join fetch a.bs
>>>> ?
>>>>
>>>> How about these two:
>>>> select a from A a left join fetch a.bs
>>>> select distinct a from A a left join fetch a.bs
>>>>
>>>> I don't want to give the impression that I'm an expert on the matter,
>> just
>>>> that I've dealt with this issue and I want to be sure my understanding
>> of
>>>> things is accurate.
>>>>
>>>> --
>>>> Daryl Stultz
>>>> _____________________________________
>>>> 6 Degrees Software and Consulting, Inc.
>>>> http://www.6degrees.com
>>>> mailto:daryl@6degrees.com


Mime
View raw message