You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Alan Chandler <al...@chandlerfamily.org.uk> on 2005/10/14 20:05:50 UTC
Group By for multiple column primary keys
I have a table in which I have just add to change it from one to two columns
making up the primary key. (before it was column "sid", now I also need
"m_no")
This is how I used to do it
<resultMap id="family-list" class="family"
groupBy="marriage.spouse.id" >
<result property="marriage.spouse.id" column="sid" />
<result property="marriage.m_no" column="m_no" />
...
I think the manual implies this - is it correct?
<resultMap id="family-list" class="family"
groupBy="a_key" >
<result property=a_key column="{marriage.spouse.id = sid, marriage.m_no
=m_no}" />
... (ignoring mail wordwrap issues)
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
Re: Group By for multiple column primary keys
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
On Friday 14 Oct 2005 23:01, Alan Chandler wrote:
...
> >
> > I think the manual implies this - is it correct?
> >
> > <resultMap id="family-list" class="family"
> > groupBy="a_key" >
> > <result property=a_key column="{marriage.spouse.id = sid, marriage.m_no
> > =m_no}" />
> >
> > ... (ignoring mail wordwrap issues)
>
> This doesn't work
>
> I get a Runtime exception
>
> Caused by: com.ibatis.common.beans.ProbeException: There is no WRITEABLE
> property named 'a_key' in class
> 'uk.org.chandlerfamily.sqlmap.famtree.Family'
So I made a writeable property,
but I now getting this message
Caused by: com.ibatis.sqlmap.client.SqlMapException: No type handler could be
found to map the property 'marriage.mkey' to the column
'{spouseid=sid,mno=m_no}'. One or both of the types, or the combination of
types is not supported.
So HOW DO I make this work?
My Result map fragment is
<resultMap id="family-list" class="family"
groupBy="marriage.mkey" >
<result property="marriage.mkey" column="{spouseid=sid,mno=m_no}"/>
,,,
And so as to incorprate marriage.mkey as the property I created a class MKey
(all the private variables have an appropriate getters and setters)
public class Mkey {
private int spouseid;
private short mno;
...
}
The Family class refered to in the result map is as follows (together with the
Marriage class which is the type of one of the variables - and again there
are getters and setters for the private variables AND a special getter and
setter for a psuedo mkey variable)
public class Family {
private Marriage marriage;
...
}
public class Marriage {
private PersonSummary spouse;
private short m_no;
...
public void setMkey (Mkey mkey) {
spouse.setId(mkey.getSpouseid());
m_no=mkey.getMno();
}
public Mkey getMkey() {
return new Mkey(spouse.getId(),m_no);
}
...
}
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
Re: Group By for multiple column primary keys
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
On Friday 14 Oct 2005 19:05, Alan Chandler wrote:
> I have a table in which I have just add to change it from one to two
> columns making up the primary key. (before it was column "sid", now I also
> need "m_no")
>
> This is how I used to do it
>
> <resultMap id="family-list" class="family"
> groupBy="marriage.spouse.id" >
> <result property="marriage.spouse.id" column="sid" />
> <result property="marriage.m_no" column="m_no" />
> ...
>
> I think the manual implies this - is it correct?
>
> <resultMap id="family-list" class="family"
> groupBy="a_key" >
> <result property=a_key column="{marriage.spouse.id = sid, marriage.m_no
> =m_no}" />
>
> ... (ignoring mail wordwrap issues)
This doesn't work
I get a Runtime exception
Caused by: com.ibatis.common.beans.ProbeException: There is no WRITEABLE
property named 'a_key' in class 'uk.org.chandlerfamily.sqlmap.famtree.Family'
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
Re: Group By for multiple column primary keys
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
On Friday 14 Oct 2005 21:12, Dan Bradley wrote:
> Here's a working example of a compound key from a project of mine:
I think I am missing some things to make sense of this ...
>
> <sqlMap namespace="Account">
> <resultMap id="account-result" class="account">
> <result property="id.number" column="id" />
> <result property="id.version" column="version" />
> ...
> <select id="getAccountByVersionId" resultMap="account-result"
> parameterClass="versionId">
> <![CDATA[
> select * from accounts
> where id=#number# and version=#version#
> and deleted=0
> ]]>
> </select>
what exactly has your classes got in them? something like this?
public class account {
...
public versionid getid();
}
public class versionid {
...
public int getNumber() {
...
}
public int getVersion() {
...
}
}
>
> <sqlMap namespace="Organization">
> <resultMap id="organization-result" class="organization">
> <result property="id.number" column="id" />
> <result property="id.version" column="version" />
> <result property="accounts" column="{id=id,version=version}"
> select="getAccountsForOrganizationId" />
> ...
What is in this select? and what does the property "accounts" mean in java
terms?
Since I want to do a GroupBy clause, effectively on any changes to the
"accounts" property
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
Re: Group By for multiple column primary keys
Posted by Dan Bradley <de...@gmail.com>.
Here's a working example of a compound key from a project of mine:
<sqlMap namespace="Account">
<resultMap id="account-result" class="account">
<result property="id.number" column="id" />
<result property="id.version" column="version" />
...
<select id="getAccountByVersionId" resultMap="account-result"
parameterClass="versionId">
<![CDATA[
select * from accounts
where id=#number# and version=#version#
and deleted=0
]]>
</select>
<sqlMap namespace="Organization">
<resultMap id="organization-result" class="organization">
<result property="id.number" column="id" />
<result property="id.version" column="version" />
<result property="accounts" column="{id=id,version=version}"
select="getAccountsForOrganizationId" />
...
On 10/14/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> I have a table in which I have just add to change it from one to two columns
> making up the primary key. (before it was column "sid", now I also need
> "m_no")
>
> This is how I used to do it
>
> <resultMap id="family-list" class="family"
> groupBy="marriage.spouse.id" >
> <result property="marriage.spouse.id" column="sid" />
> <result property="marriage.m_no" column="m_no" />
> ...
>
> I think the manual implies this - is it correct?
>
> <resultMap id="family-list" class="family"
> groupBy="a_key" >
> <result property=a_key column="{marriage.spouse.id = sid, marriage.m_no
> =m_no}" />
>
> ... (ignoring mail wordwrap issues)
>
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.uk
> Open Source. It's the difference between trust and antitrust.
>
Re: Group By for multiple column primary keys
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
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.
Re: Group By for multiple column primary keys
Posted by Clinton Begin <cl...@gmail.com>.
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.
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?
>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)
* 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)
* 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)
Let us know what it is you're trying to do...it's all possible.
Cheers,
Clinton
On 10/14/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
>
> I have a table in which I have just add to change it from one to two
> columns
> making up the primary key. (before it was column "sid", now I also need
> "m_no")
>
> This is how I used to do it
>
> <resultMap id="family-list" class="family"
> groupBy="marriage.spouse.id <http://marriage.spouse.id>" >
> <result property="marriage.spouse.id <http://marriage.spouse.id>"
> column="sid" />
> <result property="marriage.m_no" column="m_no" />
> ...
>
> I think the manual implies this - is it correct?
>
> <resultMap id="family-list" class="family"
> groupBy="a_key" >
> <result property=a_key column="{marriage.spouse.id<http://marriage.spouse.id>= sid,
> marriage.m_no
> =m_no}" />
>
> ... (ignoring mail wordwrap issues)
>
>
> --
> Alan Chandler
> http://www.chandlerfamily.org.uk
> Open Source. It's the difference between trust and antitrust.
>