You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Fábio Pisaruk <pi...@gmail.com> on 2007/09/14 16:29:03 UTC
GroupBy column insted of property doesn´t work
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
Re: GroupBy column insted of property doesn´t work
Posted by Lisa Jenkins <li...@investoranalytics.com>.
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
> <ma...@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
Re: GroupBy column insted of property doesn´t work
Posted by Fábio Pisaruk <pi...@gmail.com>.
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 <li...@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
Re: GroupBy column insted of property doesn´t work
Posted by Lisa Jenkins <li...@investoranalytics.com>.
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