On Sunday 16 Oct 2005 16:51, Clinton Begin wrote:
> Okay....let's start this thread over.
>
> 1) You don't need to use groupBy just because you now have a composite key.
> groupBy is for resolving N+1 select issues where you have 1:M or M:M
> relationships. Since I don't see a nested resultMap attribute in either of
> your result maps, I can only assume you don't need this.
>
Actually I do - but only because its much more complicated than I made out. I
have currently given up with the multiple key stuff, mainly because I have
decided to do it a different way. In fact I now have it working using two
selects one after the other, into different result maps and then use java in
my application to merge them together. Let me explain:-
I am building a family tree application, and my database has just two entities
Persons - key is id, foreign keys are mother and father (nulls if undefined)
Marriages - key is {husband, wife, m_no} husband and wife are foreign keys on
Persons - nulls not allowed, m_no is incremented for couples who divorce and
re-marry. [This addition of m_no was crucial - I had it all working before I
realised I needed it]
for a given individual I want to list
a) All the spouses related to this individual by marriage (with of marriage
and divorce listed) and with the children the children from this partnership
listed underneath
b) All the partners for which this individual is the other parent of a child
but where they have not been married - the children should be listed
underneath
c) All the children for which there this individual is a parent
Provided I had different sql for a Male and a Female, I had managed to
construct a single SQL statement that joined persons (twice - once for
spouse/parent and one for child) with marriages to generate this list
From a Java classes point of view - when I first asked the question I had just
realised the m_no problem and was trying to graft it on to my existing
structure. Here I had three classes.
Person (Integer id, ...and other details such as forename and surname ... )
Marriage (Person spouse, ... and marriage details)
Family (Marriage marriage, List<Person> children).
A queryForList would then populate a List<Family> variable.
> 2) Using composite column definitions is for passing multiple parameters to
> a sub-select, which you also don't appear to be using because there's no
> select attribute in your result map either.
>
> So let's first clarify what exactly it is you're trying to do. Here's what
> we understand:
>
> * You have two columns: sid and m_no
>
> Here's the part we don't know:
>
> * What are you trying to map them to?
The previous version had worked with a Result Map to describe the family class
and groupBy of "marriage.spouse.id" (Marriage was null in the case where a
right join had left the spouse/married columns with null)
Assuming I added a marriage.m_no in, I was now wanting to
groupBy="{marriage.spouse.id, marriage.mno}" so that a new Family item was
made when the combination of marriage.spouse.id and m.no varied - but when
this combination stayed the same, new Person entries would be added to the
children List)
>
> From your description it's unclear if you're trying to:
>
> * Map two columns to two properties (use two properties and normal
> mappings) * Map two columns to one property (use SQL concatenation and an
> alias) * Map two columns to a complex property using a object graph
> navigation (use two result mappings and object.dot.notation)
Already doing this - understand it fine
> * Map two columns to a complex property using a sub-select (use composite
> column mapping and the select attribute mapped to a second SQL statement)
Trying to avoid this
> * Map two columns to a complex collection using a join and repeating groups
> (use groupBy and a nested resultMap attribute to map to a second resultMap)
This last one is the main one - but see the point above
However - in struggling with this, I realised that my Java classes are wrong
to map this new combination - so I redefined them as follows
Person (Integer id, ...)
Marriage (irrelevent - just mapping some attributes to this)
Relationship (Person spouse, List<Marriage> marriages, List<Person> children)
I was then hoping something like this would work
<resultMap id="marriage-list" class="relationship" groupBy="spouse.id" >
<result property="spouse.id" column="sid"/>
...
<result property="marriages" resultMap="Family.marriages" />
<result property="children" resultMap="Family.children" />
</resultMap>
WIth the two other result maps just mapping the Java attributes to the
appropriate columns from the select. However, I now realised the underlying
SQL was giving problems - because on a normal relationship with multiple
children, I was getting repeated marriages (with the same dates). My SQL is
not up to fixing this (I think I want some form of union but ...)
I now do this
<resultMap id="marriage-list" class="relationship" groupBy="spouse.id" >
<result property="spouse.id" column="sid"/>
<result property="spouse.forename" column="sfname" />
<result property="spouse.surname" column="ssname" />
<result property="marriages" resultMap="Family.marriages" />
</resultMap>
and
<resultMap id="children-list" class="relationship" groupBy="spouse.id" >
<result property="spouse.id" column="sid"/>
<result property="spouse.forename" column="sfname" />
<result property="spouse.surname" column="ssname" />
<result property="children" resultMap="Family.children" />
</resultMap>
and then in the Java do
List<Relationship> marriages;
Map family;
family = map.queryForMap("getMaleChildren",getPersonId(),"spouse.id");
marriages = map.queryForList("getMaleMarriages", getPersonId());
// Now run through the marriages list and add related records to the Family
//map
for (Relationship r : marriages ) {
Integer spouseid = r.getSpouse().getId();
Relationship c = (Relationship) family.get(spouseid);
if (c == null ) {
family.put(spouseid,r);
} else {
c.setMarriages(r.getMarriages());
family.put(spouseid,c);
}
}
This seems to do the job
BUT I would be interested if there was a better "IBATIS" way to achieve the
same.
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
