ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Khoury" <khourymar...@hotmail.com>
Subject problem : Dynamic Data handling without doing loops in ibatis
Date Wed, 25 Jun 2008 09:44:57 GMT
I am using Ibatis for my sql maps. The problem i have is that i am building a grid that contains
Dynamic heads for ex: 

 

I want to read several items which is fine. But if i need to read their suppliers, where each
item can have many depending on the data saved in the table that contains the item code and
the supplier code as unique key, here i am building the rest of my columns (lets say supplier
1, supplier 2, etc..) dynamically. I am asking if there is a way getting each item with its
suppliers displayed in one row in the result set and, this, in one query not with the known
way where we put in the result map a property of type list and call an inside select query
that reads the suppliers for each item. Because in this way the inside select query will be
called so many times as much as the number of the items from the main query.

 

for ex: I have this query:

 

with tbl as (

 

                    select si.item_code, si.supplier_code  from item_supplier_table si

 

)

 

select i.item_code, i.item_name

 

        , ( select t.supplier_code, (select s.supplier_name from supplier_table s where s.supplier_code
= t.supplier_code) as supplier_name 

            from tbl t where t.item_code = i.item_code )

 

from item_table i 

 

 

 

For sure this query will not work if we run it cause i don't think there is in Oracle a way
to make a query returns columns instead of rows whether it will display the singlesubrowquery
returns more than one row error. But i am asking if there is a special tag used in ibatis
to do as follow:

We consider that all items i am reading have the same number of suppliers.

 

 

with tbl as (

 

                    select si.item_code, si.supplier_code  from item_supplier_table si

 

)

 

select i.item_code, i.item_name

 

   <specialTag   ........  >

 

        , ( select t.supplier_code, (select s.supplier_name from supplier_table s where s.supplier_code
= t.supplier_code) as supplier_name 

            from tbl t where t.item_code = i.item_code )

 

  </specialTag>

 

from item_table i 

 

 

This specialTag will return lets say two columns for the property of type list in the resulMap
which must in turn handle this specialTag.

 

Also this specialTag will display the resultSet for each item on my server or log files in
one row as

 

 "itemCode,itemName,list[0].supplierCode,list[0].supplierName,list[1].supplierCode,list[1].supplierName,..."

 

for the data

 

"123, 0.5L buttle, 1, pepsi, 2, coca, ..."

"124, 1L buttle, 1 pepsi, 3, miranda, ..."

etc







 

 

All of this will come in use in the display of my grid in the JSP file where i am using the
SSGridHeads tags from the TLD's:

 

ItemCode        ItemName        Supplier1        Supplier2        ......

 

 123                 0.5L buttle          pepsi                coca              ......

 124                 1L butyle            pepsi                miranda          ......

 etc

  .

  .

  .

 

 

 

So the above table can have thousands of records of items along with their suppliers, and
if we are calling for each row the inside select query that reads the suppliers of the item,
this is taking too much performance to display the grid in the page even though if we minimize
the records to display in the flipping utitlity of the toolbar of the grid. But lets say we
put 90 records per page, it will still enter the inside query 90 times.

 

Mainly my problem is Dynamic Data handling without doing loops in ibatis. 

 

Thank you in advance.

 

Regards,

Marc.

 

Mime
View raw message