ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Reumann" <ric...@gmail.com>
Subject One query populating *multiple* lists per object returned
Date Tue, 09 May 2006 15:08:16 GMT
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.

Mime
View raw message