Hi there,
I am trying to get data from multiple tabels so i am using join. Please let me know the problem here.
TblMeasureBean a = new TblMeasureBean();
TblContentAreaBean c = new TblContentAreaBean();
TblIndicatorBean b = new TblIndicatorBean();
TblContentAreaIndicatorBean e =new TblContentAreaIndicatorBean();
TblIndicatorMeasureBean d = new TblIndicatorMeasureBean();

System.out.println("Retrieving Record");
// TblCalculation calculation = new TblCalculation();
String hql = "select a.measureId, a.name, b.name,c.name from TblMeasureBean as a, TblIndicatorBean as b, TblContentAreaBean as c, TblIndicatorMeasureBean as d, TblContentAreaIndicatorBean as e where a.measureId = d.measureId and d.indicatorId = b.indicatorId and d.indicatorId = e.indicatorId and e.contentAreaId = c.contentAreaId";
//List list = session.createQuery("from TblCalculation").list();
org.hibernate.Query query = session.createQuery(hql);
session.getTransaction();
this sis the output I am getting from hibernate

select tblmeasure0_.MeasureId as col_0_0_, tblmeasure0_.Name as col_1_0_, tblindicat1_.NAME as col_2_0_, tblcontent2_.NAME as col_3_0_ from EPHTN2.dbo.tblMeasure tblmeasure0_ cross join TBLINDICATORBEAN tblindicat1_ cross join TBLCONTENTAREABEAN tblcontent2_ cross join TBLINDICATORMEASUREBEAN tblindicat3_ cross join TBLCONTENTAREAINDICATORBEAN tblcontent4_ where tblmeasure0_.MeasureId=tblindicat3_.MEASUREID and tblindicat3_.INDICATORID=tblindicat1_.INDICATORID and tblindicat3_.INDICATORID=tblcontent4_.INDICATORID and tblcontent4_.CONTENTAREAID=tblcontent2_.CONTENTAREAID
could not execute query
flush done

Hello, if you need to create multiple table query. We recommend using a native query and a class (bean) that contains the fields of query and use projections to load the list.

For example:
SQLQuery consulta = session.createSQLQuery("SELECT A.FIELD1, A.FIELD2, B.FIELD3 FROM TABLE1 A, TABLE2 B WHERE A.B_ID= B.ID");
consulta.setResultTransformer(Transformers.aliasToBean(ResultList.class));
consulta.addScalar("field1",Hibernate.STRING);
consulta.addScalar("field2",Hibernate.STRING);
consulta.addScalar("field3",Hibernate.STRING);
return consulta.list();

And result list is
class ResultList
{
private String field1;
private String field2;
private String field3;

//add getter and setter methods
}



I hope you find it useful

thanks!

Is any thing wrong in the above query why is it not coming

Thanks for your reply,
I am able to run the query now with modification of a table name hbm.xml file.
After getting result set i am casting to a bean which has 4 attributes and getter and setter method. Please suggest me what to do.

String hql = "select a.measureId as measureNumber, a.name as measureName," +
" b.name as contentAreaName, c.name as indicatorName from" +
" TblMeasureBean a, " +
" TblIndicatorBean b, " +
" TblContentAreaBean c," +
" TblIndicatorMeasureBean d, " +
" TblContentAreaIndicatorBean e " +
" where a.measureId = d.measureId and " +
"d.indicatorId = b.indicatorId and" +
" d.indicatorId = e.indicatorId and" +
" e.contentAreaId = c.contentAreaId";

org.hibernate.Query query = session.createQuery(hql);
System.out.println(query.getQueryString());
session.getTransaction();

//Vector bag = new Vector();
// calculationTable = (TblCalculation) bag.add(list);
List list = query.list();
System.out.println(list.size());
Iterator i = list.iterator();

while(i.hasNext())
{ SearchMeasureBean searchData = (SearchMeasureBean)i.next();

// System.out.println("getMeasureId:"+td.getMeasureId());
System.out.println("getMeasureName:-"+searchData.getContentAreaName());
}



I am getting this error

Hibernate: select tblmeasure0_.MeasureId as col_0_0_, tblmeasure0_.Name as col_1_0_, tblindicat1_.NAME as col_2_0_, tblcontent2_.NAME as col_3_0_ from EPHTN2.dbo.tblMeasure tblmeasure0_ cross join TBLINDICATOR tblindicat1_ cross join TBLCONTENTAREA tblcontent2_ cross join TBLINDICATORMEASURE tblindicat3_ cross join TBLCONTENTAREAINDICATOR tblcontent4_ where tblmeasure0_.MeasureId=tblindicat3_.MEASUREID and tblindicat3_.INDICATORID=tblindicat1_.INDICATORID and tblindicat3_.INDICATORID=tblcontent4_.INDICATORID and tblcontent4_.CONTENTAREAID=tblcontent2_.CONTENTAREAID
322
[Ljava.lang.Object; cannot be cast to com.scimetrika.actionhelpers.SearchMeasureBean
flush done

Thank you, this is good news for new visitors.