| ManyToOne with foreign keys on distinct tables [message #638178] |
Wed, 10 November 2010 06:01  |
Eclipse User |
|
|
|
Hi,
There have been 2 days since I try to solve that and I found no issue.
My DB model is something like that:
CATALOG - table
ID - generated
COUNTRY - (country code 2 chars)
WEEK - (YYYYWW)
In this table my ID is the primary key, but the COUNTRY and WEEK fields may identify one line, so there is an unique index on these fields..
CAR - table
ID - generated id
CATALOG_ID - FK on catalog
CARCODE - a code to identify the brand and the type of the car
The CAR table keeps all available cars for a CATALOG. Since I have many updated, I don't want to keep the label of the CAR in this table but in a table which keeps all labels for all cars in a country :
CARLABEL - table
CARCODE
COUNTRY - these 2 fields make my PK
LABEL
I'm trying to create entities classes for this:
@Entity
public class Catalog {
@Id @Generated private Long id;
private String country;
private String week;
@OneToMany(mappedBy="catalog")
private List<Car> cars;
// - getters, setters, constructor
};
@Entity
public class Car {
@Id @Generated private Long id;
@ManyToOne
@JoinColumn(name="CATALOG_ID")
private Catalog catalog;
@ManyToOne //Here's my problem
private CarLabel car;
};
@Embeddable
public class CarLabelPK {
private String codecar;
private String country;
};
@Entity
public class CarLabel {
@EmbeddedId private CarLabelPK id;
private String label;
}
So, here we are, I'm trying to have an instance of CarLabel in my Car entity, something like a ManyToOne mapping, but having one FK in Catalog entity and the other in Car entity.
Something like (but this doesn't work) :
@ManyToOne
@JoinColumns({
@JoinColumn(name="CARCODE", referencedColumnName="CARCODE"),
@JoinColumn(name="catalog.COUNTRY", referencedColumnName="COUNTRY")
})
private CarLabel car;
Actually my model is a bitter more complex and it has nothing to do with cars, and I've created all that model just to explain simplier my problem.
I'm using EclipseLink 2.0 on a JEE6 environnement.
I've followed to the @Customizer annotation as a solution but I was not able to solve this.
Somebody has an idea?
Roméo
|
|
|
|
| Re: ManyToOne with foreign keys on distinct tables [message #638275 is a reply to message #638238] |
Wed, 10 November 2010 11:49  |
| Eclipse User |
|
|
|
Thanks for your answer this is a start.
Indeed, my model isn't good. But:
- setting the catalog_id in CarLabel, is not quite that, because a line in CARLABEL is available for multiples CATALOGs, all of those who have the same COUNTRY. I can thow, put the CATALOG_ID of the catalog for whom I've inserted the line, and find a line with a query like "SELECT * FROM carlabel WHERE carcode=:carcode AND EXISTS (SELECT 'X' FROM catalog WHERE catalog.id=carlabel.catalog_id and catalog.country=:country)" - not quite fast as a query
- having an id in the CARLABEL table and putting it in CAR table it's a better option. But still, I will need to use mostly the carcode in other queries, and I would like not to pass by the CARLABEL table. And these queries will be done in a C program which knows nothing about EclipseLink.
So, let's say that I have to keep this model.
I've tried your proposal, so I've mapped my car field like this.
@Entity
@Customizer(CarLabelCustomizer.class)
public class Car {
@Id @Generated private Long id;
@ManyToOne
@JoinColumn(name="CATALOG_ID")
private Catalog catalog;
@OneToOne //I've specified no join column
private CarLabel car;
};
//and my CarLabelCustomizer class
public class CarLabelCustomizer implements DescriptorCustomizer {
@Override
public void customize(ClassDescriptor descriptor) throws Exception {
OneToOneMapping mapping = ((OneToOneMapping)descriptor.getMappingForAttributeName("carLabel"));
mapping.setSelectionSQLString("Select * from CARLABEL where CARCODE = #carcode and COUNTRY = (Select COUNTRY from CATALOG where ID = #catalog_id)");
}
}
When I try to insert a CAR
Car car = new Car();
car.setCarcatalog(catalog);
CarLabelPK pk = new CarLabelPK();
pk.setCarcode("AM1");
pk.setCountry("FR");
CarLabel carLabel = em.find(CarLabel.class, pk);
if (carLabel == null) {
carLabel = new CarLabel();
carLabel.setId(pk);
}
car.setCarLabel(carLabel);
carLabel.setLabel("Aston Martin DB9");
em.persist(car);
em.persist(carLabel);
, I have this error :
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00904: "COUNTRY": invalid identifier
Error Code: 904
Call: INSERT IGNORE INTO CAR (ID, CATALOG_ID, CARCODE, COUNTRY) VALUES (?, ?, ?, ?)
bind => [1, 2, AM1, FR]
Query: InsertObjectQuery(net.hexalis.persistence.cartest.Car@1734aa9)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
....
Obviously is trying to insert a country code, assuming that I have a two column join between Car and CarLabel entities.
What can I do?
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03201 seconds
Home