ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lisa Jenkins <lisa.jenk...@investoranalytics.com>
Subject Re: GroupBy column insted of property doesn´t work
Date Fri, 14 Sep 2007 18:44:54 GMT
Ah, then why have it in your select -- select 
p.person_id,p.first_name,p.last_name,a.address,a.city,a.country ??  I 
think, based on the documentation I've seen, the groupBy only works if 
the groupBy column referenced is a result property column in the 
resultMap, which may be your problem. 

from the ibatis documentation...

Avoiding N+1 Selects (1:M and M:N)

This is similar to the 1:1 situation above, but is of even greater 
concern due to the potentially large amount
of data involved.  The problem with the solution above is that whenever 
you load a Category, two SQL
statements are actually being run (one for the Category and one for the 
list of associated Products).  This
problem seems trivial when loading a single Category, but if you were to 
run a query that loaded ten (10)
Categories, a separate query would be run for each Category to load its 
associated list of Products.  This
results in eleven (11) queries total: one for the list of Categories and 
one for each Category returned to load
each related list of Products (N+1 or in this case 10+1=11).  To make 
this situation worse, we’re dealing
with potentially large lists of data.


1:N & M:N Solution

iBATIS fully solves the N+1 selects solution.  Here is an example:


<sqlMap namespace="ProductCategory">

<resultMap id=”categoryResult” class=”com.ibatis.example.Category” 
groupBy=”id”>
<result property=”id” column=”CAT_ID”/>
<result property=”description” column=”CAT_DESCRIPTION”/>
<result property=”productList” resultMap=”ProductCategory.productResult”/>
</resultMap>

<resultMap id=”productResult” class=”com.ibatis.example.Product”>
<result property=”id” column=”PRD_ID”/>
<result property=”description” column=”PRD_DESCRIPTION”/>
</resultMap>

<select id=”getCategory”  parameterClass=”int” resultMap=”categoryResult”>
select C.CAT_ID,  C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
from CATEGORY C
left outer join PRODUCT P
on C.CAT_ID = P.PRD_CAT_ID
where CAT_ID = #value#
</select>

</sqlMap>

Fábio Pisaruk wrote:
> Okay Lisa tks
>
> But remember that the bean Person doesn´t have personId property and i 
> don´t think adding it to be a good idea because person_id is an idea 
> concerning data base not my bean or the bussinesses rules.
>
> regards
>
> On 9/14/07, *Lisa Jenkins* <lisa.jenkins@investoranalytics.com 
> <mailto:lisa.jenkins@investoranalytics.com>> wrote:
>
>     person_id column is not referenced is not in the resultMap, if you add
>     that, it should work....
>
>
>     <resultMap id="resulMap-person" class="Person" groupBy="person_id">
>     <result property="personId" column="person_id"/>
>         <result property="firstName" column="first_name"/>
>         <result property="lasttName" column="last_name"/>
>         <result property="addresses" resultMap="resultMap-adress"/>
>     </resultMap>
>
>     Fábio Pisaruk wrote:
>     > Hi,
>     >
>     > Is there a way to use groupBy with a column name?
>     > I need to do so 'cause there is no property that uniquely
>     identify the
>     > bean
>     > and i am not able to change it do add one.
>     > For example:
>     > Suppose i´ve got two tables: Person and Address.
>     >
>     > Person:
>     >     person_id
>     >     first_name
>     >     last_name
>     >
>     > Address:
>     >     person_id
>     >     address_id
>     >     address
>     >     country
>     >     city
>     >
>     > And two beans:
>     > Person
>     >     firstName
>     >     lastName
>     >     addresses(Address[])
>     > Address
>     >     address
>     >     country
>     >     city
>     >
>     > My maps:
>     >
>     > <resultMap id="resulMap-person" class="Person" groupBy="person_id">
>     >     <result property="firstName" column="first_name"/>
>     >     <result property="lasttName" column="last_name"/>
>     >     <result property="addresses" resultMap="resultMap-adress"/>
>     > </resultMap>
>     >
>     > <resultMap id="resulMap-address" class="Address">
>     >     <result property="address" column="address"/>
>     >     <result property="country" column="country"/>
>     >     <result property="city" column="city"/>
>     > </resultMap>
>     >
>     > My sql:
>     >
>     > <select id="get-person-by-id" parameterClass="int"
>     > resultMap="resulMap-person">
>     >     select
>     p.person_id,p.first_name,p.last_name,a.address,a.city,a.country
>     >     from Person p ,Address a
>     >     where p.person_id=#value# and p.person_id=a.person_id
>     > </select>
>     >
>     > In doing so i am not getting the desired result.
>     > Person information are replicated for each address it contains.
>     >
>     > PS: I know two workarounds that i don´t consider good solutions:
>     > 1-) Creating a wrapperPerson with a person_id attribute and having
>     > Ibatis grouping result on it or
>     > 2-) using a nested select to get address for each person:
>     >      <result property="addresses"
>     select="get-addresses-by-person_id"
>     > column="person_id"/>
>     > <select id="get-addresses-by-person_id" parameterClass="int"
>     > resultMap="resultMap-adress">
>     >     select * from Address where person_id=#value#
>     > </select>
>     >
>     > Thanks in advance
>     >
>     > --
>     > Visto como se não executa logo a sentença sobre a má obra, o coração
>     > dos filhos dos homens está inteiramente disposto a praticar o mal.
>     >
>     >
>     > --Nerd´s sign
>     >
>     > If you have four classes, Everybody, Somebody, Anybody, and
>     Nobody, if
>     > Somebody has a bug, it could be Anybody 's fault but Nobody really
>     > knows, while Everybody shares responsibility.
>     >
>     > "Programming today is a race between software engineers striving to
>     > build bigger and better idiot-proof programs, and the universe
>     trying
>     > to build bigger and better idiots. So far, the universe is
>     winning." -
>     > Rick Cook
>
>
>
>
> -- 
> Visto como se não executa logo a sentença sobre a má obra, o coração 
> dos filhos dos homens está inteiramente disposto a praticar o mal.
>
>
> --Nerd´s sign
>
> If you have four classes, Everybody, Somebody, Anybody, and Nobody, if 
> Somebody has a bug, it could be Anybody 's fault but Nobody really 
> knows, while Everybody shares responsibility.
>
> "Programming today is a race between software engineers striving to 
> build bigger and better idiot-proof programs, and the universe trying 
> to build bigger and better idiots. So far, the universe is winning." - 
> Rick Cook 


Mime
View raw message