Basic Newbie select question / problem

chrisw · **Joined:** Sun Aug 06, 2006 1:47 am **Posts:** 8

I'm having a problem with a select I've created, basically i have the following situation: I have a domain class Property, which has some fields which are also domain objects a code snippet from Property is

Code:

....
   private Address address;
   private PropertyType propertyType;
   private SaleType saleType;
        private Location location;
....

and accessors like

   public Location getLocation() {
      return location;
   }
   public Address getAddress() {
      return address;
   }

   public void setAddress(Address address) {
      this.address = address;
   }
   public void setLocation( Location location) {
      this.location = location;
   }

in my Property.hbm.xml i have the following relations defined for these objects ( after checking out chapter 5 of the documentation )

Code:

....
   <many-to-one name="location" class="com.barrakooda.domain.Location" unique="true"/>
   <many-to-one name="propertyType" class="com.barrakooda.domain.PropertyType" unique="true"/>
   <many-to-one name="saleType" class="com.barrakooda.domain.SaleType" unique="true"/>
   <many-to-one name="address" class="com.barrakooda.domain.Address" unique="true"/>

I specified 'many-to-one' as an alternative option to one-to-one since it seemed simpler and i only need a uni-directional association.

Finally I thought I would be able to use SQL of a form similar to :

Code:

Select p from Property p, Location l, Address a, PropertyType pt where p.location.locationId = l.locationId and p.Address.addressId = a.addressId and a.locationId = l.locationId and p.propertyType.propertyTypeId = pt.propertyTypeId

This doesn't work for me. Could anyone suggest a configuration which would solve this problem? What is the best way to go about setting up hibernate for a class which references a number of other domain objects with a one to one relationship?
One last thought If i were to delete a location from my location table how do I specify to hibernate that this should perform a cascade delete of all addresses in that location and all properties in that location ?

I know this has been rather long winded but any thoughts solutions would be hugely appreciated.

cheers

chris

bonnyr · **Posted:** Sun Aug 06, 2006 8:18 pm

chrisw wrote:

Code:

Select p from Property p, Location l, Address a, PropertyType pt where p.location.locationId = l.locationId and p.Address.addressId = a.addressId and a.locationId = l.locationId and p.propertyType.propertyTypeId = pt.propertyTypeId

This doesn't work for me. Could anyone suggest a configuration which would solve this problem? What is the best way to go about setting up hibernate for a class which references a number of other domain objects with a one to one relationship?
One last thought If i were to delete a location from my location table how do I specify to hibernate that this should perform a cascade delete of all addresses in that location and all properties in that location ?

I know this has been rather long winded but any thoughts solutions would be hugely appreciated.

cheers

chris

Chris,

An HQL query which returns a Property is probably all you need as the
rest of the attributes are reachable using Java:

Code:

Select p
from Property p
where p.location.id = :locationID 
and p.address.id = :addressId 
and p.address.location.id = :locationId 
and p.propertyType.id = :propertyTypeId

where you'd have to specify some parameters or if you wanted a list of all
properties where the locationId matches that of the address' you could do:

Code:

Select p
from Property p
where p.location =  p.address.location

Then, the returned list is a list of matching Property instances.

chrisw · **Joined:** Sun Aug 06, 2006 1:47 am **Posts:** 8

Hi

I managed to fix this problem I was having thanks.

chrisw · **Joined:** Sun Aug 06, 2006 1:47 am **Posts:** 8

Hi ,

As I mentioned I changed my configuration to <one-to-one and this has now fixed my problem ( almost).
Now I have a problem with a select.

I have a class Property which has other domain objects Address, Location, PropertyType and I have tried to perform a select as follows...

Code:

select p from property p
     inner join p.location as location
     inner join p.saletype as saletype
     inner join p.propertytype as propertytype,
     Location l, PropertyType pt, SaleType st where

     l.locationid = location.locationid and l.locationname = location.locationname and propertytype.propertytypeid = pt.propertytypeid and saletype.saletypeid = st.saletypeid

However this only ever returns me 1 row !
Can anyone help ? It should be returning a number of properties.