ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marty Tomasi" <Marty.Tom...@sas.com>
Subject Questions on N+1 and ParameterMap
Date Thu, 10 Mar 2005 01:20:41 GMT
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


Mime
View raw message