Hi Nathan,
Which developer guide are you referring to? Version 1 or 2? Data Mapper?
I couldn't find anything on pages 36 in any of the guides I have, v1 or 2.
Thanks
Fred
Nathan Maves wrote:
Although your solution will work, I would suggest another. With your
way you run into the classic n+1 problem. One query to get the tasks,
then n number of queries to get the jobs for those tasks. You get the
point. That is why this is not the preferred way.
Try to use the build in group by functionality in iBatis. This way
you only have to write one query that uses standard joins. In your
case you might have to use some outer joins to ensure that you get all
tasks without jobs and all jobs without clients. Then use the group
by attribute in you result map. Look to page 36 of the developers
guild for an example of this functionality.
Cheers,
Nathan
On Jul 21, 2006, at 8:54 AM, Debasish Dutta Roy wrote:
You can do this in a simple manner. In the resultmap you need to
provide the values properly. Here is an example.
Here is the resultmap for Task
<resultMap id="taskMap" class="com.myproject.Task ">
<result property="jobId" column="jobid" select="getJob"/>
---- other properties ---
</resultMap>
<select id="getTask" resultMap="taskMap">
--- Your select for task ---
</select>
<resultMap id="jobMap" class="com.myproject.Job">
<result property="clientId" column="clientId" select="getClient">
--- other properties ---
</resultMap>
<select id="getJob" resultMap="jobMap">
-- select statement for getting job info--
</select>
<resultMap id="clientMap" class=" com.myproject.Client">
-- a normal ma with client bean properties mapped against
columns --
</resultMap>
<select id="getClient" resultMap="clientMap">
-- select statement for getting client info--
</select>
This way you will only call getTask from your client and iBATIS will
do the rest. You will save on database trips.
Whatever you need to specify in #value# of 2nd and 3rd statement is
taken from the "column" attribute. In this case jobId for 2nd select
and "clientId" for the 3rd select.
On 7/21/06, *Fred Janon* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
wrote:
Hi,
I have 3 tables:
Tasks:
taskid int PK
jobid int FK
description String
Jobs:
jobid PK
clientid int FK
Clients:
clientid PK
clientname String.
How can I get the Clients:clientname via the Jobs table starting
from a
taskid? I know how to do it with 1 level on "indirection" but I don't
know how to chain 2 queries...
taskid -> jobid -> clientid -> clientname.
I think it should be something like that:
<result-map name="result" class="com.jobtracking.Task">
<property name="taskId" column="taskid"/>
<property name="taskNotes" column="notes"/>
<property name="taskJobId" column="jobid"/>
<property name="clientName" column="jobid"
mapped-statement="getClientNameByJobId"/>
</result-map>
<mapped-statement name="getTaskList" result-map="result">
select taskid,notes,assignedid,deptid,completion,duedate,
donedate,tasktypeid,startdate,jobid
from tasks order by duedate
</mapped-statement>
<result-map name="clientNameResult" class="com.jobtracking.Job">
<property name="clientId" column="clientid"/>
<property name="clientName" column="clientid"
mapped-statement="getClientNameById"/>
</result-map>
<mapped-statement name="getClientNameByJobId"
result-map="clientNameResult">
select clientid from jobs where jobid=#value#
</mapped-statement>
<mapped-statement name="getClientNameById"
result-class="java.lang.String">
select name from clients where clientid = #value#
</mapped-statement>
Thanks
Fred
begin:vcard
fn:fjanon
n:Janon;Fred
email;internet:[EMAIL PROTECTED]
title:Sr S/W Engineer & Architect
x-mozilla-html:TRUE
url:http://www.geocities.com/fjanon
version:2.1
end:vcard
