Ibatis issue problem: Dynamic Data handling without doing loops in ibatis

[marc2]

If someone has an idea or a way to solve this i hope we can communicate and i was wondering if the generate-ibatis ant task can solve this problem.

If this is the right forum to post this thread, i have the following problem:

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 ......

12345678 | 0.5L buttle | pepsicok | cocacola ......
12345679 | 1.5L buttle | pepsicok | mirandas ......
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.

I am using Java code with Struts, Eclipse tool, Tomcat server and Oracle database.

Thank you in advance.

Regards,
Marc.