Hi there,
I have three tables: contacts, addresses and attributes. Each contact
has a number of addresses and a number of attributes. If I just want to
get the only the addresses or only the attributes for a contact I have
no problem, but if I try to get all the addresses and all the attributes
for a contact, I end up getting either the attributes or the addresses
(whichever one is defined last in the resultmap) doubled - i.e. I will
get four address entries when there is only two. Also note that this
does not happen if there is only one matching entry in one of the
tables. Here are my sqlmaps:
<sqlMap namespace="Contact">
<resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
<result property="id" column="contact_id" />
<result property="uuid" column="contact_uuid" />
<result property="deleted" column="contact_deleted" />
<result property="login" column="contact_login" />
<result property="passwordHash" column="contact_password" />
<result property="attributes"
javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
<result property="addresses" javaType="java.util.ArrayList"
resultMap="Address.address" />
</resultMap>
<resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
<result property="id" column="owner_attribute_id" />
<result property="uuid" column="owner_attribute_uuid" />
<result property="value" column="owner_attribute_value" />
</resultMap>
<resultMap id="address" class="java.util.HashMap" groupBy="id">
<result property="id" column="address_id" />
<result property="uuid" column="address_uuid" />
<result property="deleted" column="address_deleted" />
<result property="name" column="address_name" />
<result property="number" column="address_number" />
<result property="street" column="address_street" />
<result property="suburb" column="address_suburb" />
<result property="city" column="address_city" />
<result property="country" column="address_country" />
<result property="postCode" column="address_post_code" />
<result property="contactUuid" column="address_contact_uuid" />
</resultMap>
<select id="listContactSimple" resultMap="contactSimple">
SELECT
*
FROM contact
LEFT OUTER JOIN address on address_contact_uuid =
contact.contact_uuid
LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
owner_attribute_owner_uuid
WHERE
(owner_attribute_deleted ISNULL OR owner_attribute_deleted =
false )
AND (address_deleted ISNULL OR address_deleted = false )
<dynamic>
<isNotNull prepend="AND " property="deleted">
contact.contact_deleted = '$deleted$'
</isNotNull> <isNotNull
prepend="AND " property="passwordHash">
contact.contact_password = #passwordHash#
</isNotNull> <isNotNull prepend="AND "
property="login">
contact.contact_login ILIKE '%$login$%'
</isNotNull>
<isNotNull prepend="AND " property="uuid">
contact.contact_uuid = #uuid#
</isNotNull>
</dynamic>
ORDER BY contact.contact_login ASC
</select>
</sqlMap>
Any help on where I'm going wrong would be greatly appreciated.
Thanks.
Ben.
