public class Author { private Long id;
private String name;
private List<Book> books;}
With getters and setters. Then I have a Book Object like this:
public class Book { private Long id;
private String title;
private Author author;}
Then I have authors and books database tables:
mysql> select * from authors; +----+---------------------+ | id | name | +----+---------------------+ | 1 | Charles Dickens | | 2 | Ernest Hemmingway | | 3 | William Shakespeare | +----+---------------------+ 3 rows in set (0.00 sec)
mysql> select * from books; +----+----------+---------------------------+ | id | authorId | title | +----+----------+---------------------------+ | 1 | 1 | Tale of Two Cites | | 2 | 1 | Oliver Twist | | 3 | 1 | Great Expectations | | 4 | 2 | For Whom the Bell Tolls | | 5 | 2 | Sun Also Rises | | 6 | 2 | The Old Man and The Sea | | 7 | 3 | Hamlet | | 8 | 3 | Othello | | 9 | 3 | A Midsummer Night's Dream | +----+----------+---------------------------+ 9 rows in set (0.00 sec)
Then I have this sqlmap:
<sqlMap>
<resultMap id="author" class="author" groupBy="author.id">
<result property="id" column="author.id"/>
<result property="name" column="author.name"/>
<result property="books" resultMap="book"/>
</resultMap> <resultMap id="book" class="book">
<result property="id" column="book.id"/>
<result property="title" column="book.title"/>
</resultMap> <select id="getAuthors" resultMap="author">
SELECT
a.id "author.id",
a.name "author.name",
b.id "book.id",
b.title "book.title"
FROM
authors a,
books b
WHERE
a.id = b.authorId
</select></sqlMap>
Here is what the query results look like:
mysql> SELECT
-> a.id "author.id",
-> a.name "author.name",
-> b.id "book.id",
-> b.title "book.title"
-> FROM
-> authors a,
-> books b
-> WHERE
-> a.id = b.authorId;
+-----------+---------------------+---------+--------------------------+
| author.id | author.name | book.id | book.title |
+-----------+---------------------+---------+--------------------------+
| 1 | Charles Dickens | 1 | Tale of Two Cites |
| 1 | Charles Dickens | 2 | Oliver Twist |
| 1 | Charles Dickens | 3 | Great Expectations |
| 2 | Ernest Hemmingway | 4 | For Whom the Bell Tolls |
| 2 | Ernest Hemmingway | 5 | Sun Also Rises |
| 2 | Ernest Hemmingway | 6 | The Old Man and The Sea |
| 3 | William Shakespeare | 7 | Hamlet |
| 3 | William Shakespeare | 8 | Othello |
| 3 | William Shakespeare | 9 | A Midsummer Night's Dream|
+-----------+---------------------+---------+--------------------------+
9 rows in set (0.00 sec)So when I use this in Java code, I should get 3 Author objects, each with 3 different Book objects in their books property, right? Well I get 9 objects, 1 book each. When I run this code:
List<Author> authors = sqlMap.queryForList("getAuthors",null);
System.out.println(authors.size());
for(Author author: authors) {
System.out.println(author.getName());
for(Book book: author.getBooks()) {
System.out.println(" "+book.getTitle());
}
}This is the output:
9
Charles Dickens
Tale of Two Cites
Charles Dickens
Oliver Twist
Charles Dickens
Great Expectations
Ernest Hemmingway
For Whom the Bell Tolls
Ernest Hemmingway
Sun Also Rises
Ernest Hemmingway
The Old Man and The Sea
William Shakespeare
Hamlet
William Shakespeare
Othello
William Shakespeare
A Midsummer Night's DreamI expected:
3
Charles Dickens
Tale of Two Cites
Oliver Twist
Great Expectations
Ernest Hemmingway
For Whom the Bell Tolls
Sun Also Rises
The Old Man and The Sea
William Shakespeare
Hamlet
Othello
A Midsummer Night's DreamWhat am I missing?
Marty Tomasi wrote:
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]
