ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marty Tomasi" <Marty.Tom...@sas.com>
Subject RE: Questions on N+1 and ParameterMap
Date Fri, 11 Mar 2005 01:52:32 GMT
That was the correct combination: 

- the groupBy must be on the parent resultMap
- the value of the groupBy attribute must be set to the value of the property on which the
grouping occurs

Thank you, Paul and Karen!

Marty

-----Original Message-----
From: Paul Barry [mailto:paul.barry@nyu.edu] 
Sent: Thursday, March 10, 2005 3:40 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Questions on N+1 and ParameterMap
Importance: Low

Karen is correct, once I changed my groupBy from "author.id" to "id", it works as expected.
 Marty, do you have groupBy="id" or groupBy="alert_id" on the findAlertsResult resultMap?
 I think it should be groupBy="id".

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 <Marty.Tomasi@sas.com> 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:paul.barry@nyu.edu]
>>Sent: Thursday, March 10, 2005 11:19 AM
>>To: ibatis-user-java@incubator.apache.org
>>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
>>>Marty.Tomasi@sas.com
>>>
>>
> 

Mime
View raw message