ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Lamey <cla...@localmatters.com>
Subject Re: One query populating *multiple* lists per object returned
Date Fri, 12 May 2006 15:22:28 GMT
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