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
