Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Fetch strategy tips
Fetch strategy tips [message #555623] Fri, 27 August 2010 08:17 Go to next message
Matti Hansson is currently offline Matti Hansson
Messages: 68
Registered: July 2009
Member
Hi!
I'm fairly new to EclipseLink and JPA and I'm wondering if anyone would be kind enough to help me with a query I'd like to make.

I have a three-tiered bidirectional OneToMany data model, for example one BigBoss rules over many Boss, which in turn rule over many Employee.

I want to have a find-by-name query that searches all three tiers, fetches the relations of the finds and returns a list of BigBoss.

When I say fetch the relations I mean if a BigBoss is found, fetch all its Boss and all their Employee, while if an Employee is found, fetch its Boss and its BigBoss.

This might be an answer to a search for 'Eric':
List<BigBoss>
  +BigBoss 3: Eric <- Hit!
  |  |
  |  +Boss 3-1: Tony
  |  |  +Emp 3-1-1: Rosa
  |  |  +Emp 3-1-2: Annie
  |  |
  |  +Boss 3-2: Jerry
  |     +Emp 3-2-1: Eric <- Hit
  |     +Emp 3-2-2: Walter
  |
  +BigBoss 7: Marie
     +Boss 7-4: Tony
        +Emp 7-4-5: Eric <- Hit!


The way I see this done is through the following steps:

1) Query the BigBosses.
2) Loop through the results and fetch the Bosses.
2b) Loop through the Bosses and fetch the Employees.
3) Query the Bosses.
4) Loop through the results and fetch the BigBosses and the Employees.
5) Query the Employees.
6) Loop through the results and fetch the Bosses and their BigBosses.
7) Merge the results of the three queries into a single list of BigBosses, while making sure the list contains no duplicate entries.

I just feel this could be made a whole lot more efficient with the database and/or EclipseLink doing more of the work. Any insights would be much appreciated.
Thanks!
Re: Fetch strategy tips [message #556071 is a reply to message #555623] Mon, 30 August 2010 13:31 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Not sure I understand.

If you are only returning BigBoss why do you need to query Boss and Employee instead of just BigBoss?

Perhaps include your query code.

In terms on optimally loading of the objects you can use join fetching, or batch fetching (batch fetching would be more efficient for a 1-m). There is a query hint "eclipselink.batch".

If you just what to force the data to be fetched (for serialization), then you may consider the new LoadPolicy in EclipseLink 2.1.


James : Wiki : Book : Blog : Twitter
Re: Fetch strategy tips [message #556780 is a reply to message #555623] Thu, 02 September 2010 11:19 Go to previous message
Matti Hansson is currently offline Matti Hansson
Messages: 68
Registered: July 2009
Member
Thank you for your reply and sorry for not being clear enough. It's kind of tough to explain.

The reason all three tiers are queried is that, in the usecase, the user is searching for a person using a name. In real life every type is a person, but in the model they do not inherit from a common Person class.

The answer is a list of BigBosses because it is they who are at the top of the hierarchy. The response does not only contain BigBosses, but the entire tree structure (with just the relevant nodes).

The relevant nodes are: for each person that has a matching name, get all its children and grandchildren, get its parent and grandparent, but do not get its siblings or its parent's siblings.

I'll try to explain using the example answer in the first post. "BigBoss 7: Marie" might actually have ten bosses, but after the query is run like so:
List<BigBoss> bigBosses = searchForPerson("Eric"); // bigBosses = {BB3:Eric,  BB7:Marie}
BigBoss bigBossMarie = bigBosses.get(1);
List<Boss> myListOfBosses = bigBossMarie.getBosses();
myListOfBosses should only contain one element ("Tony") because that is the only node that is relevant to the search ("Eric"). On the other hand, if the search was for "Marie", myListOfBosses should contain all ten child nodes.

I can't find any information on the difference between join and batch fetching, but using one of those should let me skip steps 2, 4 and 6. Will try it out.
More comments and tips are welcome!
Previous Topic:SQLServer pagination
Next Topic:Exception during meta-model generation in maven
Goto Forum:
  


Current Time: Wed Jul 23 09:58:59 EDT 2014

Powered by FUDForum. Page generated in 0.14021 seconds