| Fetch strategy tips [message #555623] |
Fri, 27 August 2010 08: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!
|
|
|
|
| Re: Fetch strategy tips [message #556780 is a reply to message #555623] |
Thu, 02 September 2010 11: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();
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!
|
|
|
Powered by
FUDForum. Page generated in 0.01786 seconds
] 
Search
Help
Register
Login
Home
