Hello, The SqlMap PDF references a queryForList method in the SqlMapClient that takes a RowHandler and I think that's what you're referencing as well, but I don't see it in the code. Plus the docs say that the RowHandler interface gets passed a List along with an Object, but the RowHandler.java I see in the code doesn't have a List parameter. I'm looking both in the 2.1.7 release and a two day old co from svn.
So what's the right way to get object back from a RowHandler? Something like an anonymous inner class with a reference to an object in the calling code? Cheers, Chris On Thu, 2006-05-11 at 13:15 -1000, Larry Meadors wrote: > Another potential solution would be to use a row handler - more code, > but just one sql call. > > You'd do the big join, then as you walk through the rows, look at the > keys and add them to the lists yourself. > > Larry > > > On 5/11/06, Jeff Butler <[EMAIL PROTECTED]> wrote: > > > > It's not really a stupid question. The problem is that adding the Dog table > > to the join list will, in effect, create a cross join between dog and cat - > > causing lots of data to be repeated as you've seen. > > > > There's not a great solution that I can think of. One solution would be to > > use the iBATIS group by solution and a join for one of the lists (cats) - as > > you've already accomplished. For the other list (dogs), you can populate it > > with a second query sort of like this: > > > > <resultMap id="personMap" class="foo.bar.Person" groupBy="personID"> > > <result property="personID" column="personID"/> > > <result property="personName" column="personName" /> > > <result property="cats" resultMap="Persons.catsMap"/> > > <result property="dogs" column="personId" select="getDogsByPersonId"/> > > </resultMap> > > > > I haven't tried this for real, but I think it will work. This is still an > > N+1 query, but at least it's not 2N+1! > > > > Another thought is that you could write your own List implementation that > > would not allow duplicates. Then it could all be done in one query because > > you would catch and throw out the duplicates in Java code. As I think about > > it, I might like this solution better. There's still a bunch of duplicate > > data coming back from the DB, but there's only on DB call. > > > > > > Jeff Butler > > > > > > > > On 5/11/06, Rick Reumann <[EMAIL PROTECTED]> wrote: > > > I let my stupid question sit for a few days so now... Bueller, > > > Bueller... anyone, anyone ... :) > > > > > > > > > On 5/9/06, Rick Reumann <[EMAIL PROTECTED]> wrote: > > > > I can manage the n+1 stuff using groupBy when I'm populating one List > > > > per object per level. Where I'm running into trouble is I want to have > > > > multiple Lists populated per object. An example will hopefully help > > > > clarify: > > > > > > > > Table PersonDog > > > > --------------- > > > > personID > > > > dogID > > > > dogName > > > > > > > > > > > > Table PersonCat > > > > --------------- > > > > personID > > > > catID > > > > catName > > > > > > > > > > > > Table Person > > > > ------------ > > > > personID > > > > personName > > > > > > > > =================================== > > > > > > > > Person Object > > > > ------------- > > > > List cats; > > > > List dogs; > > > > int personID; > > > > String personName; > > > > > > > > (Cat class and Dog class as well) > > > > > > > > =================================== > > > > > > > > Now what I want back in one iBATIS query is a way to build a > > > > List of Person objects with the Lists of Cats and Dogs populated > > > > per person (assuming they have cats or dogs since either can be > > > > null). > > > > > > > > I'm not sure if it's a certain orderby in the sql I need to do or > > > > something I have to do with my iBATIS result maps to get > > > > both lists populated correctly. > > > > I CAN get this to work fine populating either Dogs or Cats (by > > > > themself) but I can't seem to create the correct query to get > > > > 'both' populated per person. > > > > > > > > For example for just Cats per peson, the below works (might be > > > > a typo in the below since I changed what I'm working on to > > > > Cats, Dogs, Person for sake of clarity): > > > > > > > > <resultMap id="personMap" class="foo.bar.Person" groupBy="personID"> > > > > <result property="personID" > > column="personID"/> > > > > <result property="personName" > > column="personName" /> > > > > <result property="cats" resultMap="Persons.catsMap"/> > > > > <!--<result property="dogs" resultMap=" Persons.dogsMap"/>--> > > > > </resultMap> > > > > > > > > <resultMap id="catsMap" class="foo.bar.Cat"> > > > > <result property="catID" column="catID"/> > > > > <result property="catName" column="catName"/> > > > > </resultMap> > > > > > > > > <resultMap id="dogsMap" class="foo.bar.Dog"> > > > > <result property="dogID" column="dogID"/> > > > > <result property="dogName" column="dogName"/> > > > > </resultMap> > > > > > > > > <!-- below query needs to also add dogs !!! --> > > > > <select id="getPersons" resultMap="personMap"> > > > > SELECT > > > > p.personID, > > > > p.pesonName, > > > > c.catID, > > > > c.catName > > > > FROM Person p > > > > LEFT JOIN Cat c ON p.personID = c.personID > > > > ORDER BY > > > > p.personID, c.catID > > > > </select> > > > > > > > > > > > > When I include the result property dogs and > > > > try to join in DOGS - LEFT JOIN Dog d ON p.personID = d.personID - > > > > I end up with too much duplicate data per Person when iBATIS > > > > builds my objects. > > > > > > > > I'm assuming I'm missing something simple and/or being a typical > > > > idiot and doing soemthing stupid? Thanks for any help. > > > > > > > > > > > > > -- > > > Rick > > > > > > >
