ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <larry.mead...@gmail.com>
Subject Re: Confused with groupBy
Date Wed, 24 Aug 2005 12:27:37 GMT
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 <alan@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
> 
> 
>

Mime
View raw message