ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Karen Koch <kmk...@sbcglobal.net>
Subject Re: Questions on N+1 and ParameterMap
Date Thu, 10 Mar 2005 16:23:27 GMT
You need two select statements, not a query with a join.

Really, there are examples on the Wiki.
--- Paul Barry <paul.barry@nyu.edu> 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
> > Marty.Tomasi@sas.com
> > 
> 


Mime
View raw message