CODEWITHSUNDEEP
javahibernatejpaormhibernate-mapping
Daniel Newtown
Daniel Newtown

Reputation: 2933

How to have a relationship based on a non-key field?

I have two entities as following, when I try to add items to my car table it shows following error message;therefore, it does not allow me to have more than one car with 'Auto' transmission.

Error:

 #1062 - Duplicate entry 'Auto' for key 'UK_bca5dfkfd4fjdhfh4ddirfhdhesr'

Entities:

Car

@Entity
public class Car  implements java.io.Serializable {


    @Id
    @GeneratedValue
    long id;
    @Column(name="transmission", nullable = false)
    String transmission;
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
    Set<CarFactory> factories;
    ...
}

Sample values for car table:

10 Auto
12 Auto
43 Manual
54 Manual
65 Normal
68 Standard
90 Normal
99 NoGear

CarFactory

@Entity
public class CarFactory implements java.io.Serializable {

   @Id
   @JoinColumn(name="transmission",referencedColumnName = "transmission")
   @ManyToOne
   Car car;

   @Id
   @JoinColumn(name="factory_id", referencedColumnName= "id")
   @ManyToOne
   Factory factory;

   ...
}

Expected values for CarFactory table

Auto Fac1
Auto Fac2
Manual Fac1
Auto Fac5
Standard Fac6
Normal Fac3
NoGear Fac1

Ive followed answer of this question as well but it did not work.

Long story short, I need to have a table with two foreign keys from other tables, with combined primary key. It should not force unique foreign key in participating tables.

Upvotes: 21

Views: 1712

Answers (6)

PlutonicJ
PlutonicJ

Reputation: 89

Your question leaves room for interpretation: A set of factories might be necessary for one car model, which is build at different factories. Or different parts of a car instance are build at different factories. For your sample and expected data, there is no solution. If different factories can produce/have cars with the same type of transmissions, there is no way to determine, which car was produced in/is associated with the right factory. You only can say, it was one of the factories with the same transmission. However, this is not supported by any mapping. If you want to associate the right factory/factories, you need to add further information about the CarFactory to the Car table (e.g. Factory). It depends on your requirements, but I guess, the answer of chsdk comes close to them.

Upvotes: 0

sgpalit
sgpalit

Reputation: 2686

@JoinColumn(name="transmission",referencedColumnName = "transmission")

Change with this

@JoinColumn(name="car_id",referencedColumnName = "id")

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154180

I emulated your use case and you can find the test on GitHub.

These are the mappings:

@Entity(name = "Car")
public static class Car implements Serializable {

    @Id
    @GeneratedValue
    long id;

    @Column(name="transmission", nullable = false)
    String transmission;
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
    Set<CarFactory> factories;
}

@Entity(name = "Factory")
public static class Factory  implements Serializable {

    @Id
    @GeneratedValue
    long id;
}

@Entity(name = "CarFactory")
public static class CarFactory implements Serializable {

    @Id
    @ManyToOne
    @JoinColumn(name = "transmission", referencedColumnName = "transmission")
    Car car;

    @ManyToOne
    @Id
    Factory factory;

    public void setCar(Car car) {
        this.car = car;
    }

    public void setFactory(Factory factory) {
        this.factory = factory;
    }
}

This is how you add some test data:

doInTransaction(session -> {
    Car car = new Car();
    car.transmission = "Auto";

    Car car1 = new Car();
    car1.transmission = "Manual";

    Factory factory = new Factory();
    session.persist(factory);
    session.persist(car);
    session.persist(car1);

    CarFactory carFactory = new CarFactory();
    carFactory.setCar(car);
    carFactory.setFactory(factory);

    CarFactory carFactory1 = new CarFactory();
    carFactory1.setCar(car1);
    carFactory1.setFactory(factory);

    session.persist(carFactory);
    session.persist(carFactory1);
});

And the test works just fine:

@Test
public void test() {
    doInTransaction(session -> {
        List<CarFactory> carFactoryList = session.createQuery("from CarFactory").list();
        assertEquals(2, carFactoryList.size());
    });
}

Update

You get an exception because of the following unique constraint:

