Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 54066 invoked from network); 20 Feb 2007 17:11:30 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 20 Feb 2007 17:11:30 -0000 Received: (qmail 41880 invoked by uid 500); 20 Feb 2007 17:11:36 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 41866 invoked by uid 500); 20 Feb 2007 17:11:35 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 41844 invoked by uid 99); 20 Feb 2007 17:11:35 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Feb 2007 09:11:35 -0800 X-ASF-Spam-Status: No, hits=2.1 required=10.0 tests=HTML_MESSAGE,HTML_TAG_EXIST_TBODY,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of jeffgbutler@gmail.com designates 64.233.184.236 as permitted sender) Received: from [64.233.184.236] (HELO wr-out-0506.google.com) (64.233.184.236) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Feb 2007 09:11:25 -0800 Received: by wr-out-0506.google.com with SMTP id 36so2119897wra for ; Tue, 20 Feb 2007 09:11:04 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=a3m0doqWVeGJygXZhK2zyx3DVmxeWuU8RnhlR/yRMZh8/f3X+2WhHftNyte9lMz+wQOdagFoGTmengMyCb/Zl7yvHnYMz4+VKlfwuWVS8QDj38wbhmZexAo6vAbhLRiqc/s7oEHKkgKT7Env5iEcPW66KD48uKDMEJgnJRG/Rds= Received: by 10.114.110.1 with SMTP id i1mr3413248wac.1171991460883; Tue, 20 Feb 2007 09:11:00 -0800 (PST) Received: by 10.115.111.7 with HTTP; Tue, 20 Feb 2007 09:10:59 -0800 (PST) Message-ID: Date: Tue, 20 Feb 2007 11:10:59 -0600 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: Problem with several one to many relationships in breadth In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_67400_26141949.1171991459981" References: X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_67400_26141949.1171991459981 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Interesting.... In my tests, it fails in BOTH versions (2.1.7 and 2.3.0), but in different ways. So something definitely changed. In my test, 2.3.0 is closer to correct than 2.1.7 is. But the important truth is that it does fail in both versions. For now, I think a row handler is the best way to go forward with this. I'll open a JIRA ticket with my test case, so everyone can see it. No guarantees about a fix, but at least it will be logged. Jeff Butler On 2/20/07, Jeff Butler wrote: > > I''m surprised by this (obviously). I'll take a look. It probably has to > do with the new support for multiple result sets. > > Jeff Butler > > > > On 2/20/07, Claudio Iacovozzi wrote: > > > > I tested my given example with iBatis 2.1.7.597 and it works! Ibatis > > creates the expected bean: > > Agent1 --> List {1:Customer1, 3:Customer2} > > --> List {1:housebreaking, 2:damage, 4:theft} > > > > Further tests with more depth (e.g. Custommer containing adresses) or > > breadth (Agent containing additional List of addresses) were also > > successful. > > > > So Tim ist right and something in iBatis must have been changed. We > > currently use version 2.2.0.638 / 2.3.0.677. > > > > => so what's the best solution if we still want to use the newest > > version? Should we implement our own row handler and how can that be done? > > Or is it possible that the behaviour of 2.1.7.597 will be restored? > > > > > > > > > > > > > > > > 2007/2/19, Jeff Butler : > > > > > > That's a good solution - then you could deal with object identity > > > yourself. > > > > > > Jeff Butler > > > > > > > > > On 2/19/07, Larry Meadors wrote: > > > > > > > > ..or you could use a rowhandler. > > > > > > > > Larry > > > > > > > > > > > > On 2/19/07, Jeff Butler wrote: > > > > > > > > > > iBATIS groupBy support does not support two independant lists. > > > > > This would be a very difficult problem to solve and would require us to deal > > > > > with object identity - which we don't deal with now. > > > > > > > > > > So with groupBy, you can have one and only one list that is > > > > > automatically populated. Other lists will need to be populated with a > > > > > nested select. > > > > > > > > > > Jeff Butler > > > > > > > > > > > > > > > > > > > > On 2/19/07, Tim.Hammar@mcnichols.com < Tim.Hammar@mcnichols.com > > > > > > wrote: > > > > > > > > > > > > > > > > > > Greetings Claudio, > > > > > > > > > > > > We too have this problem with the latest two releases. If you > > > > > > use version 2.1.7.597 you should have no problems - hopefully > > > > > > you have not implemented any new features like we had (and had to scramble > > > > > > to undo). Versions 2.2 and 2.3 will not handle what you are > > > > > > trying to do. I have been so swamped that I have been unable to generate a > > > > > > test case and submit a bug report. Maybe your example will suffice. > > > > > > > > > > > > Regards, > > > > > > Tim Hammar > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > *"Claudio Iacovozzi" * > > > > > > > > > > > > 02/19/2007 06:51 AM Please respond to > > > > > > user-java@ibatis.apache.org > > > > > > > > > > > > To > > > > > > user-java@ibatis.apache.org cc > > > > > > > > > > > > Subject > > > > > > Problem with several one to many relationships in breadth > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > We have a problem filling objects with several '1 to many > > > > > > relationships' in breadth from one single SQL statement. > > > > > > > > > > > > On page 40 of the iBatis Data Mapper Developer Guide the > > > > > > 'groupBy' > > > > > > construct is described as follows: > > > > > > "Using this approach, you can solve any N+1 problem of any depth > > > > > > or breadth." > > > > > > > > > > > > This just seems to work for any depth but NOT for any *breadth*. > > > > > > > > > > > > Example: > > > > > > > > > > > > An insurance agent contains a list of customers as well as a > > > > > > list of > > > > > > insured events. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > - The used SQL statement: > > > > > > > > > > > > > > > > > > > > > > > > - The returned result set: > > > > > > agentId,agent_firstname,agent_lastname,eventId,title,customerId,customer_firstname,customer_lastname > > > > > > > > > > > > > > > > > > {1,Agent1,Smith,1,housebreaking,1,Customer1,Johnson} > > > > > > {1,Agent1,Smith,4,theft,1,Customer1,Johnson} > > > > > > {1,Agent1,Smith,2,damage,3,Customer2,Miller} > > > > > > > > > > > > > > > > > > What we would like to achieve is getting a bean of type Agent, > > > > > > which > > > > > > contains two lists of all its customers and all its associated > > > > > > insured events: > > > > > > > > > > > > Agent1 --> List {1:Customer1, 3:Customer2} > > > > > > --> List {1:housebreaking, 2:damage, 4:theft} > > > > > > > > > > > > > > > > > > > > > > > > But iBatis creates a bean like this: > > > > > > Agent1 --> List {1: Customer1, 1:Customer1, 3: > > > > > > Customer2} > > > > > > --> List {1:housebreaking, 2:damage, 4:theft} > > > > > > > > > > > > > > > > > > We want to avoid several SQL statements to get the necessary > > > > > > data (one > > > > > > to get all events, and one to get all customers). > > > > > > > > > > > > Any solution ? > > > > > > > > > > > > > > > > > > > > > > > > > > > ------=_Part_67400_26141949.1171991459981 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Interesting....
 
