ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Qian" <daqiqi...@aol.com>
Subject Re: Multiple n+1
Date Fri, 03 Feb 2006 14:10:21 GMT
Nils,

Have you tried groupBy by more than one key(groupBy="key1, key2)?

If you see my previous post. During the test, I turned ibatis and sql 
log to debug level. I saw the queried data in result set. Somehow, 
groupBy cannot pick it up and always set the list to null.

thx,
Tony

Nils Winkler wrote on 2/3/2006, 2:51 AM:

 > 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"
 > <zoran@sparecreative.com> 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
 > >
 > >
 > --
 > ==================================
 > nils@nilswinkler.com
 >



Mime
View raw message