Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » Fetch strategy tips

Show: Today's Messages :: Show Polls :: Message Navigator

Fetch strategy tips [message #555623]

Fri, 27 August 2010 12:17

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!

Report message to a moderator

Re: Fetch strategy tips [message #556071 is a reply to message #555623]

Mon, 30 August 2010 17:31

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.

Report message to a moderator

Re: Fetch strategy tips [message #556780 is a reply to message #555623]

Thu, 02 September 2010 15:19

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!

Report message to a moderator

Previous Topic:	SQLServer pagination
Next Topic:	Exception during meta-model generation in maven

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 26 22:35:18 GMT 2016

.:: Contact :: Home ::.