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 Alan Chandler <al...@chandlerfamily.org.uk> on 2005/08/24 14:09:49 UTC
Confused with groupBy
Hi
I am new to this list and iBATIS, but have started to use it (embedded
within the tapestry framework) for building a small database application at
home.
I have read the developers guide, in an attempt to get straight how I am
going to use iBATIS to meet my needs, and most of it seems very clear.
However I have got a little stuck understanding properly how to deal with
complex joins. In particular, the example given on page 28/29 of the
developer guide and the use of the "groupBy" attribute to the resultMap
element. I assume the "quarter" it is refering to is mapped to the
QuarterNumber column of the BroadcastDate table, but from there on in I am
lost. [It doesn't help that my sql knowledge is not 100%, so a select
distinct with a group by which may or may not match the simplified groupBy
element in the xml is also confusing me a bit].
In an attempt to find out more, I thought the dtd might have some comments,
and downloaded the one refered to at the head of page 13.
(sql-map-config-2.dtd). However, I could find no reference the resultMap
element.
In the end, a little digging on http://ibatis.apache.org/dtd/ (ie getting a
directory listing) revealed that there is a different dtd sql-map-2.dtd.
So
1) I think there is a bug in the guide refering to the wrong dtd. Am I
correct? [and I don't mean the different domain name either - but I assume
that in the longer term that it would be more correct to use the
ibatis.apache.org name rather than the www.ibatis.com]
2) Could someone give me a more detailed explanation of what the groupBy
attribute is trying to do.
3) Why is there a namespace attribute on the sqlMap element and what does it
do. I haven't been able to find any previous (to page 28) reference in the
guide.
Thanks.
--
Alan Chandler
alan@chandlerfamily.org.uk
RE: Confused with groupBy
Posted by Brian Yoffe <by...@houston.rr.com>.
Many thanks for the response Prashanth. I done as you recommended, and the
result is that "Mike" from the example below has an order that, while
logically null and should not exists at all, contains all zeros and empty
strings. In my setOrders method, I can certainly remove this result.
I've even gone so far as to add a pseudo-column called "isNullRecord" that
is populated by a true/false. It seems like there should be/is a feature
to do this for me - leaking the need to collapse the logically null object
into my domain object is not desirable.
Thanks,
Brian
-----Original Message-----
From: Prashanth Sukumaran [mailto:prashanthsukumaran@yahoo.com]
Sent: Friday, August 26, 2005 10:38 PM
To: user-java@ibatis.apache.org
Subject: RE: Confused with groupBy
Hi Brian,
If you are getting a NullPointerException then
Add this
javaType="int" jdbcType="NUMERIC" nullValue="0"
to your resultMap for ORDER_ID and AMOUNT.
This is the worst possible error caused due to iBatis. If the bean has
primitive member
variable(int, float etc) and you try to assign a null value to it. It
throws a nullpointer
exception. For a newcomer using iBatis, he/she will not have a clue why this
is happenning. I
think we need a fix for this, so we atleast throw a different kind of
exception instead of a
NullPointerException.
I am sure taking into consideration all the people using iBatis, this
constitues to 100 - 1000
hours wasted.
Rgds
Prashanth Sukumaran.
--- Brian Yoffe <by...@houston.rr.com> wrote:
> No, actually I don't think it's straightforward at all - let me make my
> example more concrete.
>
> Here's an example data set:
>
> ACCOUNTS TABLE:
>
> ACCOUNT_ID NAME
> 500 Jim
> 501 Bob
> 502 Mike
>
>
> ORDERS TABLE:
> ORDER_ID ACCOUNT_ID AMOUNT
> 100 500 1000
> 101 501 2000
> 102 501 3000
>
> From this data set, we can see that Jim has placed one order, Bob has
placed
> two orders, and Mike has placed no orders. Now, I can solve this problem
> quite easily if I don't mind performing N+1 selects.
>
> My first SQL statement looks like this:
>
> select ACCOUNT_ID, NAME from ACCOUNTS
>
> and my second select looks like this:
>
> select ORDER_ID, AMOUNT from ORDERS where ACCOUNT_ID=#value#.
>
> I will not show the actual ibatis mapping, but lets assume I will use the
> technique whereby I map the orders list bean property to the result of
> separate select. Again, this method WILL cause N+! selects to be
> performed. I can turn on lazy loading and they they do not all occur at
> once, but N+! do occur.
>
>
>
> So, lets assume I try to resolve the N+1 selects problem by using the
> technique described below. Now, let's consider what the SQL looks like.
> I'll first try:
>
> select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
> from ACCOUNTS a, ORDERS o
> where a.account_id = o.account_id
>
> The result set is:
> ACCOUNT_ID NAME ORDER_ID AMOUNT
> 500 Jim 100 1000
> 501 Bob 101 2000
> 501 Bob 101 3000
>
>
>
> Ok, so after using a regular join, Mike completely dropped out of the join
> (just as we all knew it was). Strike 2. So, let me change the sql to
an
> outer join:
>
> select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
> from ACCOUNTS a, ORDERS o
> where a.account_id = o.account_id (+)
>
> The result set is:
> ACCOUNT_ID NAME ORDER_ID AMOUNT
> 500 Jim 100 1000
> 501 Bob 101 2000
> 501 Bob 101 3000
> 502 Mike [null] [null]
>
> Fantastic! I have the result set that I want. Now, how do I keep
iBatis
> from bombing when it encounters the nulls and tries to map that onto an
> order object. Essentially, I want an account object created for Mike I
> expect my code to create a new list just as you recommended, Huy.
>
> Hope that clears up my question.
>
> Thanks,
> Brian
>
> -----Original Message-----
> From: Huy Do [mailto:contactme42@gmail.com]
> Sent: Wednesday, August 24, 2005 8:24 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Confused with groupBy
>
> Shouldn't you do that yourself in the POJO ? If you assume this behavour
> in your apps, it wouldn't be a good idea to rely on ibatis to give it to
> you. What if you create the pojo manually ? then you wouldn't get the
> empty list. Something like
>
> if (myList == null) {
> myList = new List()
> }
> return myList
>
> is quite straightforward isnt it ?
>
> Huy
>
> >I hate to hijack Alan's question, but...
> >
> >I've been playing around with group by as well. I fully understand the
> N+1
> >selects problem and understand how groupBy works.
> >
> >Lets simplify your problem just a simple relationship - say account to
> order
> >(disregarding line items).
> >
> >Now if an account has no orders, everything, including account drops out
of
> >the join. This is obviously SQL behavior, not iBatis specific. So, I
> >propose to solve my SQL problem by using an outer join. So, now I get
all
> >accounts even those that do not have line items. Is there a way to get
> >ibatis to create an empty list of orders for those accounts where there
> >exist no orders?
> >
> >Thanks,
> >Brian Yoffe
> >
> >-----Original Message-----
> >From: Larry Meadors [mailto:larry.meadors@gmail.com]
> >Sent: Wednesday, August 24, 2005 11:33 AM
> >To: user-java@ibatis.apache.org
> >Subject: Re: Confused with groupBy
> >
> >You got it exactly right. :-)
> >
> >Larry
> >
> >
> >On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> >
> >
> >>Larry Meadors writes:
> >>
> >>...
> >>
> >>
> >>>The groupBy attribute is used to deal with the "N+1" selects problem.
> >>>The "N+1 Selects" problem is caused by trying to load child records
> >>>that are related to a list of parent records. So, if you run one query
> >>>to get the parent records, and there are some number "N" of them, then
> >>>you have to run "N" more queries to get the child records for the
> >>>parent records resulting in "N+1 selects".
> >>>
> >>>
> >>Yes I FULLY understand the above
> >>
> >>
> >>
> >>
> >>>Here is another example that does an Account -> Order -> OrderItem
> >>>mapping using this technique:
> >>>
> >>> <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> >>> groupBy="account.accountId" >
> >>> <result property="account.accountId" column="accountId" />
> >>> <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> >>> </resultMap>
> >>>
> >>> <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> >>> groupBy="order.orderId" >
> >>> <result property="order.orderId" column="orderId" />
> >>> <result property="orderItemList"
resultMap="Ch7.ResultOrderItemNMap"
> >>>
> >>>
> >/>
> >
> >
> >>> </resultMap>
> >>>
> >>> <resultMap id="ResultOrderItemNMap" class="OrderItem">
> >>> <result property="orderId" column="orderId" />
> >>> <result property="orderItemId" column="orderItemId" />
> >>> </resultMap>
> >>>
> >>> <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> >>> select
> >>> account.accountId as accountid,
> >>> orders.orderid as orderid,
> >>> orderitem.orderitemid as orderitemid
> >>> from account
> >>> join orders on account.accountId = orders.accountId
> >>> join orderitem on orders.orderId = orderitem.orderId
> >>> order by accountId, orderid, orderitemid
> >>> </select>
> >>>
> >>>
> >>I may be a little dense here - can I just confirm. In class
> >>
> >>
> >"AccountInfo",
> >
> >
> >>you have a property called "account" which is itself an object of some
> >>class(doesn't precisely matter, the class definition for AccountInfo
will
> >>have imported the definition). This sub object has a property called
> >>"accountID" (and similarly for "order").
> >>
> >>[only asking because this seems to make the example slightly more
complex
> >>than it needs for explanation, and I am just making sure I understand
> >>correctly].
> >>
> >>
> >>
> >>>Does that make it any clearer?
> >>>
> >>>
> >>Well... I still don't understand exactly what the groupBy attribute is
> >>actually saying here. Let me run one proposition by you and see if I
have
> >>it right.
> >>
> >>Is it saying account.accountID is the common field that the query is
> >>
> >>
> >joined
> >
> >
> >>on, and therefore there will be a lot of records where this will be a
> >>
> >>
> >common
> >
> >
> >>factor - and to take all of them and use them to only create a single
> >>instance of class AccountInfo and to populate orderList with all the
> >>variants of this particular common field (and of course a similar
position
> >>this with OrderInfo and orderItemList)?
> >>
> >>If that right, then I think I've got it. I know when I need to use it.
> >>
> >>If not ...
> >>
> >>... then you will have to try again:-(
> >>
> >>
> >>
> >>
> >> --
> >>Alan Chandler
> >>alan@chandlerfamily.org.uk
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
RE: Confused with groupBy
Posted by Prashanth Sukumaran <pr...@yahoo.com>.
Hi Brian,
If you are getting a NullPointerException then
Add this
javaType="int" jdbcType="NUMERIC" nullValue="0"
to your resultMap for ORDER_ID and AMOUNT.
This is the worst possible error caused due to iBatis. If the bean has primitive member
variable(int, float etc) and you try to assign a null value to it. It throws a nullpointer
exception. For a newcomer using iBatis, he/she will not have a clue why this is happenning. I
think we need a fix for this, so we atleast throw a different kind of exception instead of a
NullPointerException.
I am sure taking into consideration all the people using iBatis, this constitues to 100 - 1000
hours wasted.
Rgds
Prashanth Sukumaran.
--- Brian Yoffe <by...@houston.rr.com> wrote:
> No, actually I don't think it's straightforward at all - let me make my
> example more concrete.
>
> Here's an example data set:
>
> ACCOUNTS TABLE:
>
> ACCOUNT_ID NAME
> 500 Jim
> 501 Bob
> 502 Mike
>
>
> ORDERS TABLE:
> ORDER_ID ACCOUNT_ID AMOUNT
> 100 500 1000
> 101 501 2000
> 102 501 3000
>
> From this data set, we can see that Jim has placed one order, Bob has placed
> two orders, and Mike has placed no orders. Now, I can solve this problem
> quite easily if I don't mind performing N+1 selects.
>
> My first SQL statement looks like this:
>
> select ACCOUNT_ID, NAME from ACCOUNTS
>
> and my second select looks like this:
>
> select ORDER_ID, AMOUNT from ORDERS where ACCOUNT_ID=#value#.
>
> I will not show the actual ibatis mapping, but lets assume I will use the
> technique whereby I map the orders list bean property to the result of
> separate select. Again, this method WILL cause N+! selects to be
> performed. I can turn on lazy loading and they they do not all occur at
> once, but N+! do occur.
>
>
>
> So, lets assume I try to resolve the N+1 selects problem by using the
> technique described below. Now, let's consider what the SQL looks like.
> I'll first try:
>
> select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
> from ACCOUNTS a, ORDERS o
> where a.account_id = o.account_id
>
> The result set is:
> ACCOUNT_ID NAME ORDER_ID AMOUNT
> 500 Jim 100 1000
> 501 Bob 101 2000
> 501 Bob 101 3000
>
>
>
> Ok, so after using a regular join, Mike completely dropped out of the join
> (just as we all knew it was). Strike 2. So, let me change the sql to an
> outer join:
>
> select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
> from ACCOUNTS a, ORDERS o
> where a.account_id = o.account_id (+)
>
> The result set is:
> ACCOUNT_ID NAME ORDER_ID AMOUNT
> 500 Jim 100 1000
> 501 Bob 101 2000
> 501 Bob 101 3000
> 502 Mike [null] [null]
>
> Fantastic! I have the result set that I want. Now, how do I keep iBatis
> from bombing when it encounters the nulls and tries to map that onto an
> order object. Essentially, I want an account object created for Mike I
> expect my code to create a new list just as you recommended, Huy.
>
> Hope that clears up my question.
>
> Thanks,
> Brian
>
> -----Original Message-----
> From: Huy Do [mailto:contactme42@gmail.com]
> Sent: Wednesday, August 24, 2005 8:24 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Confused with groupBy
>
> Shouldn't you do that yourself in the POJO ? If you assume this behavour
> in your apps, it wouldn't be a good idea to rely on ibatis to give it to
> you. What if you create the pojo manually ? then you wouldn't get the
> empty list. Something like
>
> if (myList == null) {
> myList = new List()
> }
> return myList
>
> is quite straightforward isnt it ?
>
> Huy
>
> >I hate to hijack Alan's question, but...
> >
> >I've been playing around with group by as well. I fully understand the
> N+1
> >selects problem and understand how groupBy works.
> >
> >Lets simplify your problem just a simple relationship - say account to
> order
> >(disregarding line items).
> >
> >Now if an account has no orders, everything, including account drops out of
> >the join. This is obviously SQL behavior, not iBatis specific. So, I
> >propose to solve my SQL problem by using an outer join. So, now I get all
> >accounts even those that do not have line items. Is there a way to get
> >ibatis to create an empty list of orders for those accounts where there
> >exist no orders?
> >
> >Thanks,
> >Brian Yoffe
> >
> >-----Original Message-----
> >From: Larry Meadors [mailto:larry.meadors@gmail.com]
> >Sent: Wednesday, August 24, 2005 11:33 AM
> >To: user-java@ibatis.apache.org
> >Subject: Re: Confused with groupBy
> >
> >You got it exactly right. :-)
> >
> >Larry
> >
> >
> >On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> >
> >
> >>Larry Meadors writes:
> >>
> >>...
> >>
> >>
> >>>The groupBy attribute is used to deal with the "N+1" selects problem.
> >>>The "N+1 Selects" problem is caused by trying to load child records
> >>>that are related to a list of parent records. So, if you run one query
> >>>to get the parent records, and there are some number "N" of them, then
> >>>you have to run "N" more queries to get the child records for the
> >>>parent records resulting in "N+1 selects".
> >>>
> >>>
> >>Yes I FULLY understand the above
> >>
> >>
> >>
> >>
> >>>Here is another example that does an Account -> Order -> OrderItem
> >>>mapping using this technique:
> >>>
> >>> <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> >>> groupBy="account.accountId" >
> >>> <result property="account.accountId" column="accountId" />
> >>> <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> >>> </resultMap>
> >>>
> >>> <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> >>> groupBy="order.orderId" >
> >>> <result property="order.orderId" column="orderId" />
> >>> <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap"
> >>>
> >>>
> >/>
> >
> >
> >>> </resultMap>
> >>>
> >>> <resultMap id="ResultOrderItemNMap" class="OrderItem">
> >>> <result property="orderId" column="orderId" />
> >>> <result property="orderItemId" column="orderItemId" />
> >>> </resultMap>
> >>>
> >>> <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> >>> select
> >>> account.accountId as accountid,
> >>> orders.orderid as orderid,
> >>> orderitem.orderitemid as orderitemid
> >>> from account
> >>> join orders on account.accountId = orders.accountId
> >>> join orderitem on orders.orderId = orderitem.orderId
> >>> order by accountId, orderid, orderitemid
> >>> </select>
> >>>
> >>>
> >>I may be a little dense here - can I just confirm. In class
> >>
> >>
> >"AccountInfo",
> >
> >
> >>you have a property called "account" which is itself an object of some
> >>class(doesn't precisely matter, the class definition for AccountInfo will
> >>have imported the definition). This sub object has a property called
> >>"accountID" (and similarly for "order").
> >>
> >>[only asking because this seems to make the example slightly more complex
> >>than it needs for explanation, and I am just making sure I understand
> >>correctly].
> >>
> >>
> >>
> >>>Does that make it any clearer?
> >>>
> >>>
> >>Well... I still don't understand exactly what the groupBy attribute is
> >>actually saying here. Let me run one proposition by you and see if I have
> >>it right.
> >>
> >>Is it saying account.accountID is the common field that the query is
> >>
> >>
> >joined
> >
> >
> >>on, and therefore there will be a lot of records where this will be a
> >>
> >>
> >common
> >
> >
> >>factor - and to take all of them and use them to only create a single
> >>instance of class AccountInfo and to populate orderList with all the
> >>variants of this particular common field (and of course a similar position
> >>this with OrderInfo and orderItemList)?
> >>
> >>If that right, then I think I've got it. I know when I need to use it.
> >>
> >>If not ...
> >>
> >>... then you will have to try again:-(
> >>
> >>
> >>
> >>
> >> --
> >>Alan Chandler
> >>alan@chandlerfamily.org.uk
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
RE: Confused with groupBy
Posted by Brian Yoffe <by...@houston.rr.com>.
No, actually I don't think it's straightforward at all - let me make my
example more concrete.
Here's an example data set:
ACCOUNTS TABLE:
ACCOUNT_ID NAME
500 Jim
501 Bob
502 Mike
ORDERS TABLE:
ORDER_ID ACCOUNT_ID AMOUNT
100 500 1000
101 501 2000
102 501 3000
>From this data set, we can see that Jim has placed one order, Bob has placed
two orders, and Mike has placed no orders. Now, I can solve this problem
quite easily if I don't mind performing N+1 selects.
My first SQL statement looks like this:
select ACCOUNT_ID, NAME from ACCOUNTS
and my second select looks like this:
select ORDER_ID, AMOUNT from ORDERS where ACCOUNT_ID=#value#.
I will not show the actual ibatis mapping, but lets assume I will use the
technique whereby I map the orders list bean property to the result of
separate select. Again, this method WILL cause N+! selects to be
performed. I can turn on lazy loading and they they do not all occur at
once, but N+! do occur.
So, lets assume I try to resolve the N+1 selects problem by using the
technique described below. Now, let's consider what the SQL looks like.
I'll first try:
select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
from ACCOUNTS a, ORDERS o
where a.account_id = o.account_id
The result set is:
ACCOUNT_ID NAME ORDER_ID AMOUNT
500 Jim 100 1000
501 Bob 101 2000
501 Bob 101 3000
Ok, so after using a regular join, Mike completely dropped out of the join
(just as we all knew it was). Strike 2. So, let me change the sql to an
outer join:
select a.ACCOUNT_ID, a.NAME, o.ORDER_ID, o.AMOUNT
from ACCOUNTS a, ORDERS o
where a.account_id = o.account_id (+)
The result set is:
ACCOUNT_ID NAME ORDER_ID AMOUNT
500 Jim 100 1000
501 Bob 101 2000
501 Bob 101 3000
502 Mike [null] [null]
Fantastic! I have the result set that I want. Now, how do I keep iBatis
from bombing when it encounters the nulls and tries to map that onto an
order object. Essentially, I want an account object created for Mike I
expect my code to create a new list just as you recommended, Huy.
Hope that clears up my question.
Thanks,
Brian
-----Original Message-----
From: Huy Do [mailto:contactme42@gmail.com]
Sent: Wednesday, August 24, 2005 8:24 PM
To: user-java@ibatis.apache.org
Subject: Re: Confused with groupBy
Shouldn't you do that yourself in the POJO ? If you assume this behavour
in your apps, it wouldn't be a good idea to rely on ibatis to give it to
you. What if you create the pojo manually ? then you wouldn't get the
empty list. Something like
if (myList == null) {
myList = new List()
}
return myList
is quite straightforward isnt it ?
Huy
>I hate to hijack Alan's question, but...
>
>I've been playing around with group by as well. I fully understand the
N+1
>selects problem and understand how groupBy works.
>
>Lets simplify your problem just a simple relationship - say account to
order
>(disregarding line items).
>
>Now if an account has no orders, everything, including account drops out of
>the join. This is obviously SQL behavior, not iBatis specific. So, I
>propose to solve my SQL problem by using an outer join. So, now I get all
>accounts even those that do not have line items. Is there a way to get
>ibatis to create an empty list of orders for those accounts where there
>exist no orders?
>
>Thanks,
>Brian Yoffe
>
>-----Original Message-----
>From: Larry Meadors [mailto:larry.meadors@gmail.com]
>Sent: Wednesday, August 24, 2005 11:33 AM
>To: user-java@ibatis.apache.org
>Subject: Re: Confused with groupBy
>
>You got it exactly right. :-)
>
>Larry
>
>
>On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
>
>
>>Larry Meadors writes:
>>
>>...
>>
>>
>>>The groupBy attribute is used to deal with the "N+1" selects problem.
>>>The "N+1 Selects" problem is caused by trying to load child records
>>>that are related to a list of parent records. So, if you run one query
>>>to get the parent records, and there are some number "N" of them, then
>>>you have to run "N" more queries to get the child records for the
>>>parent records resulting in "N+1 selects".
>>>
>>>
>>Yes I FULLY understand the above
>>
>>
>>
>>
>>>Here is another example that does an Account -> Order -> OrderItem
>>>mapping using this technique:
>>>
>>> <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
>>> groupBy="account.accountId" >
>>> <result property="account.accountId" column="accountId" />
>>> <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
>>> </resultMap>
>>>
>>> <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
>>> groupBy="order.orderId" >
>>> <result property="order.orderId" column="orderId" />
>>> <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap"
>>>
>>>
>/>
>
>
>>> </resultMap>
>>>
>>> <resultMap id="ResultOrderItemNMap" class="OrderItem">
>>> <result property="orderId" column="orderId" />
>>> <result property="orderItemId" column="orderItemId" />
>>> </resultMap>
>>>
>>> <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
>>> select
>>> account.accountId as accountid,
>>> orders.orderid as orderid,
>>> orderitem.orderitemid as orderitemid
>>> from account
>>> join orders on account.accountId = orders.accountId
>>> join orderitem on orders.orderId = orderitem.orderId
>>> order by accountId, orderid, orderitemid
>>> </select>
>>>
>>>
>>I may be a little dense here - can I just confirm. In class
>>
>>
>"AccountInfo",
>
>
>>you have a property called "account" which is itself an object of some
>>class(doesn't precisely matter, the class definition for AccountInfo will
>>have imported the definition). This sub object has a property called
>>"accountID" (and similarly for "order").
>>
>>[only asking because this seems to make the example slightly more complex
>>than it needs for explanation, and I am just making sure I understand
>>correctly].
>>
>>
>>
>>>Does that make it any clearer?
>>>
>>>
>>Well... I still don't understand exactly what the groupBy attribute is
>>actually saying here. Let me run one proposition by you and see if I have
>>it right.
>>
>>Is it saying account.accountID is the common field that the query is
>>
>>
>joined
>
>
>>on, and therefore there will be a lot of records where this will be a
>>
>>
>common
>
>
>>factor - and to take all of them and use them to only create a single
>>instance of class AccountInfo and to populate orderList with all the
>>variants of this particular common field (and of course a similar position
>>this with OrderInfo and orderItemList)?
>>
>>If that right, then I think I've got it. I know when I need to use it.
>>
>>If not ...
>>
>>... then you will have to try again:-(
>>
>>
>>
>>
>> --
>>Alan Chandler
>>alan@chandlerfamily.org.uk
>>
>>
>>
>>
>>
>
>
>
>
Re: Confused with groupBy
Posted by Huy Do <co...@gmail.com>.
Shouldn't you do that yourself in the POJO ? If you assume this behavour
in your apps, it wouldn't be a good idea to rely on ibatis to give it to
you. What if you create the pojo manually ? then you wouldn't get the
empty list. Something like
if (myList == null) {
myList = new List()
}
return myList
is quite straightforward isnt it ?
Huy
>I hate to hijack Alan's question, but...
>
>I've been playing around with group by as well. I fully understand the N+1
>selects problem and understand how groupBy works.
>
>Lets simplify your problem just a simple relationship - say account to order
>(disregarding line items).
>
>Now if an account has no orders, everything, including account drops out of
>the join. This is obviously SQL behavior, not iBatis specific. So, I
>propose to solve my SQL problem by using an outer join. So, now I get all
>accounts even those that do not have line items. Is there a way to get
>ibatis to create an empty list of orders for those accounts where there
>exist no orders?
>
>Thanks,
>Brian Yoffe
>
>-----Original Message-----
>From: Larry Meadors [mailto:larry.meadors@gmail.com]
>Sent: Wednesday, August 24, 2005 11:33 AM
>To: user-java@ibatis.apache.org
>Subject: Re: Confused with groupBy
>
>You got it exactly right. :-)
>
>Larry
>
>
>On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
>
>
>>Larry Meadors writes:
>>
>>...
>>
>>
>>>The groupBy attribute is used to deal with the "N+1" selects problem.
>>>The "N+1 Selects" problem is caused by trying to load child records
>>>that are related to a list of parent records. So, if you run one query
>>>to get the parent records, and there are some number "N" of them, then
>>>you have to run "N" more queries to get the child records for the
>>>parent records resulting in "N+1 selects".
>>>
>>>
>>Yes I FULLY understand the above
>>
>>
>>
>>
>>>Here is another example that does an Account -> Order -> OrderItem
>>>mapping using this technique:
>>>
>>> <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
>>> groupBy="account.accountId" >
>>> <result property="account.accountId" column="accountId" />
>>> <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
>>> </resultMap>
>>>
>>> <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
>>> groupBy="order.orderId" >
>>> <result property="order.orderId" column="orderId" />
>>> <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap"
>>>
>>>
>/>
>
>
>>> </resultMap>
>>>
>>> <resultMap id="ResultOrderItemNMap" class="OrderItem">
>>> <result property="orderId" column="orderId" />
>>> <result property="orderItemId" column="orderItemId" />
>>> </resultMap>
>>>
>>> <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
>>> select
>>> account.accountId as accountid,
>>> orders.orderid as orderid,
>>> orderitem.orderitemid as orderitemid
>>> from account
>>> join orders on account.accountId = orders.accountId
>>> join orderitem on orders.orderId = orderitem.orderId
>>> order by accountId, orderid, orderitemid
>>> </select>
>>>
>>>
>>I may be a little dense here - can I just confirm. In class
>>
>>
>"AccountInfo",
>
>
>>you have a property called "account" which is itself an object of some
>>class(doesn't precisely matter, the class definition for AccountInfo will
>>have imported the definition). This sub object has a property called
>>"accountID" (and similarly for "order").
>>
>>[only asking because this seems to make the example slightly more complex
>>than it needs for explanation, and I am just making sure I understand
>>correctly].
>>
>>
>>
>>>Does that make it any clearer?
>>>
>>>
>>Well... I still don't understand exactly what the groupBy attribute is
>>actually saying here. Let me run one proposition by you and see if I have
>>it right.
>>
>>Is it saying account.accountID is the common field that the query is
>>
>>
>joined
>
>
>>on, and therefore there will be a lot of records where this will be a
>>
>>
>common
>
>
>>factor - and to take all of them and use them to only create a single
>>instance of class AccountInfo and to populate orderList with all the
>>variants of this particular common field (and of course a similar position
>>this with OrderInfo and orderItemList)?
>>
>>If that right, then I think I've got it. I know when I need to use it.
>>
>>If not ...
>>
>>... then you will have to try again:-(
>>
>>
>>
>>
>> --
>>Alan Chandler
>>alan@chandlerfamily.org.uk
>>
>>
>>
>>
>>
>
>
>
>
RE: Confused with groupBy
Posted by Brian Yoffe <by...@houston.rr.com>.
I hate to hijack Alan's question, but...
I've been playing around with group by as well. I fully understand the N+1
selects problem and understand how groupBy works.
Lets simplify your problem just a simple relationship - say account to order
(disregarding line items).
Now if an account has no orders, everything, including account drops out of
the join. This is obviously SQL behavior, not iBatis specific. So, I
propose to solve my SQL problem by using an outer join. So, now I get all
accounts even those that do not have line items. Is there a way to get
ibatis to create an empty list of orders for those accounts where there
exist no orders?
Thanks,
Brian Yoffe
-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: Wednesday, August 24, 2005 11:33 AM
To: user-java@ibatis.apache.org
Subject: Re: Confused with groupBy
You got it exactly right. :-)
Larry
On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> Larry Meadors writes:
>
> ...
> >
> > The groupBy attribute is used to deal with the "N+1" selects problem.
> > The "N+1 Selects" problem is caused by trying to load child records
> > that are related to a list of parent records. So, if you run one query
> > to get the parent records, and there are some number "N" of them, then
> > you have to run "N" more queries to get the child records for the
> > parent records resulting in "N+1 selects".
>
> Yes I FULLY understand the above
>
>
> >
> > Here is another example that does an Account -> Order -> OrderItem
> > mapping using this technique:
> >
> > <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> > groupBy="account.accountId" >
> > <result property="account.accountId" column="accountId" />
> > <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> > </resultMap>
> >
> > <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> > groupBy="order.orderId" >
> > <result property="order.orderId" column="orderId" />
> > <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap"
/>
> > </resultMap>
> >
> > <resultMap id="ResultOrderItemNMap" class="OrderItem">
> > <result property="orderId" column="orderId" />
> > <result property="orderItemId" column="orderItemId" />
> > </resultMap>
> >
> > <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> > select
> > account.accountId as accountid,
> > orders.orderid as orderid,
> > orderitem.orderitemid as orderitemid
> > from account
> > join orders on account.accountId = orders.accountId
> > join orderitem on orders.orderId = orderitem.orderId
> > order by accountId, orderid, orderitemid
> > </select>
>
> I may be a little dense here - can I just confirm. In class
"AccountInfo",
> you have a property called "account" which is itself an object of some
> class(doesn't precisely matter, the class definition for AccountInfo will
> have imported the definition). This sub object has a property called
> "accountID" (and similarly for "order").
>
> [only asking because this seems to make the example slightly more complex
> than it needs for explanation, and I am just making sure I understand
> correctly].
>
> >
> > Does that make it any clearer?
>
>
> Well... I still don't understand exactly what the groupBy attribute is
> actually saying here. Let me run one proposition by you and see if I have
> it right.
>
> Is it saying account.accountID is the common field that the query is
joined
> on, and therefore there will be a lot of records where this will be a
common
> factor - and to take all of them and use them to only create a single
> instance of class AccountInfo and to populate orderList with all the
> variants of this particular common field (and of course a similar position
> this with OrderInfo and orderItemList)?
>
> If that right, then I think I've got it. I know when I need to use it.
>
> If not ...
>
> ... then you will have to try again:-(
>
>
>
>
> --
> Alan Chandler
> alan@chandlerfamily.org.uk
>
>
>
Re: Confused with groupBy
Posted by Larry Meadors <la...@gmail.com>.
You got it exactly right. :-)
Larry
On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> Larry Meadors writes:
>
> ...
> >
> > The groupBy attribute is used to deal with the "N+1" selects problem.
> > The "N+1 Selects" problem is caused by trying to load child records
> > that are related to a list of parent records. So, if you run one query
> > to get the parent records, and there are some number "N" of them, then
> > you have to run "N" more queries to get the child records for the
> > parent records resulting in "N+1 selects".
>
> Yes I FULLY understand the above
>
>
> >
> > Here is another example that does an Account -> Order -> OrderItem
> > mapping using this technique:
> >
> > <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> > groupBy="account.accountId" >
> > <result property="account.accountId" column="accountId" />
> > <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> > </resultMap>
> >
> > <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> > groupBy="order.orderId" >
> > <result property="order.orderId" column="orderId" />
> > <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap" />
> > </resultMap>
> >
> > <resultMap id="ResultOrderItemNMap" class="OrderItem">
> > <result property="orderId" column="orderId" />
> > <result property="orderItemId" column="orderItemId" />
> > </resultMap>
> >
> > <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> > select
> > account.accountId as accountid,
> > orders.orderid as orderid,
> > orderitem.orderitemid as orderitemid
> > from account
> > join orders on account.accountId = orders.accountId
> > join orderitem on orders.orderId = orderitem.orderId
> > order by accountId, orderid, orderitemid
> > </select>
>
> I may be a little dense here - can I just confirm. In class "AccountInfo",
> you have a property called "account" which is itself an object of some
> class(doesn't precisely matter, the class definition for AccountInfo will
> have imported the definition). This sub object has a property called
> "accountID" (and similarly for "order").
>
> [only asking because this seems to make the example slightly more complex
> than it needs for explanation, and I am just making sure I understand
> correctly].
>
> >
> > Does that make it any clearer?
>
>
> Well... I still don't understand exactly what the groupBy attribute is
> actually saying here. Let me run one proposition by you and see if I have
> it right.
>
> Is it saying account.accountID is the common field that the query is joined
> on, and therefore there will be a lot of records where this will be a common
> factor - and to take all of them and use them to only create a single
> instance of class AccountInfo and to populate orderList with all the
> variants of this particular common field (and of course a similar position
> this with OrderInfo and orderItemList)?
>
> If that right, then I think I've got it. I know when I need to use it.
>
> If not ...
>
> ... then you will have to try again:-(
>
>
>
>
> --
> Alan Chandler
> alan@chandlerfamily.org.uk
>
>
>
Re: Confused with groupBy
Posted by Alan Chandler <al...@chandlerfamily.org.uk>.
Larry Meadors writes:
...
>
> The groupBy attribute is used to deal with the "N+1" selects problem.
> The "N+1 Selects" problem is caused by trying to load child records
> that are related to a list of parent records. So, if you run one query
> to get the parent records, and there are some number "N" of them, then
> you have to run "N" more queries to get the child records for the
> parent records resulting in "N+1 selects".
Yes I FULLY understand the above
>
> Here is another example that does an Account -> Order -> OrderItem
> mapping using this technique:
>
> <resultMap id="ResultAccountInfoNMap" class="AccountInfo"
> groupBy="account.accountId" >
> <result property="account.accountId" column="accountId" />
> <result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
> </resultMap>
>
> <resultMap id="ResultOrderInfoNMap" class="OrderInfo"
> groupBy="order.orderId" >
> <result property="order.orderId" column="orderId" />
> <result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap" />
> </resultMap>
>
> <resultMap id="ResultOrderItemNMap" class="OrderItem">
> <result property="orderId" column="orderId" />
> <result property="orderItemId" column="orderItemId" />
> </resultMap>
>
> <select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
> select
> account.accountId as accountid,
> orders.orderid as orderid,
> orderitem.orderitemid as orderitemid
> from account
> join orders on account.accountId = orders.accountId
> join orderitem on orders.orderId = orderitem.orderId
> order by accountId, orderid, orderitemid
> </select>
I may be a little dense here - can I just confirm. In class "AccountInfo",
you have a property called "account" which is itself an object of some
class(doesn't precisely matter, the class definition for AccountInfo will
have imported the definition). This sub object has a property called
"accountID" (and similarly for "order").
[only asking because this seems to make the example slightly more complex
than it needs for explanation, and I am just making sure I understand
correctly].
>
> Does that make it any clearer?
Well... I still don't understand exactly what the groupBy attribute is
actually saying here. Let me run one proposition by you and see if I have
it right.
Is it saying account.accountID is the common field that the query is joined
on, and therefore there will be a lot of records where this will be a common
factor - and to take all of them and use them to only create a single
instance of class AccountInfo and to populate orderList with all the
variants of this particular common field (and of course a similar position
this with OrderInfo and orderItemList)?
If that right, then I think I've got it. I know when I need to use it.
If not ...
... then you will have to try again:-(
--
Alan Chandler
alan@chandlerfamily.org.uk
Re: Confused with groupBy
Posted by Larry Meadors <la...@gmail.com>.
Wow, lots of good questions.
Starting with the easiest - the namespace attribute is used to
logically group sqlmap elements - for example, you can define a
"Product" namespace for mapped statements and related items that are
to be used when working with products. That is done by setting the
useStatementNamespaces attribute to true in the SqlMapConfig.xml file.
Think of it as analogous to a package name in Java - so you can have
multiple mapped statements named "insert" as long as they are in
different namespaces.
The DTD to be used should be the ibatis.apache.org one.
The groupBy attribute is used to deal with the "N+1" selects problem.
The "N+1 Selects" problem is caused by trying to load child records
that are related to a list of parent records. So, if you run one query
to get the parent records, and there are some number "N" of them, then
you have to run "N" more queries to get the child records for the
parent records resulting in "N+1 selects".
Here is another example that does an Account -> Order -> OrderItem
mapping using this technique:
<resultMap id="ResultAccountInfoNMap" class="AccountInfo"
groupBy="account.accountId" >
<result property="account.accountId" column="accountId" />
<result property="orderList" resultMap="Ch7.ResultOrderInfoNMap" />
</resultMap>
<resultMap id="ResultOrderInfoNMap" class="OrderInfo"
groupBy="order.orderId" >
<result property="order.orderId" column="orderId" />
<result property="orderItemList" resultMap="Ch7.ResultOrderItemNMap" />
</resultMap>
<resultMap id="ResultOrderItemNMap" class="OrderItem">
<result property="orderId" column="orderId" />
<result property="orderItemId" column="orderItemId" />
</resultMap>
<select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
select
account.accountId as accountid,
orders.orderid as orderid,
orderitem.orderitemid as orderitemid
from account
join orders on account.accountId = orders.accountId
join orderitem on orders.orderId = orderitem.orderId
order by accountId, orderid, orderitemid
</select>
Does that make it any clearer?
Larry
On 8/24/05, Alan Chandler <al...@chandlerfamily.org.uk> wrote:
> Hi
>
> I am new to this list and iBATIS, but have started to use it (embedded
> within the tapestry framework) for building a small database application at
> home.
>
> I have read the developers guide, in an attempt to get straight how I am
> going to use iBATIS to meet my needs, and most of it seems very clear.
> However I have got a little stuck understanding properly how to deal with
> complex joins. In particular, the example given on page 28/29 of the
> developer guide and the use of the "groupBy" attribute to the resultMap
> element. I assume the "quarter" it is refering to is mapped to the
> QuarterNumber column of the BroadcastDate table, but from there on in I am
> lost. [It doesn't help that my sql knowledge is not 100%, so a select
> distinct with a group by which may or may not match the simplified groupBy
> element in the xml is also confusing me a bit].
>
> In an attempt to find out more, I thought the dtd might have some comments,
> and downloaded the one refered to at the head of page 13.
> (sql-map-config-2.dtd). However, I could find no reference the resultMap
> element.
>
> In the end, a little digging on http://ibatis.apache.org/dtd/ (ie getting a
> directory listing) revealed that there is a different dtd sql-map-2.dtd.
>
> So
>
> 1) I think there is a bug in the guide refering to the wrong dtd. Am I
> correct? [and I don't mean the different domain name either - but I assume
> that in the longer term that it would be more correct to use the
> ibatis.apache.org name rather than the www.ibatis.com]
>
> 2) Could someone give me a more detailed explanation of what the groupBy
> attribute is trying to do.
>
> 3) Why is there a namespace attribute on the sqlMap element and what does it
> do. I haven't been able to find any previous (to page 28) reference in the
> guide.
>
> Thanks.
> --
> Alan Chandler
> alan@chandlerfamily.org.uk
>
>
>