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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote:
> > > I let my stupid question sit for a few days so now... Bueller,
> > > Bueller... anyone, anyone ... :)
> > >
> > >
> > > On 5/9/06, Rick Reumann <[EMAIL PROTECTED]> 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
> > >
> >
> >
