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] >
