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
