my dog learned polymorphism*
The moose likes Object Relational Mapping and the fly likes one-to many relationship(HQL-SQL generation) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "one-to many relationship(HQL-SQL generation)" Watch "one-to many relationship(HQL-SQL generation)" New topic
Author

one-to many relationship(HQL-SQL generation)

Jignesh Patel
Ranch Hand

Joined: Nov 03, 2001
Posts: 626

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

Joined: Aug 21, 2004
Posts: 28
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: one-to many relationship(HQL-SQL generation)