I have these 4 tables and 4 java classes. 2,3,4 tables are
represented as 'Set' methods in WeatherLocation class.
My problem is my company's db schema is not normalized properly and it
does not have referential integrity, no FK. Looking at the previous
sql select statements i implemented the following mapping file.

Now i am getting the following error. Is this Hibernate issue ?
or my mapping file problem ? Can some one tell me how to mention
pk, fk in mapping file. I wrote this mapping file by reading manual.

net.sf.hibernate.MappingException:
Foreign key (weather_images [IMAGE_ID])) must have same number of columns as the referenced primary key (weather_images [ZIP_CODE,IMAGE_ID])
at net.sf.hibernate.mapping.ForeignKey.setReferencedTable(ForeignKey.java:60)
at net.sf.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:638)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:732)
at com.mycompany.test.WeatherModelTest.prepareWeatherConfiguration(WeatherModelTest.java:97)
at com.mycompany.test.WeatherModelTest.testWeather(WeatherModelTest.java:72)
at com.mycompany.test.WeatherModelTest.main(WeatherModelTest.java:60)


TABLES:
1. WEATHER_LOCATION :
Name Null? Type
------------------------------- -------- ----
ZIP_CODE NOT NULL VARCHAR2(11) pk
LOCATION_ID NOT NULL VARCHAR2(8) pk
STATION_ID VARCHAR2(4)

2. WEATHER_CURRENT :
Name Null? Type
------------------------------- -------- ----
STATION_ID NOT NULL VARCHAR2(6) pk
TEMPERATURE NUMBER

3. WEATHER_FORECAST:
Name Null? Type
------------------------------- -------- ----
LOCATION_ID NOT NULL VARCHAR2(10) pk
DAY_OF_WEEK NOT NULL NUMBER pk

4. WEATHER_IMAGES :
Name Null? Type
------------------------------- -------- ----
IMAGE_ID NOT NULL NUMBER pk
IMAGE_DESC VARCHAR2(45)


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.mycompany.weathermodel.model.WeatherLocationVO" table="weather_location">
<id name="zip_code">
<generator class="uuid.hex"/>
</id>
<property name="station_id"/>
<property name="location_id"/>
<set name="weatherCurrent" lazy="true" table="weather_current">
<key column="ZIP_CODE"/>
<many-to-many class="com.mycompany.weathermodel.model.WeatherCurrentVO" column="STATION_ID"/>
</set>
<set name="weatherForecast" lazy="true" table="weather_forecast">
<key column="ZIP_CODE"/>
<many-to-many class="com.mycompany.weathermodel.model.WeatherForecastVO" column="LOCATION_ID"/>
</set>
<set name="weatherImages" lazy="true" table="weather_images">
<key column="ZIP_CODE"/>
<many-to-many class="com.mycompany.weathermodel.model.WeatherImagesVO" column="IMAGE_ID"/>
</set>
</class>
<class name="com.mycompany.weathermodel.model.WeatherCurrentVO" table="weather_current">
<id name="station_id">
<generator class="uuid.hex"/>
</id>
- - - - - - - -
- - - - - - - -
</class>
<class name="com.mycompany.weathermodel.model.WeatherForecastVO" table="weather_forcast">
<id name="location_id">
<generator class="uuid.hex"/>
</id>
- - - - - - - -
- - - - - - - -
</class>
<class name="com.mycompany.weathermodel.model.WeatherImagesVO" table="weather_images">
<id name="image_id">
<generator class="uuid.hex"/>
</id>
<property name="image_desc"/>
</class>
</hibernate-mapping>

are you sure it's not a one to many you need to declare ?

I just ran this SQL. Multiple ZIPCODEs can have same station_id,
weather_current table have same station_id for location.
This made me to say many-to-many.

select l.zip_code, l.location_id, l.station_id as ls,
c.station_id as cs from weather_location l, weather_current c;

ZIP_CODE LOCATION LS CS
----------- -------- ---- ----
66024 USKS0176 KSTJ KSTC
64501 USKS0176 KSTJ KSTC
64502 USKS0176 KSTJ KSTC
64503 USKS0176 KSTJ KSTC
64504 USKS0176 KSTJ KSTC
64505 USKS0176 KSTJ KSTC
64506 USKS0176 KSTJ KSTC
64507 USKS0176 KSTJ KSTC
64508 USKS0176 KSTJ KSTC
66732 USKS0175 KCNU KSTC
67439 USKS0173 KSLN KSTC
67450 USKS0173 KSLN KSTC

Here is the SQL i made mapping for.
select f.day_of_week as fd, l.location_id as ll,f.location_id as fl,
l.zip_code, l.city, l.state, f.sky24, c.temperature,c.wind_speed, c.relative_humidity
from weather_location l, weather_forecast f, weather_current c
WHERE l.zip_code = 30346
and l.station_id = c.station_id
and l.location_id = f.location_id
and f.day_of_week = 2;

i d'ont know how was done your relationnal model but it seems you have


many Weather_Location to one Weather_Current
many Weather_Location to one Weather_Forecast

weather image isn't linled anywhere? where can you fing a reference to image_id?

That is to say you'll have your WeatherLocation like this:
Class WeatherLocation {
private Integer zipcode; // your id
private WeatherCurrent wc; // <many-to-one name="wc" column="station_id" />

private WeatherForecast wca; // <many-to-one name="wca" column="location_id" />

.....

}

These are 2 separate previous SQL queries among 4 tables.

----------------------------------------------------------------------------------------------------------
select f.day_of_week as fd, l.location_id as ll,f.location_id as fl, l.zip_code, l.city, l.state, f.sky24, c.temperature,c.wind_speed, c.relative_humidity
from weather_location l, weather_forecast f, weather_current c
WHERE l.zip_code = 30346
and l.station_id = c.station_id
and l.location_id = f.location_id
and f.day_of_week = 2
----------------------------------------------------------------------------------------------------------
select l.zip_code,l.city, l.state, f.day_of_week, f.hi_temp_f, f.lo_temp_f, f.hi_temp_c, f.lo_temp_c,
f.sky24, f.uv_indx, f.uv_desc,
to_char( f.sunrise, 'HH:MI PM' ),
to_char( f.sunset, 'HH:MI PM'),
i.image_desc
from weather_forecast f, weather_location l, weather_images i
where l.zip_code = 30346
and l.location_id = f.location_id
and f.sky24 = i.image_id
order by f.day_of_week asc;
----------------------------------------------------------------------------------------------------------

please think in object no in relationnal, you would like to do something like that:
- retrieve a WeatherLocation defined with the zipcode
- once you have it, you want to do
myWeatherLocation.getCity(),getState... --> returning properties of the object
myWeatherLocation.getWeatherCurrent() --> returning property of the which is another Objet: WeatherCurrent

myWeatherLocation.getWeatherForeWast() --> returning property of the which is another Objet: WeatherForeCast

and also myWeatherForeCast.getImage()

That is was seems to show your result table isn't it?

Thanks for the direction. I have been trying to figure out how the tables are laid out. I will start with objects. I talked to my DBA, looks like we have messy data in there[many zip codes have same city name]. Hopefully i wouldnt have "?" after the tweaks.