Yes, that could be clearer - the difference here is that the examples
are all 1 -> M -> N, but what you are trying to do is 1 -> M*N.
It's not going to work, you'll need to write a rowhandler, or find
some other solution.
Larry
On 1/15/07, Ben Schmidt <[EMAIL PROTECTED]> wrote:
I agree that the SQL returns multiple rows for each instance of a
contact, but isn't that what the groupby property is supposed to handle?
From the iBatis SQLMaps documentation:
The resultMap element also supports the attribute groupBy. The groupBy
attribute is used to specify a list
of properties in this resultMap that are used to identify unique rows in
the returned result set. Rows with
equal values for the specified properties will only generate one result
object. Use groupBy in combination with nested resultMaps to solve the
N+1 query problem (see following discussion for examples).
and:
1:N & M:N Solution
iBATIS fully solves the N+1 selects solution. Here is an example:
<sqlMap namespace="ProductCategory">
<resultMap id="categoryResult" class="com.ibatis.example.Category"
groupBy="id">
<result property="id" column="CAT_ID"/>
<result property="description" column="CAT_DESCRIPTION"/>
<result property="productList" resultMap="ProductCategory.productResult"/>
</resultMap>
<resultMap id="productResult" class="com.ibatis.example.Product">
<result property="id" column="PRD_ID"/>
<result property="description" column="PRD_DESCRIPTION"/>
</resultMap>
<select id="getCategory" parameterClass="int" resultMap="categoryResult">
select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
from CATEGORY C
left outer join PRODUCT P
on C.CAT_ID = P.PRD_CAT_ID
where CAT_ID = #value#
</select>
</sqlMap>
When you call...
List myList = queryForList("ProductCategory.getCategory", new
Integer(1002));
...the main query is executed, and the results are stored in the myList
variable as beans of type
"com.ibatis.example.Category". Each object in that List will have a
"productList" property that is also a List
populated from the same query, but using the "productResult" result map
to populate the beans in the child
list. So, you end up with a list containing sub-lists, and only one
database query is executed.
The important items here are the...
groupBy="id"
...attribute and the...
<result property="productList" resultMap="ProductCategory.productResult"/>
...property mapping in the "categoryResult" result map. One other
important detail is that the result mapping
for the productList property is namespace aware - had it been simply
"productResult" it would not work.
Using this approach, you can solve any N+1 problem of any depth or breadth.
Which is basically the situation I have, except that my top level result
map refers to two other result maps, the second of which does not seem
to obey the groupBy property. Am I reading the documentation wrong?
Thanks.
Ben.
Larry Meadors wrote:
> That is what I'd expect. If you look at the results of the SQL, it
> becomes more apparent.
>
> Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
> do that join, you get 10 rows back - the first one 2 times, the second
> one 3 times, and the third one 5 times.
>
> Now, if those contacts have 5, 8, and 10 attributes, when you join the
> previous results (2+3+5=10 rows) with the attributes, you get 84 rows
> (2*5 + 3*8 + 5*10).
>
> What you are trying to do cannot be done in a single SQL statement,
> so..it can't be done in iBATIS with a single SQL statement..unless you
> use a row handler. I think you could make a row handler do that.
>
> Larry
>
>
> On 1/14/07, Ben Schmidt <[EMAIL PROTECTED]> wrote:
>> 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.
>>
