ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: One query populating *multiple* lists per object returned
Date Fri, 12 May 2006 18:43:43 GMT
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 <clamey@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 <jeffgbutler@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 <rickcr@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 <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