Hi,

I'm new to Hibernate. I developed a search functionality in JDBC. Now I need to convert it in hibernate. For this I'm using Hibernate SQLQuery because I need to join 3 tables.

But I'm facing a difficulty. I've a query like this :

select LOCATION, count(*) from Location_Table location left join st_sensor_table sensor on location.LOCATION = sensor.ST_SENSOR_ID where "+columnName+" like \"%"+ searchText + "%\" group by location.BM_BADGE_AT;

Now for every LOCATION I need count(*). I'm not getting the way out.
Please guide me how to do it.

Thanks in advance.

Wht do you want? HQL or SQL?

I'm creating SQLQuery in Hibernate

I post a test case in which is exemplified the use of SQLQuery

public void test(){
String sql = "Select * from table";
SQLQuery q = getSession().createSQLQuery(sql);
List l = q.list();
Iterator it = l.iterator();

while(it.hasNext()){
Object[] row = (Object[])it.next();
System.out.println("row size: " + row.length);
for(int i=0;i<row.length;i++){
System.out.println("field " + i + " :" + row[i]);
}
}

}



sql could be any VALID SQL for your database so you don't have to change anything in it if you want to port into hibernate in this way.

From my point of view your sql is not valid because you use

select field1 , count(*) from table1 group by field2


you have make group by for the fields you want to select if you use agregate functions like count()


I hope it will help you.

And by the way, if you use SQLQuery exhaustively in your app it does not mean that you are using hibernate, sorry to disappoint you. hibernate it is a lot more than SQLQuery.

Thanks a lot. It solved my problem.

I'm using SQLQuery even in Hibernate because my tables were not associated earlier. Now if I'll go for association now, its going to take a long time. So I used SQLQuery.

Thanks again.

I use a method like this to return a row count: