ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Yoffe" <byo...@houston.rr.com>
Subject RE: Confused with groupBy
Date Sat, 27 Aug 2005 05:57:11 GMT
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 <byoffe@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 <alan@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



Mime
View raw message