Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » ManyToOne with foreign keys on distinct tables
ManyToOne with foreign keys on distinct tables [message #638178] Wed, 10 November 2010 06:01 Go to next message
romeo_j  is currently offline 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 10:39 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 11:49 Go to previous messageGo to next message
romeo_j  is currently offline 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 09:56 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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
icon14.gif  Re: ManyToOne with foreign keys on distinct tables [message #638704 is a reply to message #638518] Fri, 12 November 2010 04:41 Go to previous message
romeo_j  is currently offline romeo_j
Messages: 5
Registered: November 2010
Junior Member
Well thank you, I've tried your first method, it works fine.
I can't see any way I would have been able to do this by myself, I find the EclipseLink User Guide not quite clear.
Are there tutorials or any other documentation where people can find how to do such things?

Thanks again,
Roméo
Previous Topic:Not finding persistence.xml from linked resource
Next Topic:JPA entities losing their id
Goto Forum:
  


Current Time: Fri Jul 25 01:28:03 EDT 2014

Powered by FUDForum. Page generated in 0.02363 seconds