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.
