ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: Mapped statement with chained queries ?
Date Fri, 21 Jul 2006 19:57:40 GMT
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 <fjanon@yahoo.com> 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
>
>
>
>


Mime
View raw message