ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alan Chandler <a...@chandlerfamily.org.uk>
Subject Re: sql joins in ibatis
Date Wed, 04 Jan 2006 23:01:25 GMT
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 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" />
	<select id="getMaleMarriages" parameterClass="int"
SELECT s.id AS sid, s.forename AS sfname, s.surname AS ssname, s.male as 
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

Alan Chandler
Open Source. It's the difference between trust and antitrust.

View raw message