ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ted Schrader" <teds.mailing.li...@gmail.com>
Subject Re: groupBy with combined key
Date Thu, 19 Apr 2007 16:23:42 GMT
Hi Camilla,

See if this thread might help you:

http://www.mail-archive.com/user-java@ibatis.apache.org/msg08210.html

Ted

On 19/04/07, Camilla Wolf <Camilla.Wolf@gmx.net> 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
>

Mime
View raw message