From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: problem : Dynamic Data handling
without doing loops in ibatisDate: Wed, 25 Jun 2008 12:44:57 +0300
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.
_________________________________________________________________
The i’m Talkathon starts 6/24/08. For now, give amongst yourselves.
http://www.imtalkathon.com?source=TXT_EML_WLH_LearnMore_GiveAmongst
