ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Iwao AVE!" <haraw...@gmail.com>
Subject Handling multiple table aliases in a query using one resultMap.
Date Mon, 04 Dec 2006 14:57:00 GMT
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


Mime
View raw message