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]> 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
