Hi Ted, thanks for your reply. I tried it with groupBy="fk,index" but still there is the false duplicate result: (id1, 0, value1) (id1, 1, value1) //should be (id1, 1, value2)
In difference to Mike I do have a second collection in my result JobDef and therefore the rows in the sql result set are duplicated. Maybe that's the problem in addition to the groupBy clause? I forgot to mention that I use ibatis-2.3.0.677.jar -------- Original-Nachricht -------- Datum: Thu, 19 Apr 2007 12:23:42 -0400 Von: "Ted Schrader" <[EMAIL PROTECTED]> An: [email protected] Betreff: Re: groupBy with combined key > Hi Camilla, > > See if this thread might help you: > > http://www.mail-archive.com/[email protected]/msg08210.html > > Ted > > On 19/04/07, Camilla Wolf <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I'am new to IBATIS and try to get an ordered list from the database > within a parent result map. > > I figured out, that I need to provide the groupBy attribute in the > childs result-map but that does not work correctly and the problem seems to > be, > that I use a combined key in the child table. > > > > Parent table: > > CREATE TABLE JOBDEF(JOBDEF_ID CHAR(128) NOT NULL, > > CONSTRAINT listElement_unique UNIQUE (FK_ID, KEYNO)) > > > > Child tables: > > CREATE TABLE LIST_ELEMENT(FK_ID CHAR(128) NOT NULL, > > KEYNO CHAR(5) NOT NULL, > > VAL_E VARCHAR(128) NOT NULL, > > CONSTRAINT fk_listelement FOREIGN KEY (FK_ID) REFERENCES > JOBDEF(JOBDEF_ID) ON DELETE CASCADE, > > CONSTRAINT le_pk PRIMARY KEY (FK_ID, KEYNO)) > > > > > > CREATE TABLE SET_ELEMENT(FK_ID CHAR(128) NOT NULL, > > VAL_E VARCHAR(128) NOT NULL, > > CONSTRAINT fk_setelement FOREIGN KEY (FK_ID) REFERENCES > JOBDEF(JOBDEF_ID) ON DELETE CASCADE, > > CONSTRAINT se_pk PRIMARY KEY (FK_ID, VAL_E)) > > > > > > <resultMap id="get-list-result" class="ListElementImpl" groupBy="fk"> > > <result property="fk" column="JOBDEF_ID" /> > > <result property="index" column="KEYNO" javaType="String"/> > > <result property="value" column="VAL_E" javaType="String"/> > > </resultMap> > > > > <resultMap id="get-set-result" class="String"> > > <result property="value" column="VAL_E"/> > > </resultMap> > > > > <resultMap class="JobDef" id="resultJobDef" groupBy="id"> > > <result property="id" column="JOBDEF_ID"/> > > <!--... some other pros --> > > <result property="setElements" resultMap="JobDef.get-set-result"/> > > <result property="listElements" > resultMap="JobDef.get-list-result"/> > > </resultMap> > > > > The prepared statement: > > > > select * from JOBDEF j left outer join LIST_ELEMENT le on j.JOBDEF_ID = > le.FK_ID lefter outer join SET_ELEMENT se on j.JOBDEF_ID = se.FK_ID > > where j.JOBDEF_ID = ? > > > > I inserted two rows to the LIST_ELEMENT table > > (id1, 0, value1) > > (id1, 1, value2) > > > > and two others to the SET_ELEMENT table. > > > > If I query for the jobdef with id = 'id1' I get > > (id1, 0, value1) > > with the second entry missing completely > > > > If I use groupBy="index" then I get > > (id1, 0, value1) > > (id1, 1, value1) //should be (id1, 1, value2) > > > > as a result in my ArrayList for the list result but there is no row with > those mixed values in the SQL result set. > > I guess I need something like groupBy="fk AND index" but I can't get it > to work.... > > > > > > > > -- > > "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ... > > Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail > > -- "Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ... Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail
