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 Mon, 24 Jul 2006 18:42:50 GMT
 From the main ibatis page click on the link "for Java"  under the  
"Downloads and Documentation" section on the left hand side.

http://ibatis.apache.org/javadownloads.html

And here is the direct link to the doc.

http://cvs.apache.org/dist/ibatis/ibatis.java/docs/iBATIS-SqlMaps-2.pdf

Nathan
On Jul 21, 2006, at 8:50 PM, Fred Janon wrote:

> 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
>>>
>>>
>>>
>>>
>>
>> <fjanon.vcf>


Mime
View raw message