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