If there are no entries in table Child then the SQL statement will
not return any records. To return the records from Parent with no
children, you need to use a LEFT OUTER JOIN.
I would suggest using your tool's SQL interpreter to get the query
right before trying to incorporate that into iBATIS.
Randy
________________________________
From: Band, Aniket [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 18, 2007 5:12 PM
To: [email protected]
Subject: RE: Problem with fetching data in one to many relationship
but it works fine when there are child records. You mean to say that if
I remove CDATA then query will fetch the result even when child is not
present?
-----Original Message-----
From: Poitras Christian [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 18, 2007 5:09 PM
To: [email protected]
Subject: RE: Problem with fetching data in one to many
relationship
Remove CDATA in second request. The CDATA prevents your second
request from working.
Or better, remove it everywhere and use > and < instead of
> and <.
Christian
________________________________
From: Band, Aniket [mailto:[EMAIL PROTECTED]
Sent: Thursday, 18 January 2007 16:49
To: [email protected]
Subject: Problem with fetching data in one to many relationship
Hi
I am having problem with fetching data using ibatis.
When there are children to a parent, parent also does not get returned.
Just to give you an idea queries are something like this
<resultMap id="parentList" class="com.example.parentDto">
<result property="id" column="PARENT_ID" columnIndex="1"/>
<result property="description" column="DESCRIPTION"
columnIndex="2"/>
<result property="childList" column="PARENT_ID"
select="getChildrenList"/>
</resultMap>
<resultMap id="childrenList" class="com.example.childDto">
<result property="Id" column="child_ID"/>
<result property="frenchName" column="FRENCH_NAME"/>
<result property="name" column="NAME"/>
</resultMap>
<statement id="getParentList" resultMap="parentList"
parameterClass="java.util.Map"><![CDATA[
SELECT
id,
description
FROM
Parent p
where
p.EFFECTIVE_DATE <= SYSDATE
AND p.END_DATE > SYSDATE
]]> </statement>
<statement id="getChildrenList" resultMap="childrenList">
<![CDATA[ SELECT
c.ID
c.FRENCH_NAME,
c.NAME
FROM
Parent p,
Child c
WHERE
p.ID = c.Parent_id
AND c.EFFECTIVE_DATE <= SYSDATE
AND c.END_DATE > SYSDATE
AND c.PARENT_ID = #value#
]]> </statement>
Any immediate help will be highly appreciated.
Regards,
Aniket
