ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fred Janon <fja...@yahoo.com>
Subject Re: Mapped statement with chained queries ?
Date Sat, 22 Jul 2006 02:50:58 GMT
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* <fjanon@yahoo.com <mailto: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