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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> >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 <[EMAIL PROTECTED]> 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
> >>[EMAIL PROTECTED]
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
