Inheritance and ManytoMany relationship

gpmoo7 · **Posted:** Tue Jan 27, 2009 5:56 pm

I have a problem using Hibernate.
I want to do something no really sophisticated and I can't find a way to do it.

I have this DB schema:
[vehicle]<=>[company_vehicle]<=>[company]

And I have those entities :
One abstract entity Vehicle :

Code:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="Vehicle_type",discriminatorType=DiscriminatorType.STRING)
@Table(name="Vehicle")
public abstract class Vehicle {

   [...]
}

2 Enities which inherit form Vehicle class.
The Car entity:

Code:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(discriminatorType=DiscriminatorType.STRING)
@DiscriminatorValue("car")
public class Car extends Vehicle {
   @ManyToMany
   @JoinTable(
      name="company_vehicle",
      joinColumns=@JoinColumn(name="vehicle_id",referencedColumnName="vehicle_id"),
      inverseJoinColumns=@JoinColumn(name="company_id",referencedColumnName="company_id")
   )
   private Collection<Company> companies;
}

and the Truck entity:

Code:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(discriminatorType=DiscriminatorType.STRING)
@DiscriminatorValue("truck")
public class Truck extends Vehicle {
   @ManyToMany
   @JoinTable(
      name="company_vehicle",
      joinColumns=@JoinColumn(name="vehicle_id",referencedColumnName="vehicle_id"),
      inverseJoinColumns=@JoinColumn(name="company_id",referencedColumnName="company_id")
   )
   private Collection<Companies> companies;
}

and finally, one other entity Company which have 2 ManyToMany relationships with 2 entities :

Code:

@Entity
@Table(name="company")
public class Company {

   [...]

   @ManyToMany(mappedBy="companies")
   private Collection<Car> cars;
    
   @ManyToMany(mappedBy="companies")
   private Collection<Truck> trucks;
}

The problem is that Hibernate give me all vehicles when I only ask for the cars.
For example, if I have 2 cars and 1 truck for a company, I get 3 cars.

Basically, Hibernate give me the result of:

Code:

SELECT vehicle.id
FROM vehicle,company_vehicle
WHERE vehicle.id=company_vehicle.id_vehicle
AND company_vehicle.id_company=X

and I want that:

Code:

SELECT vehicle.id
FROM vehicle,company_vehicle
WHERE vehicle.id=company_vehicle.id_vehicle
AND company_vehicle.id_company=X
AND vehicle.type="car"

Thank you for your help.

mmerder · **Posted:** Wed Jan 28, 2009 5:20 am

Just a sidenote that maybe helps: Why don't you include your companies collection in class Vehicle or create a subclass CompanyVehicle which Car and Truck could extend.
In case you don't want to change your inheritance strategy, I think there could also be problems, because you map both collections to the same columns, try different join-tables (I know it seems to be redundant, but actually you have on association-table to map two different associations).

Could you provide the code where you search the cars?

gpmoo7 · **Posted:** Wed Jan 28, 2009 10:58 am

mmerder wrote:

Why don't you include your companies collection in class Vehicle

I tried that but Hibernate told me that he can't find the companies reference:

Code:

org.hibernate.AnnotationException: mappedBy reference an unknown target entity property: Car.companies in Company.cars

mmerder wrote:

create a subclass CompanyVehicle which Car and Truck could extend.

Car and Truck already extend Vehicle ... where do you want to put this CompanyVehicle class? CompanyVehicle would be an Entity? a MappedSuperclass?

mmerder wrote:

In case you don't want to change your inheritance strategy, I think there could also be problems, because you map both collections to the same columns, try different join-tables (I know it seems to be redundant, but actually you have on association-table to map two different associations).

I need to use the same DB schema so I can't create an other join-table.
The previous version of the application didn't use Hibernate and it's possible in simple SQL to do what I want so I think it have to be possible to ask Hibernate to check the type column like in this SQL request:

Code:

SELECT vehicle.id
FROM vehicle,company_vehicle
WHERE vehicle.id=company_vehicle.id_vehicle
AND company_vehicle.id_company=X
AND vehicle.type="car"

mmerder wrote:

Could you provide the code where you search the cars?

Code:

@NamedQuery(
name="allCar",
query="SELECT car FROM Car AS car")

EDIT:
In fact, with the NamedQuery, I've got exactly what I want (only cars) but when I call myCompany1.cars, I've got all vehicles from this company.
So, I can go with named Query instead of using the cars property of the Company class.

mmerder · **Posted:** Wed Jan 28, 2009 1:16 pm

Ok, just add @Where to your companies collections:

Code:

   @ManyToMany(mappedBy="companies")
   @Where(clause="Vehicle_type='car'")
   private Collection<Car> cars;
   
   @ManyToMany(mappedBy="companies")
   @Where(clause="Vehicle_type='truck'")
   private Collection<Truck> trucks; 

That will add the extra where clause.
Rating is welcome ;-)

gpmoo7 · **Posted:** Wed Jan 28, 2009 3:06 pm

mmerder wrote:

Ok, just add @Where to your companies collections:

Code:

   @ManyToMany(mappedBy="companies")
   @Where(clause="Vehicle_type='car'")
   private Collection<Car> cars;
   
   @ManyToMany(mappedBy="companies")
   @Where(clause="Vehicle_type='truck'")
   private Collection<Truck> trucks; 

Thanks a lot. It's working!

Spriggan · **Joined:** Fri Jan 30, 2009 4:18 pm **Posts:** 2

Hello all.

I have the same structure than gpmoo7 and I solve my problem with @Where like mmerder suggested.

I just want to know if this is the "official" solution or if this is some kind of bug.
I found this bug report and it look like our case:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2883

Thank you very much.