You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Rick Reumann <ri...@gmail.com> on 2006/05/09 17:08:16 UTC
One query populating *multiple* lists per object returned
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.
Re: One query populating *multiple* lists per object returned
Posted by Larry Meadors <lm...@apache.org>.
Basically, your row handler acts as a data collector - it only has one
method (handleRow), so you need to add your own data structures and
examine the returned objects to see where you need to put the stored
objects.
So, if we look at rick's question, let's assume this data structure:
create table person (personname varchar(10));
create table cat (personname varchar(10), catname varchar(10));
create table dog (personname varchar(10), dogname varchar(10));
insert into person values ('tim');
insert into dog values ('tim', 'boobear');
insert into dog values ('tim', 'girlfriend');
insert into cat values ('tim', 'breakfast');
insert into cat values ('tim', 'lunch');
insert into cat values ('tim', 'dinner');
If we join all of this:
select p.personname, c.catname, d.dogname
from person p
join cat c on p.personname = c.personname
join dog d on p.personname = d.personname
We get this data back (formatting sucks, but you get the picture):
personname catname dogname
tim breakfast boobear
tim breakfast girlfriend
tim lunch boobear
tim lunch girlfriend
tim dinner boobear
tim dinner girlfriend
We get dog*cat rows back.
Assume we want that in this:
public class Person {
private String name;
private Set<String> catSet = new HashSet<String>();
private Set<String> dogSet = new HashSet<String>();
// getters / setters...
}
The row handler for this looks like this:
public class PersonDogCatRowHandler implements RowHandler {
private Person person = new Person();
public void handleRow(Object object) {
Map map = (Map) object;
person.setName((String) map.get("personname"));
person.getCatSet().add((String) map.get("catname"));
person.getDogSet().add((String) map.get("dogname"));
}
public Person getPerson() {
return person;
}
}
The person then is this:
Person{name='tim', catSet=[breakfast, dinner, lunch], dogSet=[boobear,
girlfriend]}
Larry
On 5/12/06, Christopher Lamey <cl...@localmatters.com> wrote:
> 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 <je...@gmail.com> 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 <ri...@gmail.com> wrote:
> > > > I let my stupid question sit for a few days so now... Bueller,
> > > > Bueller... anyone, anyone ... :)
> > > >
> > > >
> > > > On 5/9/06, Rick Reumann <ri...@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
> > > >
> > >
> > >
>
>
Re: One query populating *multiple* lists per object returned
Posted by Christopher Lamey <cl...@localmatters.com>.
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 <je...@gmail.com> 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 <ri...@gmail.com> wrote:
> > > I let my stupid question sit for a few days so now... Bueller,
> > > Bueller... anyone, anyone ... :)
> > >
> > >
> > > On 5/9/06, Rick Reumann <ri...@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
> > >
> >
> >
Re: One query populating *multiple* lists per object returned
Posted by Larry Meadors <lm...@apache.org>.
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 <je...@gmail.com> 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 <ri...@gmail.com> wrote:
> > I let my stupid question sit for a few days so now... Bueller,
> > Bueller... anyone, anyone ... :)
> >
> >
> > On 5/9/06, Rick Reumann <ri...@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
> >
>
>
Re: One query populating *multiple* lists per object returned
Posted by Rick Reumann <ri...@gmail.com>.
Jeff Butler 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.
Thanks Jeff for your comments. Makes perfect sense. I forgot about using
a custom List Implementation approach. That would work out nicely.
--
Rick
Re: One query populating *multiple* lists per object returned
Posted by Jeff Butler <je...@gmail.com>.
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 <ri...@gmail.com> wrote:
>
> I let my stupid question sit for a few days so now... Bueller,
> Bueller... anyone, anyone ... :)
>
>
> On 5/9/06, Rick Reumann <ri...@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
>
Re: One query populating *multiple* lists per object returned
Posted by Rick Reumann <ri...@gmail.com>.
I let my stupid question sit for a few days so now... Bueller,
Bueller... anyone, anyone ... :)
On 5/9/06, Rick Reumann <ri...@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