On Wednesday 04 January 2006 22:44, radha rukmani wrote:
> Hi
> I am new to iBATIS. I was able to run the person example in the ibatis
> website. For one table, the example says,
> <select id="getPerson" resultClass="com.comp.app.batchProcess.Person">
> SELECT
> PER_ID as id,
> PER_FIRST_NAME as firstName,
> PER_LAST_NAME as lastName,
> PER_BIRTH_DATE as birthDate,
> PER_WEIGHT_KG as weightInKilograms,
> PER_HEIGHT_M as heightInMeters
> FROM PERSON
> WHERE PER_ID=#value#
> </select>
> but if i want details from two tables, say person and dept table. I have
> two bean classes person.java and dept.java and if i want to select like,
> select per_id as id, per_first_name as firstname, dept_name as deptname
> from person,dept where dept_no = 2 now
> resultClass="com.comp.app.batchProcess.Person" and
> "com.comp.app.batchProcess.Dept" how to get around this. Any help is
> greatly appreciated.
> Thanks
I don't know if its the best answer, but I just made a new class which
contained an instance of each of the two classes (or one of them can be a
list if its an 1:n relationship and you are joining)
Then in the result map you use the dot notation to specify the separate
subfield. Here's an example
Note: Relationship is a new class that contains a "person" (as spouse) and a
list of marriages.
<sqlMap namespace="Family">
<typeAlias alias="relationship"
type="uk.org.chandlerfamily.sqlmap.famtree.Relationship" />
<typeAlias alias="marriage"
type="uk.org.chandlerfamily.sqlmap.famtree.Marriage" />
<resultMap id="marriage-list" class="relationship" groupBy="spouse.id" >
<result property="spouse.id" column="sid"/>
<result property="spouse.forename" column="sfname" />
<result property="spouse.surname" column="ssname" />
<result property="spouse.gender.male" column="smale" />
<result property="spouse.DOB.year" column="DOB_year" />
<result property="spouse.DOB.month" column="DOB_month" />
<result property="spouse.DOB.day" column="DOB_day" />
<result property="marriages" resultMap="Family.marriages" />
</resultMap>
<resultMap id="marriages" class="marriage">
<result property="m_no" column="m_no" />
<result property="DOM.year" column="DOM_year" />
<result property="DOM.month" column="DOM_month" />
<result property="DOM.day" column="DOM_day" />
<result property="DOD.year" column="DOD_year" />
<result property="DOD.month" column="DOD_month" />
<result property="DOD.day" column="DOD_day" />
</resultMap>
<select id="getMaleMarriages" parameterClass="int"
resultMap="marriage-list">
SELECT s.id AS sid, s.forename AS sfname, s.surname AS ssname, s.male as
smale,
s.DOB_year as DOB_year, s.DOB_month as DOB_month, s.DOB_day as DOB_day,
m_no, DOM_year , DOM_month, DOM_day, m.DOD_year AS DOD_year, m.DOD_month AS
DOD_month, m.DOD_day AS DOD_day
FROM persons AS s JOIN marriages AS m ON wife = s.id
WHERE husband = #value# ORDER BY s.id, DOM_year, DOM_month, DOM_day, m_no
</select>
--
Alan Chandler
http://www.chandlerfamily.org.uk
Open Source. It's the difference between trust and antitrust.