In my tests, it fails in BOTH versions (2.1.7 and 2.3.0), but in different ways.  So something definitely changed.  In my test, 2.3.0 is closer to correct than 2.1.7 is.
 
But the important truth is that it does fail in both versions.
 
For now, I think a row handler is the best way to go forward with this.
 
I'll open a JIRA ticket with my test case, so everyone can see it.  No guarantees about a fix, but at least it will be logged.
 
Jeff Butler
 

 
On 2/20/07, Jeff Butler <jeffgbutler@gmail.com> wrote:
I''m surprised by this (obviously).  I'll take a look.  It probably has to do with the new support for multiple result sets.
 
Jeff Butler


 
On 2/20/07, Claudio Iacovozzi <claudio.iacovozzi@googlemail.com > wrote:
I tested my given example with iBatis 2.1.7.597 and it works! Ibatis creates the expected bean:
Agent1  --> List<Customer> {1:Customer1, 3:Customer2}
        --> List<Event> {1:housebreaking, 2:damage, 4:theft}
 
Further tests with more depth (e.g. Custommer containing adresses) or breadth (Agent containing additional List of addresses) were also successful.
 
So Tim ist right and something in iBatis must have been changed. We currently use version 2.2.0.638 / 2.3.0.677.
 
=> so what's the best solution if we still want to use the newest version? Should we implement our own row handler and how can that be done?
Or is it possible that the behaviour of 2.1.7.597 will be restored?
 
 

 



 
2007/2/19, Jeff Butler <jeffgbutler@gmail.com>:
That's a good solution - then you could deal with object identity yourself.
 
