one-to many relationship(HQL-SQL generation)

[Jignesh Patel]

I am trying execute join on using HQL.

The background is:
I have 2 tables ERROR_HIST and DEVICE_READINGS,
where DEVICE_READING KEY is a foriegn key in ERROR_HIST and primary key in DEVICE_READINGS.

It is one-many relationship between DEVICE_READING and ERROR_HIST

My sql is :

select eh.* from error_hist eh,device_readings dr
where eh.DEVICE_READING_KEY=dr.DEVICE_READING_KEY AND
dr.BRAND_NAME="ZEROX" AND dr.SERIAL_NUMBER="CDFDF" AND dr.MODEL_NUMBER="EA1010"

In HQL I have tried with

SELECT {eh.*} FROM com.domain.ErrorHist as eh, com.domain.DeviceReadings as dr WHERE eh.deviceReadings.deviceReadingKey=dr.deviceReadingKey AND dr.modelNumber=:mn AND dr.brandName=:bn AND dr.serialNumber=:sn


and

SELECT {eh.*} FROM com.domain.ErrorHist as eh, com.domain.DeviceReadings as dr WHERE eh.deviceReadings=dr AND dr.modelNumber=:mn AND dr.brandName=:bn AND dr.serialNumber=:sn


let me know what is wrong in the HQL queries. Alos I have checked values of :mn,:bn and :sn are passed properly.

Also is there any method in hibernate-2 which can tell me what SQL generated by HQL. I have tried with
[B]

 <property name="show_sql">true</property>  

[/B]

But I couldn't able to see SQL.
[ August 01, 2005: Message edited by: Jignesh Patel ]

[Ambily G Nair]

Hi,

I think this query will work for you,

Query q1 = session.createQuery(SELECT eh FROM com.domain.ErrorHist as eh, com.domain.DeviceReadings as dr WHERE eh.deviceReadings.deviceReadingKey=dr.deviceReadingKey AND dr.modelNumber=:mn AND dr.brandName=:bn AND dr.serialNumber=:sn)
q1.setParameter("mn","sefs",Hibernate.STRING); // second field is the value to be substituted and 3rd is the type of the field
q1.setParameter("bn",new Float(4500000f),Hibernate.FLOAT);// second field is the value to be substituted and 3rd is the type of the field
q1.setParameter("sn",new Integer(2),Hibernate.INTEGER);// second field is the value to be substituted and 3rd is the type of the field


When I put hibernate.show_sql=true in hibernate.properties I could see the queries in console