Maybe I’m missing something, but why not do a join on virtual tables making the database do the work, and just map it right to the object like normal.  I.e., the select would look like:






work.Value as WorkPhone,

home.Value as HomePhone,

mobile.Value as MobilePhone

from Student s

                left outer join (select Value from StudentContact where ContactMethod=1) work on

                                s.StudentId = worke.StudentId

                left outer join (select Value from StudentContact where ContactMethod=2) home on

                                s.StudentId = home.StudentId

                left outer join (select Value from StudentContact wehre ContactMethod=3) mobile on

                                s.StudentId = mobile.StudentId


That would map straight to a resultmap which I’m assuming you know how to do.  Not sure what RDMS you’re working with, but this would definitely work with MSSQL or Oracle.



From: Julian Maughan []
Sent: Friday, February 29, 2008 2:34 AM
Subject: Re: Denormalization mapping - possible?


Hi Nicholas

I was hoping that IBatis had a way of doing this that wouldn't require a SELECT on the StudentCourse table for each contact method type. Even with your second approach (with the sub-select), it requires multiple selects. Ideally I would want to do a single query (SELECT ContactMethod, Value FROM StudentCourse WHERE StudentID = ?) and then map this to the Student object.

I actually thought this would be a scenario that IBatis could deal with quite easily. Perhaps the only way to achieve what I want is to resort to code (QueryWithRowDelegate, as you said). Many thanks for your input into least I know I'm probably not missing something obvious.


On 29/02/2008, Nicholas Piasecki <> wrote:

And of course now that I think about it, if your database supports sub-
selects, you could do it all in one go. That would be easier and less
crazy than the e-mail I just shot out. Something like ...

        (SELECT Value FROM StudentContact WHERE ContactMethod=1 AND
StudentId=#value#) AS WorkPhone,
FROM Student
WHERE StudentId = #value#

On Feb 29, 2008, at 12:20 AM, Nicholas Piasecki wrote:

> For a no code solution, I think you can achieve what you want via a
> slight perversion of resultMapping. Something like (warning:
> untested/pseudocode ahead)...
> <resultMap id="ResultMapStudent">
>       <result property="WorkPhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />
>       <result property="HomePhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />\
>       <result property="MobilePhone"
> column="ContactMethod=WorkPhoneContactMethod, StudentId=StudentId"
>               select="SelectPhoneNumberByContactMethodAndStudentId" />
>       <!-- Other Properties -->
> </result>
> <select id="GetStudents" resultMap="ResultMapStudent">
>       SELECT
>               1 AS WorkPhoneContactMethod,
>               2 AS HomePhoneContactMethod,
>               3 AS MobilePhoneContactMethod
>               StudentId,
>               GivenNames,
>               Surname
>       FROM Student
> </select>
> <statement id="SelectPhoneNumberByContactMethodAndStudentId"
> resultClass="string">
>       SELECT
>               Value
>       FROM StudentContact
>       WHERE ContactMethod=#ContactMethod# AND
>       StudentId = #StudentId#
> </statement>
> This is based on the documentation at
> .
> This may or may not be an insane solution or might not work, so
> we'll see if others chime in. If it doesn't work, I suppose you
> could always do some data wrangling in a QueryWithRowDelegate call.
> Hope this helps!
> V/R,
> Nicholas Piasecki
> Software Developer
> Skiviez, Inc.
> 804-550-9406
> On Feb 28, 2008, at 11:17 PM, Julian Maughan wrote:
>> Could anyone explain if the following can be done in IBatis.Net:
>> I have two tables...
>> 1) Student
>> - StudentId (int)
>> - GivenNames (varchar)
>> - Surname (varchar)
>> 2) StudentContact
>> - StudentContactId (int)
>> - Value (varchar), ie. a phone number
>> - ContactMethod (int), eg. 1 = WorkPhone, 2 = HomePhone, 3 =
>> MobilePhone, etc..
>> - StudentId (int) - foreign key to Student, allowing many
>> StudentContacts per Student
>> I want to map these tables to a class as follows:
>> Class Student
>> Private _studentId As Integer
>> Private _givenNames As String
>> Private _surname As String
>> Private _workPhone As String
>> Private _homePhone As String
>> Private _mobilePhone As String
>> End Student
>> As you can see, I want to use the mapping to 'denormalize' the
>> contact details on the Student class. I could easily map to an
>> IList(Of String) property instead, but I was hoping the mapper
>> would allow me to design my classes the way I want them...
>> Thanks
>> Julian

Nothing can have as its destination anything other than its origin. The contrary idea, the idea of progress, is poison.