Home » Eclipse Projects » EclipseLink » ManyToOne with foreign keys on distinct tables
| ManyToOne with foreign keys on distinct tables [message #638178] |
Wed, 10 November 2010 11:01  |
 romeo_j 
Messages: 5
Registered: November 2010 |
Junior Member |
|
|
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 #638238 is a reply to message #638178] |
Wed, 10 November 2010 15:39  |
|
This is not a very good data-model, as you cannot define proper foreign key constraints.
Ideally you would change the CarLabel COUNTRY to CATALOG_ID so that you have a proper foriegn key from Car to CarLabel. You could also make CarLabel use a generated id so you just have a foreign key to the generated id.
Otherwise, in EclipseLink you could use a custom SQL query for the mapping. Using a DescriptorCustomizer to the SQL string on the mapping.
i.e.
((OneToOneMapping)descriptor.getMappingForAttributeName("car ")).setSelectionSQLString("Select * from CARLABEL where CARCODE = #CARECODE and COUNTRY = (Select COUNTRY from CATALOG where ID = #CATALOG_ID)");
You could also probably make Car a multiple table descriptor so it could use the country foreign key, but this would be little complex, and you would need to use a DescriptorCustomizer as JPA does not support this.
James : Wiki : Book : Blog : Twitter
|
|
|
| Re: ManyToOne with foreign keys on distinct tables [message #638275 is a reply to message #638238] |
Wed, 10 November 2010 16:49 |
romeo_j
Messages: 5
Registered: November 2010 |
Junior Member |
|
|
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?
|
|
|
| Re: ManyToOne with foreign keys on distinct tables [message #638518 is a reply to message #638275] |
Thu, 11 November 2010 14:56 |
|
Because you have not set any @JoinColumn, it gets defaulted, which assume the full Id. You can resolve this in your customizer by remove the 2nd foreign key,
DatabaseField country = new DatabaseField("COUNTRY");
mapping.getForeignKeyFields().remove(country );
mapping.getTargetToSourceKeyFields().remove(mapping.getSourc eToTargetKeyFields().remove(country));
Or you could mark the relationship as @Transient and define it entirely in the customizer, adding just the CARCODE foriegn key.
James : Wiki : Book : Blog : Twitter
|
|
| |
Goto Forum:
Current Time: Fri Apr 19 21:06:22 GMT 2024
Powered by FUDForum. Page generated in 0.03158 seconds |
] 
Search
Help
Register
Login
Home