Jeff Butler

 
On 2/19/07, Larry Meadors <lmeadors@apache.org > wrote:
..or you could use a rowhandler.

Larry



On 2/19/07, Jeff Butler <jeffgbutler@gmail.com > wrote:
iBATIS groupBy support does not support two independant lists.  This would be a very difficult problem to solve and would require us to deal with object identity - which we don't deal with now.
 
So with groupBy, you can have one and only one list that is automatically populated.  Other lists will need to be populated with a nested select.
 
Jeff Butler


 
On 2/19/07, Tim.Hammar@mcnichols.com < Tim.Hammar@mcnichols.com > wrote:

Greetings Claudio,

We too have this problem with the latest two releases.  If you use version 2.1.7.597 you should have no problems - hopefully you have not implemented any new features like we had (and had to scramble to undo).  Versions 2.2 and 2.3 will not handle what you are trying to do.  I have been so swamped that I have been unable to generate a test case and submit a bug report.  Maybe your example will suffice.

Regards,
Tim Hammar




"Claudio Iacovozzi" <claudio.iacovozzi@googlemail.com >

02/19/2007 06:51 AM
Please respond to
user-java@ibatis.apache.org

To
 user-java@ibatis.apache.org
cc
Subject
 Problem with several one to many relationships in breadth





We have a problem filling objects with several '1 to many
relationships' in breadth from one single SQL statement.

On page 40 of the iBatis Data Mapper Developer Guide the 'groupBy'
construct is described as follows:
"Using this approach, you can solve any N+1 problem of any depth or breadth."

This just seems to work for any depth but NOT for any *breadth*.

Example:

An insurance agent contains a list of customers as well as a list of
insured events.

<resultMap class="agent" id="agent" groupBy="id" >
<result property="id" column="agentId" />
 <result property="firstName" column="agent_firstname" />
 <result property="lastName" column="agent_lastname" />
 <result property="events" resultMap="event" />
 <result property="customers" resultMap="customer" />
</resultMap>

<resultMap class="event" id="event" groupBy="id" >
 <result property="id" column="eventId" />
 <result property="title" column="title" />
</resultMap>

<resultMap class="customer" id="customer" groupBy="id" >
<result property="id" column="customerId" />
 <result property="firstName" column="customer_firstname" />
 <result property="lastName" column="customer_lastname" />
 <result property="street" column="street" />
 <result property="city" column="city" />
</resultMap>

- The used SQL statement:

<select id="getAgent" resultMap="agent" parameterClass="int">
SELECT a.agentId, a.FirstName as agent_firstname, a.LastName as
agent_lastname, b.eventId, b.title,
c.customerId, c.FirstName as customer_firstname, c.LastName as
customer_lastname, c.street, c.city

FROM insurance_agent a, agenteventlink ab, insured_event b, customer c
WHERE a.agentId = ab.agentId
AND ab.eventId = b.eventId
AND b.customerId = c.customerId
AND a.agentId = #id#

ORDER BY a.agentId,  c.customerId
</select>

- The returned result set:
agentId,agent_firstname,agent_lastname,eventId,title,customerId,customer_firstname,customer_lastname

{1,Agent1,Smith,1,housebreaking,1,Customer1,Johnson}
{1,Agent1,Smith,4,theft,1,Customer1,Johnson}
{1,Agent1,Smith,2,damage,3,Customer2,Miller}


What we would like to achieve is getting a bean of type Agent, which
contains two lists of all its customers and all its associated
insured events:

Agent1  --> List<Customer> {1:Customer1, 3:Customer2}
            --> List<Event> {1:housebreaking, 2:damage, 4:theft}
               

But iBatis creates a bean like this:
Agent1  --> List<Customer> {1: Customer1, 1:Customer1, 3: Customer2}
           --> List<Event> {1:housebreaking, 2:damage, 4:theft}


We want to avoid several SQL statements to get the necessary data (one
to get all events, and one to get all customers).

Any solution ?







------=_Part_67400_26141949.1171991459981--