Re: QueryBySQL

Hi Krupa,

krupa wrote:
> Hi All,
> 
> I have several SQLs already written.
> 
> I want to use QueryBySQL to use the existing SQLs. The problem I am facing
> is how do I map the result set of these queries to my java classes,
> especially if I have a result set that includes fields from 3 tables 
> then how do I map this in the reposity.xml file?
>

Using an existing sql-string spanning several tables (persistence 
capable classes) can't be mapped to a java class by OJB, but you can use 
a report query which returns each result set row as an object array and 
map these array by your own:

Query query = QueryFactory.newQuery(null, sql);
Iterator it = broker.getReportQueryIteratorByQuery(query);
while(it.hasNext())
{
     Object o =  it.next();
     System.out.println("result: " + ArrayUtils.toString(o));
}

NOTE: There is a bug in OJB versions <=1.0.4 if you try to perform such 
a report query. The latest version from SVN (OJB_1_0_RELEASE branch!!) 
contains a bug fix.

regards,
Armin




> Please help me with this.
> 
> here is an example of my SQL statement.
> 
> 
>  select A1.field1, A1.field2, A1.field3,A2.field1,
> A2.field2,A2.field3,A2.field4,  
>   A3.field1 as tmp1, A3.field2 as tmphdr2
>  from (((Table1  
>    left outer join table2 on field1= field2)
>    left outer join table2 A2 on field2 = A2.field2) 
>    left outer join table2 A3 on field5= A3.field2) 
>    left outer join table2 A4 on field6 = A3.field2  
>  where field1= 12  
>   and field8 = ''A'' 
>   order by field9
> ;
> 
> Thanks,
> Krupa
> 
>