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
[EMAIL PROTECTED]
