ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Barry <paul.ba...@nyu.edu>
Subject Re: Questions on N+1 and ParameterMap
Date Thu, 10 Mar 2005 15:08:45 GMT
Here is an example:

http://article.gmane.org/gmane.comp.java.ibatisdb.user/383

 From comparing this example to your original problem Marty, looks like 
you have the groupBy on the wrong resultMap.  I think you want this:

<resultMap id="findAlertsResult" class="alert" groupBy="id">
   <result property="id" column="ALERT_ID"/>
   <result property="eventName" column="EVENT_NAME"/>
   <result property="subscriber" column="SUBSCRIBER"/>
   <result property="active" column="ACTIVE_STATE"/>
   <result property="criteriaList" resultMap="Alerts.criteriaResult"/>
</resultMap>

<resultMap id="criteriaResult" class="alertCriteria">
   <result property="alertId" column="ALERT_ID"/>
   <result property="attribute" column="ATTRIBUTE"/>
   <result property="operator" column="OPERATOR"/>
   <result property="value" column="VALUE"/>
   <result property="type" column="TYPE"/>
</resultMap>


Paul Barry wrote:
> Sounds like as of 2.0.9 there is a way to do what Marty is asking:
> 
> # Solution for N+1 selects for 1:M and M:N. Version 2.0 was always 
> designed for this, I just didn't have a chance to implement it, until 
> now. It's made possible by two small additions to the mappings
> 
>     * <resultMap...groupBy="prop1,prop2">
>     * <result...resultMap="subResultMapForCollectionItems">
> 
> Is there any documentation or examples about the "groupBy" property?
> 
> 
> James, Steven wrote:
> 
>> hi Marty,
>>
>> Your query is not in the right context and is doing what you ask. You 
>> are doing a join and as you have to records in the sub table that meet 
>> the requirments of your join ie WHERE A.ALERT_ID=AC.ALERT_ID then two 
>> rows are returned. This is only good for N:1 situations.
>> You have a N:M situation. Easiest solution do a follow up query see 
>> page 26 0f 53 in ibatis-sqlmap.pdf file. ie.
>>   <result property="criteriaList" select="getMyAlertCriteia"/>
>>
>> This will envolve a second hit on your database.
>> other option use a lazy loading technique to only get the data when 
>> you need it.
>> Good luck
>> steve..
>>
>>
>> -----Original Message-----
>> From: Marty Tomasi [mailto:Marty.Tomasi@sas.com]
>> Sent: Thu 3/10/2005 1:20 AM
>> To: ibatis-user-java@incubator.apache.org
>> Subject: Questions on N+1 and ParameterMap
>>  
>> Hi,
>> First off, excellent work to date on iBATIS. It's been a real treat to 
>> work with so far. I could go on, but....
>>
>> I have some questions. Note that I am using the latest 2.0.9B code 
>> when running this code.
>>
>> Given two tables with the following:
>>
>> Table: ALERTS +----------+----------------+------------+--------------+
>> | alert_id | event_name     | subscriber | active_state |
>> +----------+----------------+------------+--------------+
>> | 1001     | ContentAdded   | demouser   |            1 |
>> | 1002     | ContentUpdated | demouser   |            1 |
>> +----------+----------------+------------+--------------+
>>
>> Table: ALERT_CRITERIA
>> +----------+-----------+----------+---------------+------+
>> | alert_id | attribute | operator | value         | type |
>> +----------+-----------+----------+---------------+------+
>> | 1001     | Document  |        4 | D003EFF4C0099 |   12 |
>> | 1002     | Document  |        4 | C900EF808099C |   12 |
>> | 1002     | Status    |        4 | 200           |    8 |
>> +----------+-----------+----------+---------------+------+
>>
>> There are two Java beans, Alert and AlertCriteria with the appropriate 
>> member fields. Pretty standard stuff, other than the fact that Alert 
>> also has a List of AlertCriteria objects; hence the 1:N relationship.
>>
>> Here is the SQL Mapping:
>>
>> <sqlMap namespace="Alerts">
>>
>>   <typeAlias alias="alert" type="com.myorg.alerts.Alert"/>
>>   <typeAlias alias="alertCondition" 
>> type="com.myorg.alerts.AlertCriteria"/>
>>   <typeAlias alias="alertParam" 
>> type="com.myorg.alerts.AlertParameterMap"/>
>>
>>   <resultMap id="criteriaResult" class="alertCriteria" groupBy="alertId">
>>     <result property="alertId" column="ALERT_ID"/>
>>     <result property="attribute" column="ATTRIBUTE"/>
>>     <result property="operator" column="OPERATOR"/>
>>     <result property="value" column="VALUE"/>
>>     <result property="type" column="TYPE"/>
>>   </resultMap>   
>>     
>>   <resultMap id="findAlertsResult" class="alert">
>>     <result property="id" column="ALERT_ID"/>
>>     <result property="eventName" column="EVENT_NAME"/>
>>     <result property="subscriber" column="SUBSCRIBER"/>
>>     <result property="active" column="ACTIVE_STATE"/>
>>     <result property="criteriaList" resultMap="Alerts.criteriaResult"/>
>>   </resultMap>   
>>     
>>   <parameterMap id="alertParamMap" class="alertParam">
>>     <parameter property="eventName"/>
>>     <parameter property="attributes"/>
>>   </parameterMap>
>>     
>>   <select id="findActiveAlerts" parameterMap="alertParamMap"           
>> resultMap="Alerts.findAlertsResult">
>>      SELECT A.*, AC.* FROM ALERTS A, ALERT_CRITERIA AC
>>      WHERE A.ALERT_ID=AC.ALERT_ID
>>      AND A.EVENT_NAME=#eventName#
>>      AND A.ACTIVE_STATE=1
>>      <iterate prepend="AND" property="attributes"          open="(" 
>> close=")" conjunction="OR">
>>        AC.ATTRIBUTE=#attributes[]#
>>      </iterate>   
>>   </select>
>> . . .
>> </sqlMap>
>>
>> The AlertParameterMap is a parameter mapping used so that 1 to n 
>> "attributes" can be passed in to the "findActiveAlerts" query. Its 
>> attributes property is a List, which is then used in the <iterate> 
>> element of the query. (Nice feature, btw.)
>>
>> Here's what happens. Consider a query where the AlertParameterMap 
>> contains a single attribute (attributes.size() = 1) and the 
>> eventName="ContentAdded". When I execute the queryForList() with that 
>> parameter map, I get back a single Alert. Makes sense because the SQL 
>> returns:
>> +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>> | ALERT_ID | EVENT_NAME   | SUBSCRIBER | ACTIVE_STATE | ALERT_ID | 
>> ATTRIBUTE | OPERATOR | VALUE         | TYPE |
>> +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>> | 1001     | ContentAdded | demouser   |            1 | 1001     | 
>> Document  |        4 | D003EFF4C0099 |   12 |
>> +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>>
>> The List returned by queryForList() contains a single Alert object, as 
>> expected. The List of AlertCriteria on the Alert object has a single 
>> object as well.
>>
>> However, when I try a query where the AlertParameterMap contains 
>> multiple attributes (attributes.size() > 1) using, for example, 
>> eventName="ContentUpdated", the SQL returns:
>> +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>> | ALERT_ID | EVENT_NAME     | SUBSCRIBER | ACTIVE_STATE | ALERT_ID | 
>> ATTRIBUTE | OPERATOR | VALUE         | TYPE |
>> +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>> | 1002     | ContentUpdated | demouser   |            1 | 1002     | 
>> Document  |        4 | C900EF808099C |   12 |
>> | 1002     | ContentUpdated | demouser   |            1 | 1002     | 
>> Status    |        4 | 200           |    8 |
>> +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+

>>
>>
>> The List returned by queryForList() contains two (2) Alert objects 
>> instead of the expected one (based on the id 1002 in this example). 
>> Further, the first Alert object has a List of AlertCriteria with a 
>> single AlertCriteria (where attribute="Document", etc.). The second 
>> Alert object has a null value for the AlertCriteria.
>>
>> The expected result is a single Alert object whose criteriaList is 
>> size()=2 with both criteria (attribute="Document" and 
>> attribute="Status") present.
>>
>> Any thoughts or suggestions as to what is incorrect? Is the parameter 
>> map not working as I expect? Are my expectations way off base?
>> Any insight would be greatly appreciated.
>>
>> Thanks,
>>
>> Marty
>>
>> -------------------
>> Marty Tomasi
>> Marty.Tomasi@sas.com
>>
>>
>>
>> This e-mail and any attachment is for authorised use by the intended 
>> recipient(s) only. It may contain proprietary material, confidential 
>> information and/or be subject to legal privilege. It should not be 
>> copied, disclosed to, retained or used by, any other party. If you are 
>> not an intended recipient then please promptly delete this e-mail and 
>> any attachment and all copies and inform the sender. Thank you.

Mime
View raw message