Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (herse.apache.org: domain of teds.mailing.lists@gmail.com
 designates 209.85.132.240 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=SiUytQaRrtVQxWk2+ujYpMXnZr1M40bZxzuXoO0cES+7EYsSiO7bUySgxkMz60bNSOmcmAwmVNBqzjUeUNxL3B3CwmjkAUJPuxcLQepDmA2Rna/B3Czb5ijOy6Nb0xoUH2cUqKRQ7Uko1Zf+rbOfgAcaGFxclo+Ab1S5OsfwD1M=
Message-ID: <295494f50704190923o1c12056nd78d57d9fd08cc09@mail.gmail.com>
Date: Thu, 19 Apr 2007 12:23:42 -0400
From: "Ted Schrader" <teds.mailing.lists@gmail.com>
To: user-java@ibatis.apache.org
Subject: Re: groupBy with combined key
In-Reply-To: <20070419155726.290030@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20070419155726.290030@gmx.net>

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
>