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
(SELECT Value FROM StudentContact WHERE ContactMethod=1 AND
StudentId=#value#) AS WorkPhone,
StudentId,
GivenName,
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 http://ibatis.apache.org/docs/dotnet/datamapper/ch03s05.html#id384072
.
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.
[EMAIL PROTECTED]
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
