ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prashanth Sukumaran <prashanthsukuma...@yahoo.com>
Subject RE: Confused with groupBy
Date Sat, 27 Aug 2005 03:37:37 GMT
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