alter table Car add constraint UK_iufgc8so6uw3pnyih5s6lawiv  unique (transmission)

This is the normal behaviour, since a FK must uniquely identify a PK row. Like you can't have more rows with the same PK, you can't have a FK identifier reference more than one row.

You mapping is the problem. You need to reference something else, not the transmision. You need a unique Car identifier, like a VIN (Vehicle Identification Number), so your mapping becomes:

@Entity(name = "Car")
public static class Car implements Serializable {

    @Id
    @GeneratedValue
    long id;

    @Column(name="vin", nullable = false)
    String vin;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "car")
    Set<CarFactory> factories;
}

@Entity(name = "CarFactory")
public static class CarFactory implements Serializable {

    @Id
    @ManyToOne
    @JoinColumn(name = "vin", referencedColumnName = "vin")
    Car car;

    @ManyToOne
    @Id
    Factory factory;

    public void setCar(Car car) {
        this.car = car;
    }

    public void setFactory(Factory factory) {
        this.factory = factory;
    }
}

This way, the vin is unique and the Child association can reference one and only one parent.

Upvotes: 8

cнŝdk
cнŝdk

Reputation: 32175

The problem here is that you are using a non primary key field as a foreign key which seems to be incorrect and your transmission field, should be unique, this line is incorrect:

@JoinColumn(name="transmission",referencedColumnName = "transmission")

You have a Many-To-Many mapping here which needs an @EmbeddedId property in the association table, and your code should be like this:

CarFactory class

@Entity
public class CarFactory {

   private CarFactoryId carFactoryId = new CarFactoryId();

   @EmbeddedId
   public CarFactoryId getCarFactoryId() {
       return carFactoryId;
   }

   public void setCarFactoryId(CarFactoryId carFactoryId) {
       this.carFactoryId = carFactoryId;
   }

   Car car;

   Factory factory;

   //getters and setters for car and factory
}

CarFactoryId class

@Embeddable
public class CarFactoryId implements Serializable{

    private static final long serialVersionUID = -7261887879839337877L;
    private Car car;
    private Factory factory;

    @ManyToOne
    public Car getCar() {
        return car;
    }
    public void setCar(Car car) {
        this.car = car;
    }

    @ManyToOne
    public Factory getFactory() {
        return factory;
    }
    public void setFactory(Factory factory) {
        this.factory = factory;
    }
    public CarFactoryId(Car car, Factory factory) {
        this.car = car;
        this.factory = factory;
    }
    public CarFactoryId() {}

}

Car class

@Entity
public class Car {

    @Id
    @GeneratedValue
    long id;
    @Column(name="transmission", nullable = false)
    String transmission;

    private Set<CarFactory> carFactories = new HashSet<CarFactory>();

    @OneToMany(mappedBy = "primaryKey.car",
    cascade = CascadeType.ALL)
    public Set<CarFactory> getCarFactories() {
        return carFactories;
    }

    ...

}

And the same thing for Factory class, note that there are several ways to define an embedded id or a composite id, take a look at:

Note:

In my example I haven't used transmission field in the composite id but you can use it, you can see the example below:

Upvotes: 7

Kicsi
Kicsi

Reputation: 1133

There is a ManyToOne relationship in your CarFactory referencing the transmission field in Car. That means the transmission field in Car must be unique.

It seams like you are trying to add multiple items with the same transmission value to your Car table, however your design suggests you only need one entry in your Car table per transmission, and you only need to add multiple CarFactory entries per transmission.

Upvotes: 2

dedek
dedek

Reputation: 8311

Why are you not using @ManyToMany relationship?

@Entity
public class Car implements java.io.Serializable {

    @Id
    @GeneratedValue
    long id;

    @Column(name="transmission", nullable = false)
    String transmission;

    @ManyToMany
    @JoinTable(
        name="CARFACTORY",
        joinColumns={@JoinColumn(name="transmission", referencedColumnName="transmission")},
        inverseJoinColumns={@JoinColumn(name="factory_id", referencedColumnName="id")})
    Set<Factory> factories;
    ...
}

... didn't test the code, but it should work.

Upvotes: 2

Related Questions