Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

one-to many relationship(HQL-SQL generation)

 
Jignesh Patel
Ranch Hand
Posts: 626
Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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][/B]

But I couldn't able to see SQL.
[ August 01, 2005: Message edited by: Jignesh Patel ]
 
Ambily G Nair
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic