Re: Mapped statement with chained queries ?

[Nathan Maves]

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* <[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





<fjanon.vcf>