ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Reumann" <ric...@gmail.com>
Subject Re: One query populating *multiple* lists per object returned
Date Thu, 11 May 2006 15:02:34 GMT
I let my stupid question sit for a few days so now... Bueller,
Bueller... anyone, anyone ... :)


On 5/9/06, Rick Reumann <rickcr@gmail.com> 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

Mime
View raw message