Hibernate version:2

oracle9i

it is not generting SQL



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

code:
--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------



But I couldn't able to see SQL.


-Jignesh

As far as seeing the sql goes, if you have log4j or similar in your application, then you can define

and the sql should show up in your logging file (or console, or whatever your appender is).

As far as your hql goes, I am a new user as well, but the way we have been setting up our application is:
You should have a class DeviceReadings which has your attributes from DEVICE_READINGS. DeviceReadings could contain an set that will contain a collection of ErrorHistory objects.
Your DeviceReadings mapping file will then contain <property> definitions for each of the attributes from that table, and a <set> definition for the collection of ErrorHistory objects.
You will also have to map the ErrorHist table

Your hql can then simply be something like


Roughly:
[code]
<hibernate-mapping ...
<class name="DeviceReadings" table="DEVICE_READINGS">
<id ....
<!-- Use your id generation methodology -->
</id>
<property name="brandName" column="BRAND_NAME" />
.
.
.
<set name="errorHistory" table="ERROR_HIST" >
<key column="DEVICE_READING_KEY" />
<one-to-many class="ErrorHistory" />
</set>
</class>
<class name="ErrorHistory" table="ERROR_HISTORY">
<id ....
<!-- Use your id generation methodology -->
</id>
<property name="attribte1" column="ATTR_1" />
<property name="attribte2" column="ATTR_2" />
.
.
.
</class>

<query name="findAllDeviceReadings">
<![CDATA[
from DeviceReading dr where dr.brandName = :bn and dr.serialNumber = :sn and dr.modelNumber = :mn ]]>
</query>

</hibernate-mapping>

Define your Java classes with matching names etc and then when you run the query you should get a DeviceReadings object(s) with an embedded Set of ErrorHistory objects relevant for each DeviceReadings.

Andrew

Andrew,

I have a same setup as you said. But I have to get the data by comparing foriegn key from History table.

-Jignesh

Andrew,

I have a same setup as you said. But I have to get the data from history table not from the device_reading table

-Jignesh

I know more about Hibernate 3 than Hibernate 2, but I don't think you need the


part of the query. Looks like you are mixing up SQL and HQL.

I don't normally use HQL, but shouldn't you be doing something along the lines of:

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

You should always specify the joins in your configuration file rather then explicitly in your query.

-Richard

exactly, the {eh.*} part is not HQL, it is part of the native-SQL support. You are mixing querying concepts here.

Also, you should be using the join syntax instead of this theta join syntax since you already have this association mapped (otherwise, eh.deviceReadings is not valid syntax either):

or possibly even:

I leave the explanation as to meaning of "fetch" in the join as a user excercise... ;)[/b]

non of solution is working.
I tried all the 3 queries.

-Jignesh

Oddly enough, mappings and maybe even exceptions might actually allow us to help you without having to guess.

Generally speaking "it no workie" is not sufficient information.

Steve,

Let me check with my DBA, I think some problem with my database.

I tried to execute following query:

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

which in turn generates following query:

select errorhist0_.ERROR_HIST_KEY as ERROR_HI1_,errorhist0_.DEVICE_READING_KEY as DEVICE_R9_
from CEM_ERROR_HIST errorhist0_,CEM_DEVICE_READINGS deviceread1_ where (deviceread1_.DEVICE_READING_KEY=973 )
AND(deviceread1_.MODEL_NUMBER='ESTUDIO280') AND (deviceread1_.BRAND_NAME='TOSHIBA' )
AND(deviceread1_.SERIAL_NUMBER='CTG410578' )

To me above query seems ok. So I think now I need to look at data.


-Jignesh

Sorry above query read as
select errorhist0_.ERROR_HIST_KEY as ERROR_HI1_,errorhist0_.DEVICE_READING_KEY as DEVICE_R9_
from CEM_ERROR_HIST errorhist0_,CEM_DEVICE_READINGS deviceread1_ where (deviceread1_.DEVICE_READING_KEY=errorhist0_.device_reading_key )
AND(deviceread1_.MODEL_NUMBER='ESTUDIO280') AND (deviceread1_.BRAND_NAME='TOSHIBA' )
AND(deviceread1_.SERIAL_NUMBER='CTG410578' )[/

-Jignesh[/quote]

Is your database case-sensitive in terms of identifiers? I notice you mapped only one of thosse column names in lowercase, while the rest are uppercase.

What is the error you get?

yes my DB columns values are case sensitive.

I figured out the problem. It was a uppercase-lower case mapping problem.

Everything works fine with the following query:

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

I will update you people if something comes out during unit testing.

And I do appreciate support from all people.
I believe in this group every body is having some level of knowlege ie. nobody is newbie.

-Jignesh