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 14:43:32 GMT
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