I have something like this working. Here's roughly what I'm doing:
- Create a single select using joins across the tables.
- Make sure that each of the selected columns has a unique alias, e.g.
"category_name"
- Test the query in a SQL tool to make sure you get what you want. You
should have multiple rows for each user.
- Create result maps for User, Group and Category.
- Link the Group and Category result maps into the User result map:
<result property="groups" column="user_id" resultMap="userGroups" />
- Each of the result maps should have the groupBy attribute set to its
own primary key attribute:
class User { private int userId;}
class Group { private int groupId;}
class Category {private int catId;}
<resultMap id="user" class="User" groupBy="userId">...</resultMap>
<resultMap id="group" class="Group" groupBy="groupId">...</resultMap>
<resultMap id="category" class="Category"
groupBy="catId">...</resultMap>
- For testing, start with only one relationship, e.g. Group, leave out
Category. Once this works, add Category again.
The example in the Wiki is too simple IMHO, we should try to add a more
complex one...
Nils
On Thu, 02 Feb 2006 21:10:50 +1100, "Zoran Avtarovski"
<[EMAIL PROTECTED]> said:
> I've been trying to get a n+1 example to work without much luck and I
> think
> that I may have misunderstood the wiki article. I have intentionally kept
> the example simple so I can fully understand the concepts and then apply
> it
> to the real world app which uses the same principles.
>
> I have three object tables
> User, which has {user_id, name, type} columns
> Group, which has {group_id, name, description} columns
> Category, which has {cat_id, name, description} columns
>
> I also have two relationship tables
> UserGroup, which has {user_id, group_id} columns
> UserCat, which has {user_id, group_id} columns
>
> Each User can have multiple Groups and Categories. This is represented
> through a User POJO which includes two Collection properties {groups,
> categories} with appropriate setters and getters.
>
> I currently use multiple SQL calls to populate the n+1 properties, I use
> a
> <result property="groups" column="user_id" select="getUserGroups" /> and
> the same for categories. You get the Idea. Ideally, I'd really like to
> use a
> single SQL query to improve system performance.
>
> Can I resolve multiple n+1 relationships in a single call and if so how.
> I'd
> appreciate any help. I'm sure that once I get my head around the concept
> of
> n+1 selects it will be easy.
>
> Zoran
>
>
--
==================================
[EMAIL PROTECTED]
