ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Claudio Iacovozzi" <claudio.iacovo...@googlemail.com>
Subject Problem with several one to many relationships in breadth
Date Mon, 19 Feb 2007 11:49:51 GMT
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 ?

Mime
View raw message