Karen Koch wrote:
Is your groupBy referring to the parent Java object's property name rather than the database column name? I see that Paul's example is using the database column name, which is not right -- and perhaps, rather than raising an error, the grouping simply does not happen in that case.
--- Marty Tomasi <[EMAIL PROTECTED]> wrote:
Just to follow up, Paul's suggestion to move the "groupBy" from the criteria resultMap did correct the problem I had with the null values showing up on the List of AlertCriteria. The groupBy needs to be on the parent resultMap. My results now match what Paul outlines more succinctly below.
That is, my case still returns more objects than expected.
Still puzzled as to what to try next,
Marty
P.S. Thanks, Paul.
-----Original Message-----
From: Paul Barry [mailto:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 11:19 AM
To: [email protected]
Subject: Re: Questions on N+1 and ParameterMap
Importance: Low
I tried to make a simple example myself using the new groupBy feature and I think I am having a problem similar to Marty's. I created a simple "author has many books" example. I have an Author Object like this:
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 Dream
I 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 Dream
What 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]
