Handling multiple table aliases in a query using one resultMap.

[Iwao AVE! <ha...@gmail.com>]

Hi all,

It was a great news that [#IBATIS-225] had been implemented.
I'm wondering if it can be improved a little bit more.

--
Assuming I have 2 simple java classes.
Needless to say, there are corresponding tables in a database.

public class Staff {
    Integer id;
    String name;
    // ... accessor methods
}

public class Section {
    Integer sectionId;
    Staff manager;
    Staff submanager;
    // ... accessor methods
}

To retrieve section with its manager and submanager, I will have a query
like below.

<select id="select" resultMap="sectionResult" resultClass="int">
SELECT
    section.section_id,
    manager.staff_id AS manager_id,
    manager.staff_name AS manager_name,
    submanager.staff_id AS submanager_staff_id,
    submanager.staff_name AS submanager_staff_name
FROM
    section,
    staff AS manager,
    staff AS submanager
WHERE
    section.section_id = #value# AND
    section.manager_id = manager.staff_id AND
    section.submanager_id = submanager.staff_id
</select>

Then I will define the sectionResult resultMap.
Currently, it may have to be like this, I suppose.

<resultMap id="sectionResult" class="sectionClass">
    <result property="sectionId" column="section_id"/>
    <result property="manager.id" column="manager_staff_id"/>
    <result property="manager.name" column="manager_staff_name"/>
    <result property="submanager.id" column="submanager_staff_id"/>
    <result property="submanager.name" column="submanager_staff_name"/>
</resultMap>

Because both manager and submanager are instances of Staff, wouldn't it
be nice to have a way to map these properties using externaly defined
resultMap? (In my understanding, I can specify resultMap for one of
manager or submanager, but not for both of them, right?)

So, here's my proposal which introduces 'columnPrefix' attribute.
On applying staffResult, iBATIS appends the prefix to the name of each
column.

<resultMap id="sectionResult" class="sectionClass">
    <result property="sectionId" column="section_id"/>
    <result property="manager" resultMap="staff"
            columnPrefix="manager_"/>
    <result property="submanager" resultMap="staff"
            columnPrefix="submanager_"/>
</resultMap>

<resultMap id="staffResult" class="staffClass">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
</resultMap>

--
If some of you like it, I will submit new improvement request to JIRA.
Or, if iBATIS already has a way to achieve this, please show me how to
do it.

--
Regards,

Iwao

Re: Handling multiple table aliases in a query using one resultMap.

[Jeff Butler <je...@gmail.com>]

I understand the issue, and your proposed solution.  I think the
implementation could get complex - especially if the referenced result map
referenced other result maps.  Result processing is already pretty complex
in iBATIS - this would add to that complexity.

I'd like to hear from other users to see if this is a big need.  My feeling
is that this use case - where you populate different nested objects of the
same type from a single query - is pretty rare.

Jeff Butler



On 12/4/06, Iwao AVE! <ha...@gmail.com> wrote:
>
> Hi all,
>
> It was a great news that [#IBATIS-225] had been implemented.
> I'm wondering if it can be improved a little bit more.
>
> --
> Assuming I have 2 simple java classes.
> Needless to say, there are corresponding tables in a database.
>
> public class Staff {
>    Integer id;
>    String name;
>    // ... accessor methods
> }
>
> public class Section {
>    Integer sectionId;
>    Staff manager;
>    Staff submanager;
>    // ... accessor methods
> }
>
> To retrieve section with its manager and submanager, I will have a query
> like below.
>
> <select id="select" resultMap="sectionResult" resultClass="int">
> SELECT
>    section.section_id,
>    manager.staff_id AS manager_id,
>    manager.staff_name AS manager_name,
>    submanager.staff_id AS submanager_staff_id,
>    submanager.staff_name AS submanager_staff_name
> FROM
>    section,
>    staff AS manager,
>    staff AS submanager
> WHERE
>    section.section_id = #value# AND
>    section.manager_id = manager.staff_id AND
>    section.submanager_id = submanager.staff_id
> </select>
>
> Then I will define the sectionResult resultMap.
> Currently, it may have to be like this, I suppose.
>
> <resultMap id="sectionResult" class="sectionClass">
>    <result property="sectionId" column="section_id"/>
>    <result property="manager.id" column="manager_staff_id"/>
>    <result property="manager.name" column="manager_staff_name"/>
>    <result property="submanager.id" column="submanager_staff_id"/>
>    <result property="submanager.name" column="submanager_staff_name"/>
> </resultMap>
>
> Because both manager and submanager are instances of Staff, wouldn't it
> be nice to have a way to map these properties using externaly defined
> resultMap? (In my understanding, I can specify resultMap for one of
> manager or submanager, but not for both of them, right?)
>
> So, here's my proposal which introduces 'columnPrefix' attribute.
> On applying staffResult, iBATIS appends the prefix to the name of each
> column.
>
> <resultMap id="sectionResult" class="sectionClass">
>    <result property="sectionId" column="section_id"/>
>    <result property="manager" resultMap="staff"
>            columnPrefix="manager_"/>
>    <result property="submanager" resultMap="staff"
>            columnPrefix="submanager_"/>
> </resultMap>
>
> <resultMap id="staffResult" class="staffClass">
>    <result property="id" column="id"/>
>    <result property="name" column="name"/>
> </resultMap>
>
> --
> If some of you like it, I will submit new improvement request to JIRA.
> Or, if iBATIS already has a way to achieve this, please show me how to
> do it.
>
> --
> Regards,
>
> Iwao
>
>