You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Fred Janon <fj...@yahoo.com> on 2006/07/21 16:38:39 UTC
Mapped statement with chained queries ?
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
Re: Mapped statement with chained queries ?
Posted by Fred Janon <fj...@yahoo.com>.
Thanks Nathan, but I should have said that actually that's the other way
around: a client has many jobs that has many tasks. So getting the
client name for a task is a 1 to 1 mapping. I would not mind solving the
problem with an outer join or a subquery but so far I haven't been able
to write a suitable query and it is quite important for me since I don't
duplicate any field in my schema but use foreign keys everywhere.
My goal is to list the task with the client name via the job, not much
more than that: 2 levels on indirection.
Could you give me an example for solving the problem in one SQL query
using outer join or anything else?
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 <ma...@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
>>
>>
>>
>>
>
Re: Mapped statement with chained queries ?
Posted by Nathan Maves <Na...@Sun.COM>.
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
>>> <ma...@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>
Re: Mapped statement with chained queries ?
Posted by Fred Janon <fj...@yahoo.com>.
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 <ma...@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
>>
>>
>>
>>
>
Re: Mapped statement with chained queries ?
Posted by Nathan Maves <Na...@Sun.COM>.
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 <fj...@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
>
>
>
>
Re: Mapped statement with chained queries ?
Posted by Debasish Dutta Roy <de...@gmail.com>.
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 <fj...@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
>
>
>
>