Example of "Solution for N+1 selects for 1:M and M:N" and "Remappable result sets"...

[Ron Grabowski <ro...@yahoo.com>]

I saw that iBatis.com has been updated with 2.0.9. I had a hard time
following the snippets on the front page for N+1 selects:

 <resultMap...groupBy="prop1,prop2">
 <result...resultMap="subResultMapForCollectionItems">

and remappable result sets:

 <select...remapResults="true">

Could someone take out the ... and give a more complete example?

Thanks,
Ron

Re: Example of "Solution for N+1 selects for 1:M and M:N" and "Remappable result sets"...

[Clinton Begin <cl...@gmail.com>]

Here's a JPetStore-ish example from the unit tests.  This example
actually joins of 4 tables, 3 of which are involved in 2 sets of
repeating groups.  Or in other words, both Cagegory and Product repeat
for the total number of returned Items (Inventory is just a 1:1 with
Item).

  <resultMap id="categoryResult" class="testdomain.Category"
groupBy="categoryId">
    <result property="categoryId" column="catid"/>
    <result property="name" column="name"/>
    <result property="description" column="descn"/>
    <result property="productList" resultMap="productResult"/>
  </resultMap>

  <resultMap id="productResult" class="testdomain.Product" groupBy="productId">
    <result property="productId" column="productid"/>
    <result property="categoryId" column="category"/>
    <result property="name" column="name"/>
    <result property="description" column="descn"/>
    <result property="itemList" resultMap="itemResult"/>
  </resultMap>

  <resultMap id="itemResult" class="testdomain.Item">
    <result property="itemId" column="itemid"/>
    <result property="productId" column="productid"/>
    <result property="listPrice" column="listprice"/>
    <result property="unitCost" column="unitcost"/>
    <result property="supplierId" column="supplier"/>
    <result property="status" column="status"/>
    <result property="attribute1" column="attr1"/>
    <result property="quantity" column="qty"/>
  </resultMap>

  <select id="getAllCategories" resultMap="categoryResult" >
    select *
    from category c, product p, item i, inventory v
    where c.catid = p.category
      and p.productid = i.productid
      and i.itemid = v.itemid
  </select>

  <select id="getFish" resultMap="categoryResult" >
    select *
    from category c, product p, item i, inventory v
    where c.catid = p.category
      and p.productid = i.productid
      and i.itemid = v.itemid
      and c.catid = 'FISH'
  </select>


Cheers,
Clinton




On Sun, 2 Jan 2005 17:59:03 -0800 (PST), Ron Grabowski
<ro...@yahoo.com> wrote:
> I saw that iBatis.com has been updated with 2.0.9. I had a hard time
> following the snippets on the front page for N+1 selects:
> 
>  <resultMap...groupBy="prop1,prop2">
>  <result...resultMap="subResultMapForCollectionItems">
> 
> and remappable result sets:
> 
>  <select...remapResults="true">
> 
> Could someone take out the ... and give a more complete example?
> 
> Thanks,
> Ron
> 
>