I'm sorry, I'm an idiot. I flew off the handle on that one & confused the old
"select" syntax with the new "groupBy" feature...
I believe you want the groupBy on your child ResultMap.
--- Karen Koch <[EMAIL PROTECTED]> wrote:
> You need two select statements, not a query with a join.
>
> Really, there are examples on the Wiki.
> --- Paul Barry <[EMAIL PROTECTED]> wrote:
>
> > 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]
> > >
> >
>
>
